login form

[janeyg]

Hi
I am really struggling here. I am having trouble with a line of this code and have search dozens of post and variations on this for an answer but can't seem to find a response which solves it.

I used this guide to make the table and password form http://www.databasedev.co.uk/login.html
Then used the forum to search and modify it so that when you login in, a specific form will load depending on which user you are - a choice of two forms.

I have a table named tbleEmployees with fields IngEmpID strEmpName strEmpPassword strEmpForm

When I run the below code, i get an error 2471 with this part of the code highlighting this field [lngEmpID]

this field is highlighted:-
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then


This is the full code in the login button. I have incorporated a lookup for which form to load:-

Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.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.cboEmployee.Value) Then
lngMyEmpID = Me.cboEmployee.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmLogon", acSaveNo
MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
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 admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub


Can anyone advise what is wrong?
thanks, any help is much appreciated.

 

[nanscombe]

I take it that it's nothing silly like the field in the table being call "IngEmpId" (capital I) instead of "lngEmpId" (lower case L)?

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then

I see you are using a comboBox. Are you using two columns such as ID (number) and Name (string)? If you are you probably need to use ".Column(0)" rather than ".Value".

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Column(0)) Then

ETA:

Ah, I see you are.

SELECT [tblEmployees].[lngEmpID], [tblEmployees].[strEmpName]
FROM tblEmployees;

Try using ".Column(0)" rather than ".Value".

 

[nanscombe]

Nope.

As you were. ".Value" and ".Column(0)" do produce the same value.

I have a table named tbleEmployees with fields IngEmpID strEmpName strEmpPassword strEmpForm

...

this field is highlighted:-
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then

Whoops. IngEmpID (I N G) instead of lngEmpID (L N G), in the table, does give error '2471'.

 

[janeyg]

Many thanks, that works a treat. Only thing is I thought this part of the code would close the login window when the other form/s open? But is doesnt. It stays open and the other form/s open behind it. I am not sure what else I need to add to allow this to happen. I have various codes to hide though I am not sure where to add it. I think also I need to add a bit of code to both opening forms?

DoCmd.Close acForm, "frmLogon", acSaveNo

Would you have any ideas on this? I am trying to get to grips with code but its tough going.

any help is appreciated.

 

[nanscombe]

[COLOR="Red"]DoCmd.Close acForm, "frmLogon", acSaveNo[/COLOR]
MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
DoCmd.OpenForm MyForm

But you are still executing code, I would imagine that it should be the last thing you do.

MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
DoCmd.OpenForm MyForm
[COLOR="red"]DoCmd.Close acForm, "frmLogon", acSaveNo[/COLOR]

Or you could just hide it?

' In frmLogin
Me.Visible = FALSE

When you close the other form you could add a bit of code to unhide it.

Private Sub Form_UnLoad()
' In other form(s)
Forms("frmLogin").Visible = TRUE
End Sub

 

[janeyg]

I tried moving the code to the bottom but it did not like that.


MyForm = DLookup("strEmpForm", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
DoCmd.OpenForm MyForm
DoCmd.Close acForm, "frmLogon", acSaveNo



I added the unload to the other forms as your suggestion as I think this idea may be good but not sure where to place this bit of code in the frmLogin form, to get it to work?


Me.Visible = FALSE

 

[nanscombe]

Me.Visible = FALSE would be in place of DoCmd.Close acForm, "frmLogon", acSaveNo in frmLogin

 

[janeyg]

Thats great, works perfectly! Thanks so much for your help