How to let a user know he has entered a duplicate key

[Jimcb8]

I am a new user to access 2010. I encountered a puzzling problem in my form.
I designed the following form in which the top portion of the form, and is the clients basic information. For example, it contains:
First Name
Last Name
Address
City
State, zip

The above information is stored in a table called ClientInformation which has a key called ClientID. The key is 6 positions long and is unique for that table. The clientID is created by the user by using the first 4 letters of the last name and the first 2 letters of the first name

The bottom portion of the form is where the user enters the new clients family household members:

MemberFirstName
MemberLastName
Relationship

The household member information is stored in a separate table called HouseholdMembers, and is linked to the ClientInformation table using the same ClientID.

Now my problem.
The user enters the new client information by creating the ClientID field followed by the name and address data information I described above in the top portion of the form.

The user enters the ClientID data and tabs to the next field which is the clients LastName on the top portion of the form.

If a previous client has the same ClientID data(duplicate ClientID fields), the previous clients Household Memebers data appear in the bottom portion of the form as if this data belonged to the top potion new client.

Access does not alert the user of the duplicate key problem immediately. It only flags the dup key when the user tries to add the record to the ClientInformation table.

How do I alert the user that a duplicate key field is being entered immediately after he enters the key field data, when he tabs to the next field to enter the LastName?

I defined the clientID field in both tables as unique.
I do not know VB so it is difficult for me to try and catch this error.

 

[apr pillai]

If you have already defined ClientID field as Primary Key then copy and paste the following Code into the VBA Module of the Form:

Private Sub Desc_LostFocus()
  Me.Refresh
End Sub

If you are not sure how to do it then do the following:

1. Open the Form in Design View.
2. Click on the ClientID Field to select it.
3. Press ALT+F11 to display the VBA Module of the Form.
4. Copy and paste the above Code into the VBA Module, below the existing Code: Option Compare Database, Option Explicit.
5. Save the Form and open it in normal view.
6. Try to enter a duplicate ClientID value in a new record and press Tab key to move to the next field.

The Me.Refresh statement attempts to update the new record with the duplicate CliendID. This action will force the Primary Key settings to do a validation check immediately, rather than at the end of all fields are filled with values.

 

[jdraw]

It sounds to me that your HouseholdMembers table should have a PK of its own, and also a FK to the ClientInformation table

Client -->HouseholdMembers are linked in a 1 to many relationship.

Then your Form / subform linkage is via LinkMasterField/LinkChildField

Why did you chose the ClientId set up you did?
Duplicates in Access typically error with 3022.

There is a thread here that may be of use to you,
http://answers.microsoft.com/en-us/...ror-3022/1c55e25f-024a-46aa-98a6-6a8070555798

Good luck.

 

[Jimcb8]

apr pillai
This is what I did:
Opened form,clicked on ClientID Field to select,press alt+F11 to display the VBA module. At this point after I did the alt+f11 a screen appeared called "microsoft Visual Basic for Application"
It was blank, so I clicked on the insert icon and was the presented with a screen that had
Option Compare Database as the only line in the module
I added Option Explicit command
then pasted your code below that as you stated
I saved the module and named it dupclientid wnt back to access form then I
I saved the form and tried to enter dup id and got the same results as before.
What am I doing wrong?
Thank you so much for your help and patience with me, I can't thank you enough
Under modules I did see the dupclientId listed
Jim