Next Tip  Find Record Lookup Wizard in Access 2007 (and Earlier)

Here are some screen shots from the Find Record wizard in Access 2007. This sets up a find record combo box and the VBA code that goes with it.

Built In Search

Here are some inbuilt options for search that are available to you when you use an Access 2007 form.

 

Alternative Code: Preferred by Garry

This example shows you how to setup VBA code to find a record based on the users choice in a combo box.

Sub cboFindRecord_AfterUpdate() 

' This code can be used to replace the combo box wizard code
' Line 1 is necessary to ensure that the FindRecord Method
' is working on the correct field.
' FindRecord on Line 2 has many options. 

  Me![RowNumber].SetFocus
 
DoCmd.FindRecord Me!cboFindRecord, acEntire 

' Note that you can subsequently use the docmd.FindNext method
' to find the next record with the same entry in the combo box 

End Sub

The DoCmd.FindRecord method searches the records currently visible to the form in their primary state and stops the cursor on the first record that matches the search criteria (selected in listing 2 cboFindRecord combo box).  FindRecord is exactly the same process as that used by the Binoculars Button on the Forms toolbar.  If you select the Find Record Toolbar button,  the Find in field choose box is a very good illustration of the different options that are available for the FindRecord method.

 

Figure 3 Illustration of the Find Record Button and the options that are given to find a record.

Prior to running the FindRecord method  in the AfterUpdate event of the combo box , the important trick is to set the focus to the field that you are going to search on as follows

  Me![RowNumber].SetFocus
 
DoCmd.FindRecord Me!cboFindRecord, acEntire 

The find record has many options as shown in Figure 3 ranging from Search only current field,  Search Whole Field, Start Of Field and Any Part of Field, Match Case and Search Field As Formatted. 

The little bonus here is that the next time you run the Find Button on the toolbar, it will have the settings from the last time that you issued the FindRecord method.    If you have ever been frustrated having to change Match Whole Field to Match Any Part of Field, this is a little time saver.

You also can add the DoCmd.FindNext method to continue searching on through your data set to find the next record that matches the current search criteria.

 

Author Bio.
Garry Robinson is the founder of GR-FX Pty Limited, a company based in Sydney, Australia. Garry has written over 40 geological, metallurgical and environmental solutions in Access plus Graf-FX, a shareware data mining tool and object library designed for Access 2000 though to 2007. Contact details  in Australia     Web http://www.gr-fx.com/

  


Published  2008-07

Our Tools and Resources

  • RSS & Newsletter Here
    Join our newsfeed or sign up for our informative newsletter on Office Automation, Access and VB topics
  • Get Good Help
    If you need help with a database, our Australian Professionals could be the answer

  • Smart Access is online 
    The best magazine written about Microsoft Access is now being transferred to the web. There are 400 articles written by a 100 authors in the collection.  Purchase Smart Access

  • The Workbench
    Find out who has your database open, start the correct version of Access, easy compacting and backups, change startup options, creation versions,  shutdown database

  • Convert Access to SQL Server  
    Upsize to SQL Server 2005 or 2008, easily repeated conversions, highly accurate SQL query  translation and web form conversion.
  • Purchase the Popular FMS Products  
    If you purchase the Popular FMS products from us, you will receive a complimentary of Smart Access Gold, Silver or Bronze Collections [Your choice]

 

 

vb123 Professionals


Get Good Help Here

If you need help with a database or Office programming, our Professionals could be the answer because we have worked on many similar solutions



Frontpage Conversions
We have converted vb123.com to Expression Web, contact us if we can help you move to the latest Microsoft web tool.


About The Editor ~ Contact Us
Garry Robinson writes for a number of popular computer magazines, is now a book author and has worked on 100+ Access databases. He is based in Sydney, Australia

Upsize Access to SQL Server

The Access tool that we use to upsize Access databases to SQL Server is called MUST. It not only sorts out most of the problems that stop the Access upsizer but it makes building more effective Access to SQL solutions easier.

Read More here