MS Access Find Record Combo Box

Find Record ~ Record Set Clone - Bookmark Alternative

Iis the record set clone approach for finding a record and then managing its contents the best way to tackle the job ? This article outlines an alternative approach

The Recordset Clone

We have 500 records in a table called Table1. The first few records of this set are

RowNumber	textField
1	No Change
2	No Change
3	No Change
4	No Change

Here is the code from the Find A Record Combo Wizard that comes with Access. Both the errant record set and a useful solution are illustrated in Listing 2 & 3

Private Sub cboFindClone_AfterUpdate()

' This code is generated by the Access Combo Box Wizard entitled"
' Find A Record Based On A Value I Select In My Combo Box
' Find the record that matches the control.

Me.RecordsetClone.FindFirst "[RowNumber] = " & Me![cboFindClone]
Me.bookmark = Me.RecordsetClone.bookmark

End Sub

Listing 2 Shows the recordsetClone software produced by the Combo Box Find Record Wizard

Why not try Find Record Method

Sub cboFindRecord_AfterUpdate()

' This code is the suggested 2 lines of code that 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. Search Access for help on FindRecord

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

Listing 3 shows the use of the FindRecord Method to give exactly the same functionality

The Explanation

As far as I can tell, 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.

For an alternative approach run the Command Button Wizard and Choose either the Find record and Find Next wizards. If you are searching a large table on a network, you probably should be doing this as combo boxes involve transfer large amounts of data to the local PC prior to selection of a record.

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 2, 95 and 97. Contact details +61 2 9665 2871 Web http://www.gr-fx.com/