Finding records using a VB button

[themillerman]

Hi,
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.

[missinglinq]

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.

Private Sub RunSearch_Click()
If DCount("[Surname]", "ClubTable", "[Surname]= '" & Me![SearchName] & "'") < 1 Then
  Response = MsgBox("There are no records that match!", vbOKOnly, "Match Not Found!")
   Me.SearchName = ""
Else
   'Place your query here
   Me.SearchName = ""
End If
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.

[NeoPa]

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.