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 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.
  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:

  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] & "'"


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


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

Featured Post

ROUNDDOWN Function of Excel

We have introduced ROUNDUP() Function of Excel in Microsoft Access earlier.  It works like ROUND() Function except that the number is alway...


Blog Archive

Recent Posts