Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Search for Record Macro Action Access2007


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 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 fields on the Form.

Prepare for a Test Run.

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 the Form Wizard option from the Forms Group under Create Menu.
  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 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.

  4. Create two TextBoxes 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.

    Creating a Macro.

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

    Sample Macro image is given below:

  9. Select SearchForRecord from the drop-down list in the 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 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 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.

    The Form Design Change.

  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 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 have noted down earlier.  We will modify the Macro to make it more flexible.

  22. 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 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 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 (doesn’t exist on the and Form) 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 few records on paper and close the Table.
  • Open the Form frmEmployees.
  • 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.
  • 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.
  • 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] & "'"


    1 comment:

    1. Your style is so unique compared to other people I have read stuff from. Many thanks forposting when you have the opportunity, Guess I will just bookmark this site Macro Recorder


    Comments subject to moderation before publishing.



    MSA GURU : Access Tips & Tricks App

    • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.



    Subscribe in a reader
    Your email address:

    Delivered by FeedBurner


    Popular Posts

    Blog Archive

    Powered by Blogger.


    Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts ListView Control Query VBA msaccessQuery Calculation Event ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Android App Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code