Searching for a record on the Form is normally done with the help of Find (Ctrl+F) method of Microsoft Access. For the search operations on the Last Name field of employees record, on the Employees Form, it is absolutely necessary that the Last Name data field is present on the Form. 

This is where the SearchForRecord Macro Action mainly makes the difference, besides other flexible features.  You can search for the Last Name of an employee even when this field is not present on the Form.  But the data field must be available on the Record Source (Table/Query) of the Form.  The SearchForRecord Macro Action can accept logical comparisons like <, >, AND, OR and BETWEEN to search and find the required record.  But, the Find method accepts only one of the three options, viz. Whole Field, Any Part of Field & Start of Field to search for a record on any of the available field on the Form.

Let us try out SearchForRecord Macro Action with the help of data from Employees Table of Northwind.accdb sample database.

  1. Import the Employees Table from Northwind.accdb sample database.
  2. Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the help of Form Wizard option from the Forms Group under Create Menu.

    Employees Form Design

  3. Select the Last Name Textbox and Delete it.

    We will try to search and find records using this field, without placing the field on the Form. We will also try the search method with the First Name field on the Form combined with Last Name field (not on the form) and learn the usage of AND, OR Logical Operations in the search criterion in the Macro we are going to create.

  4. Create two Text Boxes and a Command Button at the Footer of the Form, as shown in the image above.
  5. Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
  6. Similarly, change the second Child Label Caption of the second Text Box to First Name and the Textbox Name Property Value to fstName.

    Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.

  7. Save the Form with the name frmEmployees and close it.

  8. Select Macro from Create Menu to open a new Macro design window.

    Sample Macro image is given below:

    Search Macro Design

  9. Select SearchForRecord from the drop-down list in Action Column of the Macro.
  10. Set Form in the Object Type control, in the property sheet below.
  11. Select frmEmployees from the drop-down list of Object Name.
  12. Select First in the Record control.
  13. Type [Last Name] = "Kotasa" in the Where Condition control. 

    NB: You may open the Employees Table to view and select any record value from the Last Name field, preferably after few records from the beginning of the records. Note down the first name of the employee also so that we can cross check the correctness of the record found by the search operation, when the record changes on the form.  Remember we have deleted the Last Name Field  from the frmEmployees Form.

    Note: Initially, we will try this method with a simple constant criteria (easier to understand it’s usage) in the Where Condition control and search for last name of an employee Kotasa in the Last Name field not on the Form.  After that we will modify the macro to use the values typed on the Text Boxes, which we have created on the Footer of the frmEmployees, as search criteria.  This way it will give us much needed flexibility on search operations on the Form, by simply changing the search values on the text boxes.

  14. Save the Macro with the name macSearch and close it.
  15. Open frmEmployees in Design View.
  16. Click on the Command Button at the Footer of the Form to select it.
  17. Display the Property Sheet (F4), if it is not visible.
  18. Change the Name Property value to cmdRun and change the Caption value to Search For Record.
  19. Select On Click Event on the Event tab of the Property Sheet and type the macro name macSearch, or select it from the drop-down list.
  20. Save the Form and open it in Normal View.  You will see the first record on the form is active now.
  21. Click on the Command Button to search the Last Name Kotasa (or whatever last name you have inserted in the criteria) on the Form.

    You will see the record changes on the Form. Check and confirm that the First Name on the form matches with the name you have noted down earlier.  We will modify the Macro to make it more flexible.

  22. Close the frmEmployees for now.

Now, we will modify the Where Condition control settings on the Macro to take the values we type on the Text Boxes (with the names: lstName and fstName) on the frmEmployees Form, rather than using constant values in the search criteria, as we did in the earlier example. We must create an expression to take the lstName and fstName Text Box values joined with the AND Logical operator to conduct search operation on Last Name and First Name fields on the Form.  For this reason we need to take some extra effort to build the expression correctly so that it works every time.  We must join the Data Field names (Last Name, First Name) and Text Box (lstName, fstName) contents combined with the Logical Operator AND  in the expression.

  1. Open the macSearch Macro in Design View.
  2. Copy and paste the following expression into the Where Condition control, replacing existing one.

    ="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"

    The expression starts with an = sign, the field name Last Name have a space in the middle and is placed in square brackets followed by an equal sign for an exact match of value and the whole segment of the expression is placed in double quotes.  Before closing the second double-quote a open single quote is placed because we have joined the text data from the lstName text box on the form.

    The next segment of the expression is opening with a double-quote, and a closing single quote for the first text data, followed by a space and the AND logical operator followed by the First Name field name in square brackets followed by a space, = sign, opening single quote for the fstName text value followed by the closing double-quote of the third segment of the expression.  The fstName text box reference from the form is joined with an ampersand followed by an ampersand to join the closing single quote within double quotes.

  3. Save and close the macro.

    Since, we have used the AND Logical operator both Last Name (doesn’t exists on the and Form) and First Name field values should match to find a record on the Form.

  4. Open the Employees Table and note down last name and first name of few records on paper and close the Table.
  5. Open the Form frmEmployees.
  6. Type last name and first name from the first record, you have noted down earlier, into their respective text boxes on the footer of the Form.
  7. Click the Command Button to run the macSearch to find the record on the form that matches both last name and first name.  Remember, the last name field is not there on the form.  You may repeat this method with the other record values you have noted down earlier, if any.
  8. You may modify the macro to change the AND Logical Operator to OR.  You may try the macro after entering search value in any one of the text boxes (lstName or fstName) or values in both text boxes.  If any one of the two or both values matches with the record then it will be returned.

The modified expression is given below for reference:

="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"