473,320 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 6657
NeoPa
32,556 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

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

Similar topics

4
by: Paolini | last post by:
compiling this using gcc -o xxx xxx.c I obtain this error message in function Main 6 storage size of 'ff' isn't known Can any help me ? #include <stdio.h>
10
by: Frusterated | last post by:
Hi, I'm using Access-2k under Win-XP... When I have a form with imbedded code it works perfect. Such as the following code example. Dim db as DAO.Database Dim rs as DAO.Recordset Set db =...
0
by: imassadpk | last post by:
Hi all, Apprecite your help in resolving this tricky issue... I am trying implementing Recurrsive TreeView in MS. Access 2003 ADP project. So far, no luck :( The Problem: This sample...
10
by: Hughes | last post by:
Hi, I would like to get the filename from a folder by using C language. For example, in path /Users/abc/Desktop/xyz/ there is a file named "test.s" (file "test.s" is inside folder "xyz"). How...
5
by: Steffen Loringer | last post by:
Hi group, may be a simple question: How can I find out in C, which files of a specified extension exist in a specified folder? My app should constantly look for a file with an known extension...
1
by: bmshirey | last post by:
I have a program which manages various log files of multiple extensions. The program needs to delete files based on an age paramter and a file number limit paramter. So if files are older than say...
0
by: David W. Fenton | last post by:
I was just working on an A97 app in which has lots of filtering and dynamic changing of the recordsets, but also uses bookmark navigation of subsets of records. I was trying to implement some...
0
by: JC | last post by:
I have a list box loaded with Student Name, Social Security. When the user click on a specific record, the following code is assigned to the lstStudent1_AfterUpdate procedure. Private Sub...
9
by: drhowarddrfine | last post by:
I don't want to use a db manager, like mysql, for such a small database but I'm finding this trickier than I thought and hope someone can provide some guidance. I have a restaurant menu with...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.