databasedev.co.uk - database solutions and downloads for microsoft access

Searching for a record based on a text box value in a form:

There are times, when developing your Microsoft Access databases, that you may wish to allow your users to search for a record in the form that contains a certain value which will be entered into a textbox.

Below shows the example Microsoft Access form containing an unbound text box and command button (Search) which will enable the user to enter a value to be searched on.

Form containing the unbound text search facility.

To perform the search you will need to enter a value (this search is based on the student number primary key) into the unbound text.

The code below is attached to the command button - cmdSearch. This code checks for a value (if no value is entered or no match found it will then return one message and set the focus back to the search field.

If a value is found it will return the appropriate record and will give a message then clear the search control.

'--------------------------------------------------------------
'GJT 25-01-02
'--------------------------------------------------------------
Private Sub cmdSearch_Click()
    Dim strStudentRef As String
    Dim strSearch As String
    
'Check txtSearch for Null value or Nill Entry first.

    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Performs the search using value entered into txtSearch
'and evaluates this against values in strStudentID
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("strStudentID")
    DoCmd.FindRecord Me!txtSearch
        
    strStudentID.SetFocus
    strStudentRef = strStudentID.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
'If matching record found sets focus in strStudentID and shows msgbox
'and clears search control

    If strStudentRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        strStudentID.SetFocus
        txtSearch = ""
        
    'If value not found sets focus back to txtSearch and shows msgbox
        Else
           MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtSearch.SetFocus
    End If
End Sub

To try this example:

1. First click the command button on the form(without typing any value into the search field) and you will be told to enter a value.

   
   Error message when no search value is entered.

2. Next try entering a value of XXXXXXX. The message will advise that no student found with this value.

   
   Error message when an invalid search value is entered.

3. Finally type in DD27676. This will return a match and take you to the corresponding record.

   
   Message when matching search value is found.

Please download the Microsoft Access 2000 example file from the Microsoft Acces Forms main page or from the Microsoft Access Downloads index.