Syncing one Microsoft Access form with another is made simple with this VBA function. It finds and moves to a record in a form by looking for a value in a specific field that is in the form’s recordset. For example, this would allow you to click on an employee ID in one form and automatically open the employee’s profile record in a different form.
If the search form is not already open, it will open it hidden before searching, and then make it visible if it finds a matching record. If the function cannot find the record it is looking for, the search form will be closed. If the search form is already open, the function attempts to find the record in the open form and it leaves the form open whether or not it finds the matching record.
The type of field matters in the search string, so three kinds of field types are accounted for: date, number, and text. The function, as it is written, does not handle searches in a subform.
basFindInForm
' basFindInForm() Version 1.0.0
' Copyright © 2023 Extra Mile Data, www.extramiledata.com.
' For questions or issues, please contact [email protected].
' Use (at your own risk) and modify freely as long as proper credit is given.
'
Public Enum fifType
fifDate
fifNumber
fifText
End Enum
'
Public Function FindInForm(SearchForm As String, SearchField As String, _
SearchFieldType As fifType, SearchValue, _
Optional FocusControl As String = "") As Boolean
' This finds the record in SearchForm that has SearchValue as the value
' for SearchField.
' It assumes that SearchForm is not a subform.
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim frm As Form
Dim blnLoaded As Boolean
Dim strPrompt As String
Dim strSearch As String
' If there is nothing to search for, then exit with a True.
If IsNull(SearchValue) Then
FindInForm = True
GoTo Exit_Proc
End If
' If the SearchForm is not already open, open it.
If CurrentProject.AllForms(SearchForm).IsLoaded Then
blnLoaded = True
' Have to hide it here or there is a problem with focus later.
Forms(SearchForm).Visible = False
Else
blnLoaded = False
DoCmd.OpenForm SearchForm, , , , , acHidden
End If
' Get the form object.
Set frm = Forms(SearchForm)
' Build the search string based on the type.
Select Case SearchFieldType
Case fifDate
strSearch = "#" & SearchValue & "#"
Case fifNumber
strSearch = SearchValue
Case fifText
strSearch = "'" & Replace(SearchValue, "'", "''") & "'"
End Select
' Find the record.
Set rst = frm.RecordsetClone
With rst
.FindFirst "[" & SearchField & "]=" & strSearch
If .NoMatch Then
' Message the user.
strPrompt = "The record could not be found."
MsgBox strPrompt, vbInformation, "Search Failed"
' Close the SearchForm if it was not orginally open.
If Not blnLoaded Then
DoCmd.Close acForm, SearchForm
End If
' Pass back a False.
FindInForm = False
Else
' Sync the form with the found record.
frm.Bookmark = .Bookmark
' Make the form visible and set focus.
frm.Visible = True
frm.SetFocus
If Len(FocusControl) <> 0 Then
' Set focus on the FocusControl control.
frm.Controls(FocusControl).SetFocus
End If
FindInForm = True
End If
End With ' rst
Exit_Proc:
On Error Resume Next
rst.Close
Set frm = Nothing
Exit Function
Err_Handler:
MsgBox Err.Number & " " & Err.Description, vbInformation, "FindInForm()"
FindInForm = False
Resume Exit_Proc
End Function
Making Microsoft Access Work. Call (505) 369-1650.