468,107 Members | 1,363 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,107 developers. It's quick & easy.

searching recordsets using FindFirst and FindNext

Corster
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
1 5850
NeoPa
32,021 Expert Mod 16PB
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.

Similar topics

4 posts views Thread by Paolini | last post: by
10 posts views Thread by Frusterated | last post: by
10 posts views Thread by Hughes | last post: by
5 posts views Thread by Steffen Loringer | last post: by
1 post views Thread by bmshirey | last post: by
reply views Thread by David W. Fenton | last post: by
9 posts views Thread by drhowarddrfine | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.