505.369.1650 [email protected]

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() 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
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
        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
            ' Sync the form with the found record.
            frm.Bookmark = .Bookmark
            ' Make the form visible and set focus.
            frm.Visible = True
            If Len(FocusControl) <> 0 Then
                ' Set focus on the FocusControl control.
            End If
            FindInForm = True
        End If
    End With ' rst

    On Error Resume Next
    Set frm = Nothing
    Exit Function

    MsgBox Err.Number & " " & Err.Description, vbInformation, "FindInForm()"
    FindInForm = False
    Resume Exit_Proc
End Function