I have a "search form" that is its own form. The search form has a combo box and a search button. I have a bit of code in the button that currently will take the value of the combo box, look up the primary key field and populate several other forms. So when I use the search, I will have several forms open and populate with the data(the forms share fname, lname, and an ID). Other than that the forms show different information.
I would like to adapt this to work with a navigation form. I have created a navigation form, and added my client data forms to it. What I'm not sure about is how I need to modify the code. I have done extensive searching but I fail to make sense of how to adapt my code.
Private Sub btnClientSearch_Click()
Dim strSQL As String
If Not Nz(cbxLastName) = "" Then strSQL = strSQL & " And [LastName]='" & cbxLastName & "'"
strSQL = Mid(strSQL, 6)
If strSQL = "" Then
MsgBox "Please enter at least one criteria field.", vbExclamation
Exit Sub
End If
Dim strID As String
strID = Nz(DLookup("ID", "[Table1]", strSQL))
If strID = "" Then
MsgBox "No match!", vbExclamation
Exit Sub
End If
FindClient "Form1", strID
FindClient "Form2", strID
FindClient "Form3", strID
FindClient "Form4", strID
End Sub
Private Sub FindClient(strFormName, strID As String)
DoCmd.OpenForm strFormName
Dim rs As Recordset
Set rs = Forms(strFormName).RecordsetClone
rs.FindFirst "ID='" & strID & "'"
If Not rs.NoMatch Then Forms(strFormName).Bookmark = rs.Bookmark
End Sub
I believe the changes need to be made in the FindClient method.
Help or suggestions would be greatly appreciated. Thanks.