DLookup Problem

[matthewnsarah07]

I am currently have a basic LogOn form on my database, when the user selects their name from the username field and enter the password the code runs a basic select query to open their form

I would like to change this to a DLookup which will find which form to open based an entry in the tblEmployees

In this table Column 1 has the ID No. Column 2 has the username and Column 4 has the form name to open

I have tried this piece of coding but i get runtime 2471 saying doesn't contain automation object strempform

Any help?

Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.combo12) Or Me.combo12 = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.combo12.SetFocus
Exit Sub
End If
'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.combo12.Value) Then
lngMyEmpID = Me.combo12.Value
 
 
MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.combo12.Column(1))
DoCmd.OpenForm MyForm
 
 
 
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If
'If User Enters incorrect password 3 times database will shutdown
intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub

 

[Darth Vodka]

as an aside, you can do

If IsNull(Me.combo12) Or Me.combo12 = "" Then

as

If nz(Me.combo12,"")="" Then

try typing the dlookup in the immediate window, like

?DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=12")

it will almost certainly be a text/long error, dlookups are fiddly and don't give good error messages

perhaps try

DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & clng(Me.combo12.Value))

oh another point is that if a dlookup doesn't work, it returns null, so you may want to try

nz(DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & clng(Me.combo12.Value)),"")

 

[matthewnsarah07]

Thanks for your help - i've got it up and running now