Log In Trouble

[Nwtech75]

Greetings All,
My thanks to everyone who answered my last 2 posts. Your suggestions are helpful.
I wonder if I might ask further assistance yet again. Please remember that I am still learning, so detailed explanations really help.

I have created a Log In Form that will open when the dbase opens.

LogIn Form.JPG

As you can see its fairly straight forward. The fields in the header and the Password test field will either be later deleted or made invisible. Once the user has logged in the form itself will continue to run invisible in the background. This is so that I can obtain information from it in other forms as the user manipulates the various forms within the system.

Let me share my code first......then I will get to my questions.

Private Sub txt_EMPID_LostFocus()


'Declair the Dbase
Dim CMAP As DAO.Database
Set CMAP = CurrentDb


Dim LOGIN As DAO.Recordset
Set LOGIN = CMAP.OpenRecordset("tbl_Personnel", dbOpenSnapshot) 'Possible update with ,dbReadOnly


Dim USER As String 'Need qry for this
USER = Me.txt_EMPID
Dim First As String
First = LOGIN!F_NAME
Dim Last As String
Last = LOGIN!L_NAME
Dim Level As String
Level = LOGIN!SEC




Me.txt_count = DCount("[ID]", "tbl_Personnel", "EMPID =" & "'" & USER & "'") 'Need If statements to cover pre-existing numbers (>1) & non-existing numbers (<1)
'Add buttons to try again, send an email exct.
If Me.txt_count < 1 Then
    If MsgBox("Invalid Log In! Please contact the Administrator for Cridentials!", vbOKOnly) = vbOK Then
        Canel = True
        Me.txt_EMPID = Null
        Me.txt_EMPID.SetFocus
    End If
Else
    Me.txt_F_NAME = [qry_LogIn]![F_NAME]
    Me.txt_L_NAME = [qry_LogIn]![L_NAME]
    Me.txt_SEC = [qry_LogIn]![SEC]
    Me.txt_check = [qry_LogIn]![PSWD]
    
End If


End Sub

I have also created a query for the Log-In. I would like to note here that it is my intent to ensure that the qry itself never opens, I just want to get the data from it.

SELECT tbl_Personnel.F_NAME, tbl_Personnel.L_NAME, tbl_Personnel.PSWD, tbl_Personnel.SEC, tbl_Personnel.EMPID
FROM tbl_Personnel
WHERE (((tbl_Personnel.EMPID)=[Forms]![frm_LogIn]![txt_EMPID]));

So now with my code, and an image of my form, here then is the difficulty that I am running into.

The goal is to produce a first name, last name, Security Level & a Password for comparison in their respective fields after the user tabs away from the log in field. As you can see in the If statement, I am attempting to derive that information from the qry that I have written. But when I tab away from the LogIn Field, I get an error "Run Time Error '2465'. Microsoft Access can't find the filed '|1' referred to in your expression". Right now I am of the opinion that this is because I have forgotten something that is causing the qry not to fire and thus produce the results. Again, I don't want the qry to open, just give me the results to populate into the fields. I think I'm close but have missed something in the code. Any suggestions?

Many thanks in advance to all.

[HiTechCoach]

Did you get this working?

1) It is better to use the control after update event. This way it only fires the code when the user enters something. The Lost Focus event fires if the user did not enter anything. Clicking in the control then in another would fire the Lost Focus event code. Same with tabbing between controls without any data entry.


2) You can't directly refernce a query. Yu must use code or VBA functions to process the query. You might try DLookup()