Help with form code to prevent dups.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 27 Apr 2005 22:34 GMT

I have been trying for weeks to come up with a way to prevent duplicate
entries through the form. Several other trys on the newsgroups have proven
unsuccessful. I have a tbl of "Account" with a field of [Cert_ID]. I have
a form named "Accounts" with a texbox named "Cert_Id" and control sourcee of
"Cert_ID" The "Cert_ID" field will contain some null values, no data, but I
do need to make sure that any data entered is not a duplicate. Below I
pasted from the forms newsgroup but havn't been able to get it to work..I
would appreciate any help..Thanks...Randy

Private Sub Cert_ID_BeforeUpdate(Cancel As Integer)
If IsNull(DLookup("Cert_ID", "Account", "[Cert_ID]=" _
& [Forms]![Accounts]![Cert_ID])) Then
MsgBox "This Certificate Number has Already Been Entered"
Cancel = True
End If
End Sub

Reply to this Message

Angi - 28 Apr 2005 08:28 GMT

Randy,
I'm not an expert, but I think your If statement should be something
like this:

If Not IsNull(DLookup("Cert_ID", "Account", "[Cert_ID]=" _
& [Forms]![Accounts]![Cert_ID])) Then
msgbox....

Right now, you're saying "if there isn't a record (isnull) that matches
the current (cert_id) then display the message." Also, you might want
to check if the cert_id value is being recognized. Put a stop at this
code and hold your mouse over the cert_id part...the value should be
the same as entered.

I hope this helps...or at least gets you on the right track.
Angi

Reply to this Message

Angi - 28 Apr 2005 08:52 GMT

Randy,
I just thought of something else. Another option is set the Indexed
property for that field to Yes (no duplicates) in the table. Then you
could display the message on error. The only downside to that is I
think you won't get the error until the form is trying to save. Just a
thought.

Angi

Reply to this Message