Username and passwod login using Form

[Tom1]

Hello,


We have found a code from "DoubleListBoxExample" databese, to input into VBA for username and password to be entered into a form before they can procede and update certain parts of the database. I have copied the code, replacing the table names ect with the relevant ones from my table. But it is coming up with an error message. Any help would be greatly appreciated.





The code is attached below.
I am not sure if it is to do with 'set up the connection and 'set up the record set not being correct. I also don't know about the myRSUsers, I have managed to tie everyting back to tables except for that.

Private Sub cmdLogin_Click()
'Check to make sure something is in the username control
If Nz(Me.Combo6, "") = "" Then
MsgBox "You must specify your username"
Me.Combo6.SetFocus
Exit Sub
End If
'Check to make sure something is in the password control
If Nz(Me.Text10, "") = "" Then
MsgBox "you must specify your password"
Me.Text10.SetFocus
Exit Sub
End If
'set up the connection
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

'set up the recordset
Dim myRSUsers As New ADODB.Recordset
myRSUsers.ActiveConnection = cnn1
Dim mySQL As String

mySQL = "SELECT TblUsers.ID, TblUsers.FirstName & '.' & TlbUsers.LastName AS Username, Tblusers.Password"
mySQL = mySQL & "FROM TblUsers"
mySQL -mySQL & " WHERE TblUsers.FirstName & '.' TblLastName='" & Me.Combo6 & "'"
Debug.Print mySQL
myRSUsers.Open mySQL, , adOpenDynamic, adLockOptimistic
'If no user is found that matches what was typed in the username field, return Invalid username message
If myRSUsers.BOF And myRSUsers.EOF Then
MsgBox "Invalid username; try again"
Me.Combo6 = Numm
Me.Combo6.SetFocus
myRSUsers.Close
Set myRSUsers = Nothing
Exit Sub
Else
'a valid username is found now check the password; if the password is invalid return a message
If myRSUsers!Password <> Me.Text10 Then
MsgBox "Invalid Password, try again"
Me.Text10 = Numm
Me.Text10.SetFocus
myRSUsers.Close
Set myRSUsers = Nothing
Exit Sub
Else
'Password is valid, assign the ID of the user to the control on the form and then open the main menu form and gide the login form
Me.empID.ID = myRSUsers!ID
DoCmd.OpenForm "FrmRequestWork", acNormal
myRSUsers.Close
Set myRSUsers = Nothing
Me.Visible = False
End If
End If

End Sub

[John_G]

What is the setting for the On Click property of the Command Button? It should be [Event procedure]. The error message suggests that it is not.

[Tom1]

I've just checked the on click property and it does state [Event procedure]

[John_G]

Check to see if the procedure is actually being executed. Put a msgbox command as the first line in the code, something like msgbox "Login procedure" is all you need. If when you click the login button you do not see the message box, then something else is wrong, because the code is never reached.

PS - do you always compile your code after making changes? If not, you should.

John

[John_G]

I just noticed - there is a typo in this line:

mySQL -mySQL & " WHERE TblUsers.FirstName & '.' TblLastName='" & Me.Combo6 & "'"
should be
mySQL = mySQL & " WHERE TblUsers.FirstName & '.' TblLastName='" & Me.Combo6 & "'"

Compiling the code would probably have caught that.