First and last name indexed

[Guest]

I think I am finally figuring out access!!! I have a couple questions

I have it where I can input client /dog information.

I have a form where I input first name and last name of each client.
I have a subform where I input all the dog's information Name, etc.

I know people have same last names, same first names & dogs have same names.

How would I set it up where I don't duplicate the same first & last name of
a client. Also, is there a way I can make sure I don't duplicate the same
dog's name under that particular client.

Joe Smith may have a Spot
Jane Smith may have a Spot

I just don't want to duplicate the same owner & dog

Thanks

 

[Douglas J Steele]

Names are seldom good choices for guaranteeing uniqueness. What happens if
you have two Joe Smiths as customers?

You should have a CustomerId to guarantee uniqueness. You should have a
DogId to guarantee uniqueness (presumably you need to be able to distinguish
between the two Spots)

 

[Guest]

I have set up the ClientID as the primary Key & a DogID for the primary key
of dogs. I am thinking this is what you mean.

Is there a way I can make an alert that I am about to duplicate a name that
is already in the dateabase.

Or would it be better to make a search form so I can search the names, etc.

Which ever is easier to set up.

 

[Douglas J Steele]

You can use DLookup or DCount to check what's already in the table:

If DCount("*", "ClientTable", "ClientName = '" & txtClient & "'") > 0 Then
MsgBox "You already have a client " & txtClient
End If

or

If Not IsNull(DLookup("ClientName", "ClientTable", "ClientName = '" &
txtClient & "'")) Then
MsgBox "You already have a client " & txtClient
End If

Theoretically, the second one should be marginally faster, since it will
stop at the first match, rather than checking the entire table. However,
since you're hoping that there isn't a match, most of the time both will
have to check the entire table.

 

[Guest]

Hi HLarkin - I just add another subform but linked back into the client DB.
Works same way as linking to any other file (like your Pet file). In the
queries I create fields based on the first char of the First Name and the
full Surname and use that as the Link rather than the First and Surname
together as data can be misspelled. I actually have one DB with 3 subforms
all linking back to the Client Data, 1 by Surname only, 1 by First Initial
and Surname, and the last by Firstname and first char of the surname. Is a
bit slow but works a treat and keeps duplicates to almost nill. Hope this
helps - yours Dika