ACC95: FindNext Does Not Advance to Next Record to Search

• Microsoft Access version 7.0

SYMPTOMS

When the FindNext macro action is run from a command button on a form to find the next instance of the search criteria specified by the previous FindRecord action, the first instance is found repeatedly. The FindNext macro action fails to advance to the next record to find the next instance of the search criteria.

NOTE: The same behavior can be demonstrated using the FindRecord macro action with the FindFirst argument set to No.

CAUSE

Microsoft Access for Windows 95 and Microsoft Access version 2.0 behave differently in regard to where the FindNext macro action begins the search for the next instance of the search criteria.

The FindNext action in Microsoft Access version 2.0 always begins searching starting with the next record in the recordset. In Microsoft Access for Windows 95, if the currently selected text is the same as the search text at the time the FindNext macro action is invoked, the search begins immediately following the selection in the same field as the selection, and in the same record. Otherwise, it begins searching at the start of the current record.

The advantage to the Microsoft Access for Windows 95 behavior is that you can repeatedly find multiple instances of the same search criteria that might appear in a single record. The FindNext macro action in Microsoft Access version 2.0 is limited to finding only the first instance of the search criteria in a record, regardless of other instances of the same search criteria in the record.

The problem occurs in Microsoft Access for Windows 95 when you push a custom Find Next command button on a form. When this happens, focus moves from the current control to the button. If text is selected in the current control, it will not remain selected when the focus moves to the button. The FindNext action will then begin searching from the start of the record because the current selection is no recognized. The first instance of the search criteria will be found repeatedly in the same record.

RESOLUTION

Use one of the following techniques to work around this behavior:

1. Use a different mechanism to invoke the FindNext action that allows focus to remain in the current control. This can be accomplished in one of the following ways:

       - Use a custom Toolbar button rather than a command button.
   

       - Use a function key or keystroke invoked by a AutoKeys macro.
   

2. Use Visual Basic code to reset focus back to the control that contains the selection before invoking the FindNext macro action.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the sample database Northwind.mdb.

2. Create a new macro called "Find" with the following actions:

         Macro Name    Macro Action
         --------------------------
         Company       GoToControl
                       FindRecord
   
         Company Actions
         --------------------------------------------
         GoToControl:
            ControlName: CompanyName
         FindRecord:
            Find What: =[Forms]![Customers]![txtFind]
            Match: Any Part of Field
            Match Case: No
            Search: All
            Search As Formatted: No
            Only Current Field: Yes
            Find First: Yes
   
         Macro Name     Macro Action
         ---------------------------
         NextCompany    GoToControl
                        FindNext
   
         NextCompany Actions
         ---------------------------
         GoToControl:
            ControlName: CompanyName
   
      NOTE: You can optionally use the FindRecord macro action in place of
      FindNext. Use the same arguments as the FindRecord macro action with
      the exception of the FindFirst argument, which should be set to No.
   
      NOTE: Do not omit the equals sign (=) to the left of
      [Forms]![Customers]![txtFind] in the FindWhat argument above.
   
   

3. Open the Customers form in Design view.

4. Add a new text box to the form with the following properties:

         Name: txtFind
         ControlSource: <leave empty>
         DefaultValue: "a"
   

5. Add a command button to the form with the following properties:

         Name: FindCompany
         Caption: Find Company
         OnClick: Find.Company
   

6. Create a second command button with the following properties:

         Name: FindNextCompany
         Caption: Find Next Company
         OnClick: Find.NextCompany
   

7. View the Customers form in Form view.

8. Click the Find Company command button to find the first customer record that contains the letter "a". Note that the "A" in "Alfreds Futterkiske" is selected, as expected.

9. Click the Find Next Company command button to find the next instance of the letter "a" in the Company Name field. Note that the "A" in "Alfreds Futterkiske" is selected again. This is not expected.

Workaround 1: Using a Custom Toolbar Button

1. While the Customers form with the modifications made in the "Steps To Reproduce Behavior" section is open in Form view, using the right mouse button (right click), click the toolbar and choose Customize.

2. Choose "All Macros" from the Categories list.

3. Drag "Find.Company" from the Objects list to the toolbar.

4. Right click the new toolbar button and choose "Choose Button Image."

5. Click to select the "Text" check box and type "Find Company" in the text box to the right. Click OK.

6. Drag "Find.NextCompany" from the Objects list to the toolbar.

7. Right Click the new toolbar button and choose "Choose Button Image."

8. Click to select the "Text" check box and type "Find Next Company" in the text box to the right. Click OK.

9. Click the Close button in the Customize Toolbars dialog box to return to the Customers form.

10. Click the Find Company toolbar button. Note that the "A" in "Alfreds Futterkiske" is selected, as expected.

11. Click the Find Next Company toolbar button. Note that the first "A" in "Ana Trujillo Emparedados y helados" is selected, as expected.

12. Click the Find Next Company toolbar button again. Note that the second "a" in "Ana Trujillo Emparedados y helados" is selected, as expected.

Workaround 2: Using the F3 Key

1. Create the following new macro and name it AutoKeys:

         Macro Name     Macro Action
         ---------------------------
         {F3}           FindNext
   
   

2. Open the modified Customers form from the "Steps To Reproduce Behavior Section" in Form view.

3. Click the Find Company command button to find the first customer record that contains the letter "a". Note that the "A" in "Alfreds Futterkiske" is selected, as expected.

4. Press the F3 key to repeat the search. Note that the "A" in "Ana Trujillo Emparedados y helados" is selected, as expected.

Workaround 3: Using Visual Basic with a Command Button