Preventing Duplicate Entries in Linked Table (different thread)

[John V]

Hi there,



There is already a post on this same topic, but that conversation is still ongoing and I didn't want to hijack that post.

I have a Contact Information Form where the user enters the usual information regarding Patient Contact Information. One of the items that needs to be entered into a Textbox is a Patient ID Number. This Patient ID Number is the PK on the underlying table and it becomes the FK for another table in my database.

I would like to check if the user has entered a duplicate number using the BeforeUpdate event for the Textbox. To this end, I have entered this code into a Private Sub for the BeforeUpdate for the Textbox control:

If DCount("[Patient ID]", "[Contact Information]", "[Patient ID] = '" & Me.txtPatientID & "'") > 0 Then
Cancel = True
MsgBox ("Patient ID Number is already in use!"), vbOKOnly, "Duplicate ID"
End If

This works great until I press OK in the message box. At that point, up pops another message box warning me about violating the data validation for the field. I have checked (and double checked) and I do not have any Data Validation for this Field in the Table, or for the Textbox on the Form.

What I actually want to have happen is, my message box comes up, and when the user clicks OK, it clears the entry in the textbox, and waits for a different entry.

What am I doing wrong?

John V

PS I have also tried putting the statement:

Me.txtPatientID.Undo

into the code in various places, to no avail.

[ssanfu]

I just tried to duplicate your problem - I cannot get the same response.
I would try deleting the control and re-creating it.

Also, while you can use a text field for the PK, it is not really a good idea.
You might want to read:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


I only use autonumber fields for the PKs in tables. You could set an index on the control "txtPatientID" -> "Indexed - No duplicates". The error message is OK, but you could use your code to display a customized message.

[NTC]

so I think the key here is your statement: "...and I do not have any Data Validation for this Field in the Table, or for the Textbox on the Form."

If the field is indeed the PK then Access will not allow a duplicate and what you are seeing is a system message. So that means your Cancel is not working. Try the undo method.

[John V]

NTC. I got rid of the Cancel Statement and the validation warning no longer pops up but the cursor moves to the next Textbox on the Form and it does not clear the data in the Patient ID Textbox.

What I want to happen is:

1) My message box pops up
2) The data in the Patient ID Textbox is cleared
3) The cursor is placed back in the empty Patient ID Textbox

Basically the user cannot move on until they enter a unique Patient ID.

I tried rearranging my table layout and using Autonumbering for my PK as Steve suggested. This didn't work and caused other problems on this form and other forms in my DB. I tried all kinds of combinations for rearranging my tables and their primary keys and made sure I tracked these things through to the properties of the various Forms and their Controls. In the end I have come back to my original layout there.

I did change the Patient ID to a Number (Long Integer) datatype, also as Steve suggested.

Getting rid of the Cancel statement is the closest I have come to getting the results that I want, but, I have the issues mentioned above.

I assume the focus has moved from the Patient ID Textbox since the cursor is at the next Textbox on the Form. I cannot, however, move the focus back because I have not saved the value, and I can't clear the data because the value has not been saved.

I thought maybe I need to change this to an AfterUpdate event, but realized that wouldn't work because AfterUpdate is to late to keep the system warnings from kicking in (which is what I want to avoid).

Any help is appreciated.

Thank you

John V

[NTC]

My prior post was only pointing out that you were getting the system error of entering a duplicate key value; so your cancel was not preventing that entry - and suggested the Undo method. You may indeed still need the cancel command. I would think you would want apply them in the BeforeUpdate Event.

[ssanfu]

Any chance you could post your dB for analysis?
Munge sensitive data, only need a few records. Compact and repair, then zip it.

[John V]

Steve,

I appreciate you taking an interest in my problem. I have attached a copy of my database. Hopefully I have done this correctly.

All I want is to check that the user is not trying to enter a duplicate Patient ID Number on the Contact Information Form, as this is the Primary Key on my Contact Information Table (the Table the Form Controls are bound to).

I want to bypass the system warnings popping up as I find these to be confusing to the end user.

Thank you for your time on this matter.

John V

Wetaskiwin (6).zip

[ssanfu]

You could try the Form Error event. Delete the code for the patient id after update event and add the following.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Const conDuplicateKey = 3022
    Dim strMsg As String

    If DataErr = conDuplicateKey Then
        '    Response = acDataErrDisplay
        Response = acDataErrContinue
        strMsg = "Each patient record must have a unique " _
               & "ID number. Please recheck your data."
        MsgBox strMsg
        Me.Undo
        Me.txtPatientID.SetFocus

    End If
End Sub

There is a small problem with this code. If someone entered a duplicate patient number, you don't get the error until you try and save the record. The "Undo" command clears the record, so you have to re-enter ALL of the info again.

I think the best thing to do is change the PK to an autonumber and use code (and/or index the field) in the "Patient ID" after update event to check for duplicate patient IDs.

A few Problems that I see:
There are spaces in object names (fields, tables, forms). Only the programmer should see object names, so "Proper" English is unimportant. Use the underscore or "CamelBack" to distinguish words. Short, descriptive names for objects are more important. Labels are where you can be verbose.
Example: a field named "ApptDate". The label on the form for that control (attached or unattached) could be "Appointment Date for prenatal exam".
Long names and spaces are a real pain.

Misnamed primary keys. The primary key field for the table "Appointments" is "Date ID" and is linked to other tables by the foreign key "Date ID".
I always name the primary key/foreign key pair with the name of the table.
Table "tblCustomers" would have a PK of "CustID_PK" and the FK would be "CustID_FK".
Table "tblAppointments" would have a PK of "ApptID_PK" and the FK would be "ApptID_FK".

Look up FIELDS.... I never use look up fields (look up tables are a different matter). See http://access.mvps.org/access/lookupfields.htm

Recommendations:
read the following about Primary keys:

Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Selecting the Right Primary Key in Microsoft Access
http://www.bluemoosetech.com/microso...ial.php?jid=54

And related to the above topics:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


I always use an autonumber for the table PK. Then if I have to change the patient ID or use letters & numbers in the patient ID, the links to other tables are maintained. No one should see the PK/FK.

I would not use the built in search function because it also exposes the Replace function.


I spent some time in your dB. One of my conventions is to have the PK as the first field in the table, then all of the FK fields. After that are the rest of the fields, grouped by type. All of the text fields, number fields, date fields and boolean fields are grouped together. Since the order of the fields in a table is not important, grouping the fields makes it easier for me to find a specific field.


-------------
I apologize.... you posted about a specific question and I blabbed on and on.
But I think you will have more problems because of the current design. In 6 months you will look at the dB and say "What was I thinking???"
Been there, done that, got 3 tee shirts to prove it.
You wouldn't believe how bad my first dB was.

[John V]

Thank you so much Steve for having a look at my DB. I will make the changes you suggest. I will also try out the code you posted and the other suggestions you made for checking for duplicate entries.

John V