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

Introduction.

Searching for a record on the Form is normally done with the help of the Find (Ctrl+F) method of Microsoft Access. For the search operations in the Last Name field of the employee's record, on the Employees Form, it is absolutely necessary that the Last Name data field is present in 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) on 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 fields on the Form.

Prepare for a Test Run.

Let us try out SearchForRecord Macro Action with the help of 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 help of the Form Wizard option from the Forms Group under Create Menu.
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 the 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.
Create two TextBoxes and a Command Button at the Footer of the Form, 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 Property Value to fstName.
Before making changes to the Command Button Properties we must create a Macro with the SearchForRecord Action.
Save the Form with the name 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:
Select SearchForRecord from the drop-down list in the Action Column of the Macro.
Set Form in the Object Type control, in the property sheet below.
Select frmEmployees from the drop-down list of Object Name.
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 value from the Last Name field, preferably after a 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 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 values typed on the TextBoxes, which we have created on the Footer of the frmEmployees, as search criteria. This way 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 with the macro name - 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 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 on the Command Button to search the Last Name Kotasa (or whatever the 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 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 on the Macro to take the values we type on the TextBoxes (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 TextBox values joined with the AND Logical operator to conduct a search operation in the 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.

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 starts with an = sign, the field name Last Name has 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 an 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.
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 text boxes on the footer of the Form.
Click the Command Button to run the macSearch 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 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 the record, then it will be returned.

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