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
Me!subfrmOffice.Form.txtboxOfficeID.SetFocus
InputData2 = Me!subfrmOffice.Form.txtboxOfficeID.Value
If InputData1 = InputData2 Then
Exit Sub
Else:
Do While (InputData1 <> InputData2)
Me!subfrmOffice.Form.txtboxOfficeID.SetFocus
Call GoToNextRecord
Me!subfrmOffice.Form.txtboxOfficeID.SetFocus
InputData2 = Me!subfrmOffice.Form.txtboxOfficeID.Value
Loop
End If
Public Sub GoToNextRecord()
DoCmd.GoToRecord , , acNext
As I said, it nearly works, but the loop is the problem.
