Duplicate Entry Warning Message

[Fezichimo]

I'm trying to get a duplicate entry into a textbox flagged as a before_update warning message. The field is "Hospital_Numbe r" within the table "Voc Rehab DB". The Hospital Number is a text field since hospital numbers can include things like: U/G39401

At the moment I have the following code:

Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)

    If Nz(DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number.Text & "'"), 0) > 0 Then
        If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If
End Sub

Please help!

[Seth Schrock]

You need to tell us what is happening. Off the top of my head, nothing looks wrong with your code. Are you getting an error message or is it not doing what you want to happen?

[Fezichimo]

Sorry, yes, the code causes it to come up with an error message whatever is entered into the textbox. Even if there isn't an existing duplicate for that particular hospital number.

[Seth Schrock]

What is the exact error message and number? Also, what line gets highlighted if you click the Debug button on the error?

[Fezichimo]

Once again, sorry, I need to be clearer!

Every time a number is entered it delivers the MsgBox containing "The value you are adding already exists, do you want to continue?".

This pops up even when you are entering a completely new hospital number and not a duplicate.

[Seth Schrock]

Try removing the Nz() function from around your DCount() function. I don't think that this would break it, but DCount() never returns null, so it isn't needed either.

Also, remove the .Text at the end of your control reference.

[Fezichimo]

I removed the .Text as suggested as well as trying removing the Nz() function. However, removing the latter bracket led to an error code stating it expected a "Then or a Go To Statement". Removing the earlier end bracket was allowed but then returned the error: "Compile Error: Wrong number of arguments or Invalid Property Assignment".

This is now the code:

Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
 
    If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'", 0) > 0 Then
        If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If
End Sub

[Seth Schrock]

You didn't remove the , 0 that is part of the Nz() function. So your first IF statement line should be

If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'") > 0 Then

[Fezichimo]

Thank you Seth, that prevented the error message but the same problem still applies in that every time I enter ANYTHING in for hospital_number (text field) it generates the message box that is only supposed to warn you if a duplicate already exists. Any ideas? The code as it stands is as follows:

Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)

   If DCount("Hospital_Number", "Voc Rehab DB", "Hospital_Number = '" & Me.Hospital_Number & "'") > 0 Then
        If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If

End Sub

[Seth Schrock]

Try the following code just to troubleshoot what is going on:

Private Sub Hospital_Number_BeforeUpdate(Cancel As Integer)
    Dim strCriteria As String

    strCriteria = "Hospital_Number = '" & Me.Hospital_Number & "'"
    Debug.Print strCriteria
    If DCount("*", "Voc Rehab DB", strCriteria) > 0 Then
        If MsgBox("The value you are adding already exists do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
            Cancel = True
            Me.Undo
            Exit Sub
        End If
    End If
 
End Sub

This will output the criteria to the Immediate window (press Ctrl + G to open it if it isn't already). Please post back what it outputs.

[Fezichimo]

Have done as you suggested and the same problem still arises. In the Immediate Window it just comes up with whatever I had entered. For example:

Hospital_Number = 'RTRTRTRTRT'

It even still generates the MsgBox when I input punctuation which is never normally included in any patient's hospital_number . I wanted to make sure it wasn't because every individual digit/letter had appeared in a hospital number at some point.

[Seth Schrock]

Type the following into the immediate window and tell me what returns:

?DCount("*", "Voc Rehab DB", "Hospital_Number = 'RTRTRTRTRT'")