Assign a serial number to a related and filtered form RRS feed

  • Question

  • I have a main form named "frmInsurancePolicy" that have the main information of an Insurance Policy. Every insurance policy could have several addendum. When the operator click a button on the main form another related form opens and wants the information for the addendum. Because every insurance policy could have several addendum and I filter the opened forms based on the insurance policy form, I want the addendum form -that are filtered- get number -AddendumNumber- from 1 to any addendum number that is possible. For example, an Insurance policy may have one addendum today that when the operator enter the information for it , it get No 1 and when several days later it wants to issue another addendum for it , the addendum should get number 2 and similarly.  How can I assign serial number to addendums from 1 to any number, with respect that they are filtered based on main form.

    Private Sub cmdOpenRelatedAddendum_Click()
    On Error GoTo cmdRelatedAddendum_Click_Err
    Application.TempVars.add "tstrChildForm", "frmAddendum"
        If ChildFormIsOpen() Then
        End If
        Exit Sub
        MsgBox Error$
        Resume cmdRelatedAddendum_Click_Exit
    End Sub
    Private Sub OpenChildForm()
        strFormName = Application.TempVars("tstrChildForm")
        DoCmd.OpenForm strFormName, _
    End Sub
    Private Sub CloseChildForm()
        strFormName = Application.TempVars("tstrChildForm")
        DoCmd.Close acForm, strFormName
    End Sub
    Private Function ChildFormIsOpen()
        strFormName = Application.TempVars("tstrChildForm")
        ChildFormIsOpen = (SysCmd(acSysCmdGetObjectState, acForm, strFormName) And acObjStateOpen) <> False
    End Function
    Private Sub FilterChildForm()
        strFormName = Application.TempVars("tstrChildForm")
        If Me.NewRecord Then
            Me.DataEntry = True
            Forms(strFormName).Filter = "[InsurancePolicyID]= " & Me.[InsurancePolicyID]
            Forms(strFormName).FilterOn = True
        End If
    End Sub

    Karim Vaziri Regards,

    Saturday, June 11, 2016 7:49 PM


  • You can use the "DMax + 1" technique. It should not be hard to find references online because this is a FAQ.

    -Tom. Microsoft Access MVP

    • Proposed as answer by David_JunFeng Sunday, June 19, 2016 2:38 PM
    • Marked as answer by David_JunFeng Monday, June 20, 2016 2:36 PM
    Saturday, June 11, 2016 8:07 PM