Error on entering duplicate value

[anwaar]

Guys,

I am designing a "User Registration" form in which I have 5 textboxes and 1 command button:

Text Boxes:
---------------
UserFullName
Username


Password
Password2
UserType


Command Button:
-------------------
cmdRegister


Now the problem is, I want if any duplicate 'username' is going to be registered then VBA should display an error message like "Username already exists."

Kindly help me out.

[alansidman]

Why not just set the property in the table for the username field to No duplicates.

Alan

[anwaar]

Dear alansidman,

Really thanks for your instent reply, I know this way to do it but the problem is I want to do it through VBA, because by using Index property (No Duplicates), it returns a VBA error (Debug) due to this code line "DoCmd.GoToRecord , , acNewRec". That's why I want to do it complete through VBA.

So Please review it and Help me
(QdEvaluators is Query which retrieve only 'Usernames' from original table)

Private Sub cmdRegister_Click()
If DLookup("[Username]", "QdEvaluators") = Username Then
    MsgBox "Username already exists.", vbCritical, "Username"
    userFullName.Value = ""
    Username.Value = ""
    Password.Value = ""
    Password2.Value = ""
    UserType.Value = "User"
Else
    If Not Password.Value = Password2.Value Or Password.Value = "" Or Password2.Value = "" Then
        MsgBox "Password and Verify Password should be matched, and should not blank.", vbCritical, "Password"
        userFullName.Value = ""
        Username.Value = ""
        Password.Value = ""
        Password2.Value = ""
        UserType.Value = "User"
    Else
        If userFullName.Value = "" Or Username.Value = "" Then
            MsgBox "Name/Username fields should not blank.", vbInformation
        Else
            MsgBox "Congratulations, New user is successfully registered.", vbInformation
            DoCmd.GoToRecord , , acNewRec
            DoCmd.Close
        End If
    End If
End If
End Sub

[anwaar]

Thanks Guys

I have done it using Recordset and SQL Count function.