Search for Record Macro Action Access2007 ~ LEARN MS-ACCESS TIPS AND TRICKS

Search for Record Macro Action in Access 2007.

Searching for a record in the Form is normally done using the Find (Ctrl+F) method in Microsoft Access. To search an employee's last name in the Employees Form, the Last Name data field must be present in the Form.

This is where the SearchForRecord Macro Action makes the difference, besides other flexible features. You can search for the Employee's Last Name even when this field is not present in the Form. But the data field must be available on the Record Source (Table/Query) on the Form. The SearchForRecord Macro Action can accept logical comparisons <, >, AND, OR, and BETWEEN for searching and finding 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 fields on the Form.

Prepare for a Test Run.

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

Import the Employees Table from the Northwind sample database.
Design the Form frmEmployees in Columnar Format (see the sample image given below). You can do this quickly with the Form Wizard option.

Right-click to open Large Image in New Tab/Window.
Select the Last Name Textbox and delete it.
We will search and find Employee records using this field without the Last Name field on the Form. We will also try the search method with the First Name field on the Form, combined with the Last Name field (not on the form), and learn the logical operators AND and OR, with the search criteria we create in the Macro.
Create two TextBoxes and a Command Button in the Form Footer as shown in the image above.
Change the Child Label Caption value of the first Text Box to Last Name and the Textbox Name Property Value to lstName.
Similarly, change the second Child Label Caption of the second Text Box to First Name, and the Textbox Name to fstName.
Before making changes to the Command Button Properties, we must create a Macro with the SearchForRecord Action.
Save the Form as frmEmployees and close it.

Creating a Macro.

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

The Sample Macro image is given below:
Right-click to open Large Image in New Tab/Window.
Select SearchForRecord from the drop-down list in the Macro Action Column.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the Object Name drop-down list.
Select First in the Record control.
Type [Last Name] = "Kotasa" in the Where Condition control.

NB: You may open the Employees Table to view and select any record from the Last Name field, preferably after skipping a few records at the beginning. Note down the employee first name so that we can cross-check the accuracy of the search operation. Remember, we have deleted the Last Name Field from the frmEmployees Form.

Note: Initially, we will try this method with simple constant criteria (easier to understand its usage) in the Where Condition control and search for the 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 value input in the TextBoxes we created on the Footer of the frmEmployees as search criteria. It will give us much-needed flexibility in search operations on the Form by simply changing the search values in the text boxes.
Save the Macro as macSearch and close.

The Form Design Change.

Open frmEmployees in Design View.
Click on the Command Button at the Footer of the Form to select it.
Display the Property Sheet (F4) if it is not visible.
Change the Name Property value to cmdRun and change the Caption value to Search For the Record.
Select the 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.
Save the Form and open it in Normal View. You will see the first record on the form is active now.
Click the Command Button to search for the Last Name Kotasa (or whatever the last name you have inserted in the criteria) on the Form.
You will see the record change on the Form. Check and confirm that the First Name on the form matches the name you noted down earlier. We will modify the Macro to make it more flexible.
Close the frmEmployees for now.

The Condition Control Settings
Now, we will modify the Where Condition control settings in the Macro to use the values from the lstName and fstName TextBoxes 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 that combines the lstName and fstName TextBox values with the AND logical operator to perform the search on the Last Name and First Name fields on the Form. For this reason, we need to take extra care when constructing the expression to ensure that it works correctly every time. The expression must combine the data field names (Last Name and First Name), the corresponding TextBox values (lstName and fstName), and the AND logical operator.

Open the macSearch Macro in Design View.
Copy and paste the following expression into the Where Condition control, replacing the existing one.
```
="[Last Name] = '" & [lstName] & "' AND [First Name] = '" & [fstName] & "'"
```
The Search Criteria Expressions.
The expression begins with an = sign. The field name Last Name, which contains a space, is enclosed in square brackets followed by the '=' sign to specify an exact value match. The entire segment of the expression is enclosed in double quotation marks. Before the closing double quotation mark, an opening single quotation mark is included because the text value from the lstName text box on the form is concatenated with the expression.

The next segment begins with a double quotation mark, and contains the closing single quotation mark for the first text value, followed by a space and the AND logical operator. This is followed by the First Name field name enclosed in square brackets, a space, an equal sign (=), and an opening single quotation mark for the fstName text value. The segment ends with the closing double quotation mark. The fstName text box reference from the form is concatenated with an ampersand (&), followed by another ampersand to join the closing single quotation mark enclosed within double quotation marks.
Save and close the macro.

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

Open the Employees Table, and note down the Last Name and First Name of a few records on paper and close the Table.
Open the Form frmEmployees.
Type the Last Name and First Name of the first record you have noted down earlier into their respective Unbound TextBoxes in the Footer of the Form.
Click the Command Button to run the macSearch Macro and find the record on the form that matches both the 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 noted down earlier, if any.
You may modify the macro to change the AND Logical Operator to OR. You may try the macro after entering the search value in only one of the text boxes (lstName or fstName) or values in both text boxes. If any or both values match the record, then it will be returned.

The modified expression is given below for reference:
```
="[Last Name] = '" & [lstName] & "' OR [First Name] = '" & [fstName] & "'"
```