Looking for Existing Records

[Anders]

Howdy,

What type of expression do I create if I want to search a table (take
customers) to see if someone already exists? My customer table contains the
usual contact information. Everything I've seen on Help is setting the query
before hand (e.g. in the first name column, Like "Mar*" would return anybody
with Mar in the first name). I would love to find a way to have the user
define that criteria, but not have to setup a query.

Ideal world, the user clicks a button on the switchboard, then 3 consecutive
popup windows ask for the first three letters of 3 fields; first name, then
last name, then organization. Is this possible?

Greatly appreciated!

Anders

 

[Allen Browne]

Use DLookup(). Like this:

Dim strWhere As String
dim varResult As Variant
strWhere = "(LastName = ""Jones"") AND (FirstName = ""Anders"")"
varResult = DLookup("ClientID", "tblClient", strWhere)
If Not IsNull(varResult) Then
MsgBox "Found! Client ID is " & varResult
End If

More info about DLookup() in:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[TedMi]

Matching names is more art than science, because of possible variations in
spelling. Are Jon Smith, Jonathan Smith and John Smith the same or different
persons?Here's the method I use for this:
Present a "New Customer" form, on which the user enters only the last name
of a supposedly new customer. Query the customer table and return all names
& addresses which match the input name. Ask the user if any of the displayed
names are the "new" customer; if not, have user click button which presents
a full customer entry form. Nice touch - have the last name transferred from
the new customer form to the full form.
-TedMi