Duplicate username entry problem

[Mohammed Mazid]

Hi,

Basically I have a problem with registering to my quiz system. I had
borrowed some code from an existing program but I just do not know why
it doesn't work.

If (txtUsername = "" Or txtPassword = "") Or (txtFirstName = "" Or
txtLastName = "") Then
MsgBox "Please complete all the fields", vbCritical = vbOKOnly,
"Incomplete Login Details"
txtFirstName.SetFocus
Else

Set rs = rs.Open("SELECT * FROM Login"), ......
'add new account to login database
With rs
.AddNew
!UserName = Trim(txtUsername)
!Password = Trim(txtPassword)
!FirstName = Trim(txtFirstName)
!LastName = Trim(txtLastName)
On Error GoTo duplicate
.Update
MsgBox "Congratulations! You can use the system now.",
vbInformation, "Details entered"
duplicate:
'Err 3022 is an error code for duplicate ID
If Err = 3022 Then
MsgBox "Username already in use. Please enter a different
Username.", vbOKOnly, "Duplicate Username"
.CancelUpdate
txtUsername = ""
txtPassword = ""
txtFirstName = ""
txtLastName = ""
txtUsername.SetFocus
Exit Sub
End If
Resume Next
Unload Me
frmLogin.Show
End With
End If

Well it's not that it doesn't work, it bypasses the error message. If
I enter a duplicate username, it gives me a success message rather
than an error. However, it does run the .cancelupdate function. I am
catering for an error in the Err = 3022 if statement, which is an
error code for duplicate entry of a primary key.

Please someone tell me what am I doing wrong here and how to rectify
the problem, I'll be really appreciated.

[Raoul Watson]

"Mohammed Mazid" <ka******@hotmail.com> wrote in message
news:7c**************************@posting.google.c om...

Hi,

Basically I have a problem with registering to my quiz system. I had
borrowed some code from an existing program but I just do not know why
it doesn't work.

If (txtUsername = "" Or txtPassword = "") Or (txtFirstName = "" Or
txtLastName = "") Then
MsgBox "Please complete all the fields", vbCritical = vbOKOnly,
"Incomplete Login Details"
txtFirstName.SetFocus
Else

Set rs = rs.Open("SELECT * FROM Login"), ......
'add new account to login database
With rs
.AddNew
!UserName = Trim(txtUsername)
!Password = Trim(txtPassword)
!FirstName = Trim(txtFirstName)
!LastName = Trim(txtLastName)
On Error GoTo duplicate
.Update
MsgBox "Congratulations! You can use the system now.",
vbInformation, "Details entered"
duplicate:
'Err 3022 is an error code for duplicate ID
If Err = 3022 Then
MsgBox "Username already in use. Please enter a different
Username.", vbOKOnly, "Duplicate Username"
.CancelUpdate
txtUsername = ""
txtPassword = ""
txtFirstName = ""
txtLastName = ""
txtUsername.SetFocus
Exit Sub
End If
Resume Next
Unload Me
frmLogin.Show
End With
End If

Well it's not that it doesn't work, it bypasses the error message. If
I enter a duplicate username, it gives me a success message rather
than an error. However, it does run the .cancelupdate function. I am
catering for an error in the Err = 3022 if statement, which is an
error code for duplicate entry of a primary key.

Please someone tell me what am I doing wrong here and how to rectify
the problem, I'll be really appreciated.

Just a wild guess. When you create the database are you disallowing null
fields? If so, it is possible when the add statement is executed that you
are getting a different error (3315 = zero length fields) or even 3421 =
datatype mismatch (i.e. alpha in num field) if you incorrectly specify the
field.

Best thing to do is put a break point and trace it so you can know EXACTLY
the error code that was raised.

[MT Head]

It looks to me like you have two separate problems:

One is that the error 3022 is not being raised, therefore your On Error
never trips. That I can't help you with.

Your other problem is with program flow. On Error you would jump to your
"duplicate:" label, which is proper... but the way it's written, you go
there even if there's no error! Try changing this:

On Error GoTo duplicate
.Update
MsgBox "Congratulations! You can use the system now.",
vbInformation, "Details entered"
duplicate:
'Err 3022 is an error code for duplicate ID
If Err = 3022 Then
MsgBox "Username already in use. Please enter a different
Username.", vbOKOnly, "Duplicate Username"
.CancelUpdate
txtUsername = ""
txtPassword = ""
txtFirstName = ""
txtLastName = ""
txtUsername.SetFocus
Exit Sub
End If
Resume Next
Unload Me
frmLogin.Show
End With
End If
to this:
On Error GoTo duplicate
.Update
MsgBox "Congratulations! You can use the system now.",
vbInformation, "Details entered"
End If
Resume Next
Unload Me
frmLogin.Show
End With
End If

duplicate:
'Err 3022 is an error code for duplicate ID
If Err = 3022 Then
MsgBox "Username already in use. Please enter a different
Username.", vbOKOnly, "Duplicate Username"
.CancelUpdate
txtUsername = ""
txtPassword = ""
txtFirstName = ""
txtLastName = ""
txtUsername.SetFocus
Exit Sub

etc.

[MT Head]

One last thing: indentation is your friend! Use it wisely, and your
program logic becomes MUCH clearer!