Loop through a subform RRS feed

  • Question

  • Hello

    I have created a relational database of organisations, offices and employees. One organisation has many offices, and one office has many employees. All good on that score. 

    I have a  main form (frmContacts derived from tblOrganisation)  and a sub form (subfrmOffice derived from tblOffice).  Don't worry about the employees.

    Each office record has an OfficeID (primary key). The tblOrganisation primary key as a foreign key in tblOffice.

    I have created a command button (visual basic code) on the MainForm (frmContacts) whereby the user enters the OfficeID as InputData1, and the form navigates to the particular record.  The code is:

     DLookup("[FKOrgID]", "tblOffice", _

    "[OfficeID] = " & InputData1)

    The command works perfectly well if one organisation only has one office. However, if an organisation has several offices, the form will only navigate to the organisation and the first office attached to that organisation. The user then has to manually click "Next Office Record " button (in the subfrmOffice) to find the particular office with the RN the user input.

    In otherwords, say an organisation has three offices, with OfficeID 1, OfficeID 2 and OfficeID 3. If I want to go to OfficeID 3, I currently click the command button I created on the main form (frmContacts), input 3, and the form displays the organisation and OfficeID 1.   I then manually click the subform nextrecord button to get to OfficeID 3.

    How can I get the command to go straight to the organisation along with office OfficeID 3?

    The following code nearly works, but the loop loops through the main form and not the subform, and of course then does not stop:

    Dim InputData1 As Integer                    'User inputs OfficeID
    Dim InputData2 As Integer                    

    InputData2 = Me!subfrmOffice.Form.txtboxOfficeID.Value

    If InputData1 = InputData2 Then
         Exit Sub
        Do While (InputData1 <> InputData2)
        Call GoToNextRecord 
        InputData2 = Me!subfrmOffice.Form.txtboxOfficeID.Value  
    End If

    Public Sub GoToNextRecord()
    DoCmd.GoToRecord , , acNext

    As I said, it nearly works, but the loop is the problem.

    Tuesday, March 20, 2018 12:19 PM


All replies