Duplicate Number Warning (2000)

[RShoe5]

I have a form that enters new customer information into a table. One of the fields is the customerID, which is a "No Duplicates" field. Currently, when the user fills out the form and they use a

[ready4data]

I use this code in before update to warn about duplicate serial numbers:

Private Sub SERIAL_BeforeUpdate(Cancel As Integer)
Const qt = """" 'four quotes
If DCount("[SERIAL]", "EQUIPMENT SCHEDULE", "[SERIAL] = " & qt & Me.SERIAL & qt) > 0 Then
MsgBox ("This is a Duplicate Serial Number")
Exit Sub
End If
End Sub

[HansV]

I don't think Exit Sub does anything here, I would use Cancel = True instead, but I'm not sure this will help.

[RShoe5]

Forgive me but is "EQUIPMENT SCHEDULE" the table the Serial number is coming from or is that something else?

[Trabuda]

Have you set the index property in the table design for the index to be yes with no duplicates? This will bring up a duplicate error message.

[RShoe5]

Yes I did, but it doesn't bring up any error messages at all. It just closes to the form (because the button coded to do that) and all the information is gone. It doesn't overwrite the other information that corresponds to that ID. If it isn't a dup. ID, it enters the information in the table without a problem.

[RShoe5]

Using this code:

Private Sub iMISNumber_BeforeUpdate(Cancel As Integer)

Const qt = """" 'four quotes
If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & qt & Me.iMISNumber & qt) > 0 Then

MsgBox ("This is a Duplicate Serial Number")
Exit Sub

End If

End Sub

I get this error

Run-Time Error 3464
Data type mismatch in criteria expression

[SteveH]

Taken from Access help searching under 'Error 3022' (no I can't remember all of the error codes - this one happens to be fresh in my mind!)


The following example shows how you can replace a default error message with a custom error message. When Microsoft Access returns an error message indicating it has found a duplicate key (error code 3022), this event procedure displays a message that gives more application-specific information to users.

To try the example, add the following event procedure to a form that is based on a table with a unique employee ID number as the key for each record.


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

If DataErr = conDuplicateKey Then
Response = acDataErrContinue
strMsg = "Each employee record must have a unique " _
& "employee ID number. Please recheck your data."
MsgBox strMsg
End If
End Sub
</pre>


Might this be of some use? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

[HansV]

Steve has pointed you at the Form_Error event. That is probably the best one to use here.

About the data type mismatch: the field name iMISNumber makes me think it is numeric. In that case, you shouldn't use the quotes qt - that is for text fields.

[ready4data]

Yes Equipment schedule is the table and serial number is the field.
Our serial numbers are alphanumeric so the serial field is a text field

[RShoe5]

Should the whole line :
Const qt = """"
Be removed or changed to sometime else?

[HansV]

If you do have a numeric field, you can remove that line and replace

If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & qt & Me.iMISNumber & qt) > 0 Then

by

If DCount("[IMISNumber]", "tblAttendees", "[IMISNumber] = " & Me.iMISNumber) > 0 Then

[Support4John]

Hi ready4data

What is the advantage of using dcount vs dlookup?

John

[RShoe5]

Thanks Guys!!!! Using Ready4data's code and Han's edit, it works great.

[charlotte]

|I'll jump in here and give you an answer, although it may not be *the* definitive answer. Dlookup use the indexes, if any, to find the values in the domain. As soon as it finds a match, it returns a value. DCount executes the equivalent of a single efficient Count(*) on the domain without needing to step through records looking for anything. I would expect DCount to be faster.