Knowledge Base

ACC95: FindNext Does Not Advance to Next Record to Search

Article ID: 150665

Article Last Modified on 11/17/2000

APPLIES TO

This article was previously published under Q150665

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.
For a detailed discussion of how to implement these techniques, please see the "Workaround" sections following the "More Information" section.

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


  1. Open the modified Customers form from the "Steps To Reproduce Behavior" section in Design view.
  2. Add a new command button to the form with the following properties: 
          Name: NewFindCompany
      Caption: New Find Company

    For the OnClick event procedure, click the Build button and choose Code Builder from the Choose Builder dialog box. Edit the procedure as follows: 
          Private Sub NewFindCompany_Click()
         Me![CompanyName].SetFocus
         DoCmd.FindRecord Me![txtFind], acAnywhere
         RecordFind
      End Sub
  3. Create a second command button with the following properties: 
          Name: NewFindNextCompany
      Caption: New Find Next Company

    For the OnClick event procedure, click the Build button and choose Code Builder from the Choose Builder dialog box. Edit the procedure as follows: 
          Private Sub NewFindNextCompany_Click()
         RestoreFind
         DoCmd.FindNext
         RecordFind
      End Sub
  4. In the Object box at the top of the code window, choose "(General)"
  5. Add the following lines of code to the Declarations section of the form module: 
          Dim FoundControl As Control
      Dim FoundControlSelStart As Integer
      Dim FoundControlSelLength As Integer
  6. Add the following procedures to the General section of the module: 
          Sub RestoreFind()
         FoundControl.SetFocus
         FoundControl.SelStart = FoundControlSelStart
         FoundControl.SelLength = FoundControlSelLength
      End Sub

      Sub RecordFind()
         Set FoundControl = Me.ActiveControl
         FoundControlSelStart = Me.ActiveControl.SelStart
         FoundControlSelLength = Me.ActiveControl.SelLength
      End Sub
  7. View the 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 "Ana Trujillo Emparedados y helados" is selected, as expected.

Keywords: kbprb kbusage KB150665


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.