By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,034 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,034 IT Pros & Developers. It's quick & easy.

searching recordsets using FindFirst and FindNext

Corster
P: 36
I went through a great deal of hassle to figure this out for myself, but now it is complete, I would like to share it with the world!
I know afew other people have had trouble with FindFirst and FindNext, so I hope this will be helpful!

Expand|Select|Wrap|Line Numbers
  1. 'This is the counter to help with FindNext
  2. Dim vblCnt As Integer
  3.  
  4. Private Sub btnSearch_Click()
  5. On Error GoTo Err_btnSearch_Click
  6.     Dim vblSearch As String
  7.     Dim vblRSC As Recordset
  8.     cbxField.SetFocus
  9.     vblSearch = cbxField.Text
  10.     'Set variable to attain appropriate column referenced to text selected from combobox.
  11.     'This allows you to set the field the user wishes to search without using your field names.
  12.     If cbxField.Text = "My Search Column Option 1" Then
  13.         vblSearch = "[MyColumn1]"
  14.     Else
  15.         If cbxField.Text = "My Search Column Option 2" Then
  16.             vblSearch = "[MyColumn2]"
  17.         Else
  18.             If cbxField.Text = "My Search Column Option 3" Then
  19.                 vblSearch = "[MyColumn3]"
  20.             Else
  21.                 If cbxField.Text = "My Search Column Option 4" Then
  22.                     vblSearch = "[MyColumn4]"
  23.                 Else
  24.                     MsgBox ("Please select the field you wish to search!")
  25.                     Resume Exit_btnSearch_Click
  26.                 End If
  27.             End If
  28.         End If
  29.     End If
  30.     'Set focus to referenced control
  31.     txtSearch.SetFocus
  32.     'Use variable to store clone of current recordset to allow dynaset searching
  33.     Set vblRSC = Me.RecordsetClone()
  34.         'If not first search of specified text, find the succeeding record (Stored in FindNext global variable)
  35.         If vblCnt > 1 Then
  36.             vblRSC.FindNext (vblSearch & " = '" & txtSearch.Text & "'")
  37.             If vblRSC.NoMatch Then
  38.                 MsgBox (" There are no more instances of " & txtSearch.Text & "in" & cbxField.Text & ".")
  39.                 vblCnt = 1
  40.                 vblRSC.Close
  41.                 Exit Sub
  42.             Else
  43.                 'Freeze current record for Me.Refresh
  44.                 Me.Bookmark = vblRSC.Bookmark
  45.             End If
  46.         Else
  47.             'Find the first instance
  48.             vblRSC.FindFirst (vblSearch & " = '" & txtSearch.Text & "'")
  49.             If vblRSC.NoMatch Then
  50.                 MsgBox ("Cannot find " & txtSearch.Text & "in" & cbxField.Text & "; please redefine your search.")
  51.                 vblRSC.Close
  52.                 Exit Sub
  53.             Else
  54.                 Me.Bookmark = vblRSC.Bookmark
  55.             End If
  56.         End If
  57.         Me.Refresh
  58.     'Close the recordset clone
  59.     vblRSC.Close
  60.     If MsgBox("Is this the record you were looking for?" & Chr(13) & "Select 'Yes' to close this message or 'No' if you wish to continue searching.", vbYesNo, "Searching...") = vbNo Then
  61.         'Search for the next instance
  62.         vblCnt = vblCnt + 1
  63.         'Search again by performing another click on the search button
  64.         Call btnSearch_Click
  65.     Else
  66.         vblRSC.Close
  67.         Exit Sub
  68.     End If
  69.  
  70. Exit_btnSearch_Click:
  71.     vblRSC.Close
  72.     Exit Sub
  73.  
  74. Err_btnSearch_Click:
  75.     MsgBox Err.Description
  76.     Resume Exit_btnSearch_Click
  77. End Sub
May 22 '07 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,419
Corster,
Thank you for posting this. This code may well help some of our members if they come across similar situations :)
May 25 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.