Finding a record from a different form

[bbatson]

Hello,

Suppose I have two forms, Form1 and Form2, both of which are open. Form1 has three fields, EmployeeID (Primary Key), EmployeeName and EmploymentDate. There is a combo box on the form that finds the record based on the selection made in the combo box (using the built-in VBA), call it "combobox1" . Form2 has a combo box of EmployeeID and EmployeeName, with EmployeeID being the bound field, call it "combobox2" .

What would be the VBA code that would take the EmployeeID selected on Form2 and find the related record on Form1? I've been able to set the "combobox1" to the value selected in "combobox2" but I've been unsuccessful in having Form1 subsequently find the record.

Any help would be greatly appreciated.

[ADezii]

Hello,

Suppose I have two forms, Form1 and Form2, both of which are open. Form1 has three fields, EmployeeID (Primary Key), EmployeeName and EmploymentDate. There is a combo box on the form that finds the record based on the selection made in the combo box (using the built-in VBA), call it "combobox1" . Form2 has a combo box of EmployeeID and EmployeeName, with EmployeeID being the bound field, call it "combobox2" .

What would be the VBA code that would take the EmployeeID selected on Form2 and find the related record on Form1? I've been able to set the "combobox1" to the value selected in "combobox2" but I've been unsuccessful in having Form1 subsequently find the record.

Any help would be greatly appreciated.

This is basically a Multi-Step process and should be performed correctly. Here are the basic Steps and questions to be answered.

1. Is Form2 Loaded?
2. If Form2 is Loaded, does ComboBox2 contain a valid entry?
3. Is there a Record in the Record Source for Form1 that has the EmployeeID referenced in ComboBox2 on Form2?
4. What is the most efficient mechanism for finding this Matching Record?
5. If there is a Match, how do we navigate to the Record on Form1?
6. If a Matching Record does not exist, then what?
7. The below posted code should answer all these questions for you, any other questions, please feel free to ask.

[CODE=vb]
Dim rstClone As DAO.Recordset

'First, let's see if Form2 is even Loaded
If CurrentProject. AllForms("Form2 ").IsLoaded Then
'Second, is there a valid entry in ComboBox2?
If Not IsNull(Forms![Form2]![ComboBox2]) Then
Set rstClone = Me.RecordsetClo ne

rstClone.FindFi rst "[EmployeeID] = " & Forms![Form2]![ComboBox2]

'Is there a Matching Record on EmployeeID? If there is, navigate
'to it on Form1
If Not rstClone.NoMatc h Then
Me.Bookmark = rstClone.Bookma rk
Else 'Not a Match! Out of Dodge again!
Exit Sub
End If
Else 'ComboBox2 on Form2 has no value, get out of Dodge!
Exit Sub
End If
Else 'Form2 is not even Loaded, no need to proceed
Exit Sub
End If

'Don't forget the Clean Up chores
rstClone.Close
Set rstClone = Nothing[/CODE]

[bbatson]

Thank you so much, very helpful.

Now, what if I wanted to assign this type of functionality to a command button (call it "cmd_find") that was on Form2? That is, after making a selection in combobox2, the button will go to Form1 and find the record that matches the selection in combobox2 (on Form2)?

[ADezii]

Thank you so much, very helpful.

Now, what if I wanted to assign this type of functionality to a command button (call it "cmd_find") that was on Form2? That is, after making a selection in combobox2, the button will go to Form1 and find the record that matches the selection in combobox2 (on Form2)?

I would think that the logic must change. The following code has been tested and is fully functional, but it may not be the optimal solution since it is getting past my bedtime. Wait and see if some of the other Experts/Moderators/Members come up with a better idea. In the meantime, copy and paste the following code in the AfterUpdate() Event of ComboBox2 on Form2:
[CODE=vb]
Private Sub ComboBox2_After Update()
Const conDESGIN_VIEW As String = 0

If Not IsNull(Me![ComboBox2]) Then
If CurrentProject. AllForms("Form1 ").IsLoaded Then
'Before we go shifting Focus to Form1, let's make sure the EmployeeID
'exists in its Record Source
If DCount("*", "<Record Source for Form1>", "[EmployeeID] = " & Me![ComboBox2]) > 0 Then
If Forms!Form1.Cur rentView <> conDESGIN_VIEW Then 'NOT Design View
Forms![Form1].SetFocus
Forms![Form1]![EmployeeID].SetFocus
DoCmd.FindRecor d Me![ComboBox2]
Else 'Form1 is Loaded but it is in Design View (not good)
MsgBox "Record cannot be found because Form1 is not in the proper View"
End If
Else
MsgBox "No matching Record exists in Form1 with an EmployeeID of " & Me![ComboBox2]
End If
Else
Exit Sub
End If
Else
Exit Sub
End If
End Sub[/CODE]

[bbatson]

Very helpful again. Many, many thanks.

[ADezii]

Very helpful again. Many, many thanks.

You are quite welcome.