Problem creating a TempVars

[Ant_Snell]

I am trying to create a TempVars that holds the “Permission” Variable for use in code when opening forms etc. After watching numerous YouTube videos I managed to create a log in screen and save the rs variable as TempVars “CurrentUserID” that all works fine so thought it would be fairly simple task to copy the code that works parrot fashion, change a few names to create a TempVars “UserPermission” but I just cant get it to work. I confess I have very little understanding of code and have tried several things with no success.

Any help appreciated.

Private Sub btnLogIn_Click()


    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("tbl_Emp_Details", dbOpenSnapshot, dbReadOnly)
    rs.FindFirst "User_Name='" & Me.txtUserName & "'"
    
    Dim ap As Recordset
    Set ap = CurrentDb.OpenRecordset("tbl_Emp_Details", dbOpenSnapshot, dbReadOnly)
    ap.FindFirst "Emp_ID='" & Me.txtUserID & "'"
        
    If rs.NoMatch = True Then
    Me.labWrongUser.Visible = True
    Me.txtUserName.SetFocus
    Exit Sub
    
  End If
    Me.labWrongUser.Visible = False
  
    If rs!User_Password <> Nz(Me.txtPassword, "") Then
    Me.LabWrongPW.Visible = True
    Me.txtPassword.SetFocus
    Exit Sub
  End If
  
    TempVars.Add "CurrentUserID", Me.txtUserID.Value
    'MsgBox TempVars![currentUserID]
    TempVars.Add "UserPermission", Me.txtPermission.Value
    'MsgBox TempVars![UserPermission]
    
    
    Me.LabWrongPW.Visible = False
    DoCmd.OpenForm "frm_Index_Main"
    DoCmd.Close acForm, "frm_Login"
End Sub

.

[moke123]

Is the Log In form bound to the table and txtPermission bound to the field?

[orange]

@Ant,

What exactly isn't working in the code you have posted? Error message.

[Welshgasman]

You need to start indenting your code properly?
Pretty much required to assist with finding errors. Google Smart Indenter, to indent what you have already.

Why two recordsets for the same table?
An ID to me is always numeric?, so no need for the single quotes?

[Ant_Snell]

It's stopping at the yellow line. ap.FindFirst "Emp_ID='" & Me.txtUserID & "'"

The form is not bound to any table.

Thanks

[Welshgasman]

It's stopping at the yellow line. ap.FindFirst "Emp_ID='" & Me.txtUserID & "'"

The form is not bound to any table.

Thanks

So nothing to do with Tempvars then?

[Ant_Snell]

The error message is " Data mismatch in criteria expression"

[Welshgasman]

The error message is " Data mismatch in criteria expression"

I've told you the likely cause of that, even without the error message?

[Ant_Snell]

So nothing to do with Tempvars then? Not sure, I am trying to declare the variable ap then save it a a TempVars later on at "TempVars.Add "UserPermission", Me.txtPermission.Value" but as I say not expert at VBA and just copying parrot fashion

[Welshgasman]

So nothing to do with Tempvars then? Not sure, I am trying to declare the variable ap then save it a a TempVars later on at "TempVars.Add "UserPermission", Me.txtPermission.Value" but as I say not expert at VBA and just copying parrot fashion

That will not work.
You need to take the time to understand the code, to some degree at least? Otherwise it is only going to slow you down even more and will frustrate you more and more.
Numbers do not need to be surrounded by anything when concatenating
Text needs a single quote, as you had for the name, or triple " if the name could contain a ' like O'Leary ? So your code is going to fail there as well if that occurs.
Dates need # and be in mm/dd/yyyy or yyyy-mm-dd format

[orange]

Emp_ID and Permission are both Numeric (right justified) in your table.

Try removing the single quotes as welshgasman suggested.

 ap.FindFirst "Emp_ID='" & Me.txtUserID & "'"

[Ant_Snell]

@ Welshgasman Agreed! I signed up to a LinkedIn VBA course but did not find it very intuitive. I now tend to YouTube bits as and when needed but but that does lead to doing copying code without understanding the syntax. I did understand that "Data mismatch in criteria expression" was referring to numbers V text etc. so assumed that "Dim As Recordset" (used in the first should probably be "Dim As Integer" but then got stuck on the next line. I will push on to see if I can sort Thanks

[orange]

@ant,
There are some great vba and database tutorials on youtube.
See the Database Planning and Design link in my signature.
For Access and vba the tutorials series by Steve Bishop and Ronald Rost are very good.

[Ant_Snell]

@ orange Thanks I will look at these authors ( I do think I have seen a few of S Bishop's)

I have now removed the signal quotations as suggested and the code as moved on to

[code]
empVars.Add "UserPermission", Me.txtPermission.Value
MsgBox TempVars![UserPermission]
[\code]

It now errors with "invalid use null" at the message box line

[Ant_Snell]

TempVars.Add "UserPermission", Me.txtPermission.Value
MsgBox TempVars![UserPermission]

That was