Flag dup keys in main form

[Jimcb8]

I have a NEW Client form that the user enters basic data i.e. name and address.
This table called "ClientInformation" uses a 6 character key called CLIEDTID and
is unique for that table.(ClientID is the 1st 4 characters of last name and
first 2 char of first name. )

As part of the NEW Client Form I also have a subform, which is linked to a table
called Household, where the user enters all the people in their household, also
keyed unique using the same ClientID

Now the problem:
When a user is entering the ClientID data, and then tabs to the next field to
enter the LastName data:
If the ClientID is NOT unique,(another client happens to have the same id which
is very rare.
The other client's data is displayed in the household members subform. Which, of
course, does NOT belong to the new Client being entered.
Eventually, Access flags the duplicate ClientID after all of the data in the
basic section is entered, when it tries to add the record into the
ClientInformation table.

Is there a way to flag the duplicate ClientID as soon as the users tabs to the
next field( which is LastName)? It would be great to warn the user immediately
of the dup ClientID that was entered so the user can change the ClientID
immediately. (I tell the user to append a number(1-9) as the last character to
make the ID unique, if they see information appearing in the household section.

Your thoughts are appreciated.

 

[pbaldy]

One way would be with a DCount using the client ID as the criteria. Got to head out to dinner, but post back if you're stuck.

 

[Jimcb8]

Could you please explain how to do this. I'm not clear what to do.

Thanks so much.

Jim

 

[pbaldy]

Along the lines of:

If DCount(...) > 0 Then
  'already exists
End If

http://www.mvps.org/access/general/gen0018.htm

 

[Jimcb8]

Paul,
Thank you for all your help. I'll try not to be nuisance to you.

Best Regards,

Jim

 

[pbaldy]

You're not a nuisance. Post back if you're still stuck.

 

[Jimcb8]

I was able to make it work properly, thanks to you.
I created an event when the ClientID lost focus;
passed the data from the navigation subform to the VB code you supplied;
created a MsgBox to alert the user of the dup key;
it worked like a charm.

I would have never thought of the solution using DCount that you provided.

Thank you Thank you so much. You are the best.

Jim

 

[pbaldy]

Happy to help!