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

Finding records using a VB button

P: 1
I'm an access 2003 novice trying to improve the search ability of my club membership database. I am currently searching for the members surnames by simply running a query (Please enter surname in the critieria field or the query) which then returns the records of everyone matching the surname entered. This is pretty rubbish as there is no error handling functionality and requires the user to scroll through the records if more than one person with the same surname exists.

Therefore, is there a simple way to improve the search function by using VB or SQL code which would allow error handling, should there be no record found?

Cheers for your help.
Mar 14 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,532
I would first use DCount to see if you're going to get a match, and then if a match exits, run your query.

Where RunSearch is the command button, Surname is the name of the field in the table ClubTable, and SearchName is the control on the form where the search string is entered. You'll need to substitute your own actual names for these, of course.
Expand|Select|Wrap|Line Numbers
  1. Private Sub RunSearch_Click()
  2. If DCount("[Surname]", "ClubTable", "[Surname]= '" & Me![SearchName] & "'") < 1 Then
  3.   Response = MsgBox("There are no records that match!", vbOKOnly, "Match Not Found!")
  4.    Me.SearchName = ""
  5. Else
  6.    'Place your query here
  7.    Me.SearchName = ""
  8. End If
  9. End Sub
This is pretty rubbish as...the user (has) to scroll through the records if more than one person with the same surname exists.
A query seems the appropriate method to use when the possibilty of the search returning more than one record exists. I'm really not sure how you expect to move thru multiple records except to scroll thru them.
Mar 17 '07 #2

Expert Mod 15k+
P: 31,662
This may, or may not, help (Example Filtering on a Form.).
You will still need to consider the situation where none is found, but you can, with intelligent use of filtering, reduce the number of matches severely by more filtering. See what you think anyway.
Mar 20 '07 #3

Post your reply

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