Message Box for Duplicates

[Fox359]

I'm a novice and am having trouble getting a message box to work. I would like a message box to appear when someone is entering or updating a number in a control in a subform when there is a duplicate. The message box can then allow the person to continue (keep the duplicate if it's intentional) or delete the entire record (if it wasn't intentional).

I have the following coding for AfterInsert and AfterUpdate for the control in the subform:

If DCount("*", " Database-Number ", "[Number]="& Chr(34) & [Number] & Chr(34)) > 1 Then

Call MsgBoxDuplicate

End If

I have entered the following for the message box in the subform, but nothing happens:

Sub MsgBoxDuplicate()

Dim answer As Integer
answer = MsgBox("This number has already been entered. Would you like to continue? If this is an intentional duplicate, press Yes continue entering information. Otherwise press No to delete this entry.", vbYesNo + vbExclamation, "Duplicate Entry")

If answer = vbNo Then

answer = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbCritical, "Delete")

If answer = vbYes Then

DoCmd.RunCommandacCmdDeleteRecord

End If

End If

I tried starting with the basic coding and then adding one section at a time to figure out where the problem is. The message box appears for duplicates if I do this:

Sub MsgBoxDuplicate()

MsgBox"This number has already been entered. Would you like to continue? If this is an intentional duplicate, press Yes continue entering information. Otherwise press No to delete this entry.", vbYesNo + vbExclamation, "Duplicate Entry"

but as soon as I add "answer=(…)" it no longer appears. Any help would be much appreciated.

[davegri]

First, get rid of the spaces:
If DCount("*", " Database-Number ", "[Number]="& Chr(34) & [Number] & Chr(34)) > 1 Then

It that doesn't work, get rid of the delimiters around Number (If Number is a number)

If DCount("*", "Database-Number", "[Number]=" & Number) > 1 Then

[Fox359]

Sorry, I realize how misleading that is. While it's referred to as a "number", the original data that was imported had a dash in the middle so the field is short text.

[davegri]

Sorry, I realize how misleading that is. While it's referred to as a "number", the original data that was imported had a dash in the middle so the field is short text.

EDIT:

The first argument is the name of the field you are looking for ("*") which is anything
The second argument is the domain (the table or query) containing the first argument (is that Database-Number or Number?).

If DCount("*", "Database-Number", "Number=" & Chr(34) & [Number] & Chr(34)) > 1 Then

Which means "Count any record in Database-Number where Number = this number"

Notice removal of spaces in red Database-Number and removal of brackets in Number.