I need some help in how to reference a field name in my tblemployee for Login.

Hi everyone,

I have an Access 2007 database where I have setup a Lonin form called CreateLogin.  The information that is entered into the Create Logon form goes to a table called tblEmployees.  I have the following fields in the tblEmployees:

lngEmpID          AutoNumber
strEmpName     Text
strEmpPassword     Text
StrAccess          Text

I have a form called "CreateLogin" that lets the user put in their strEmpName, strEmpPassword, and strAccess.  The field strAccess will always be admin or user.

I need an If statement to check the table and see if the strAccess is Admin and if so open a form called StartupScreen, if not open a form called "StartUpScreen1.

I have this coding but, I do not know how to make Access know what strAccess is.  When I reference strAccess in an If Statement.  

My coding is this:


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

        lngMyEmpID = Me.cboEmployee.Value
       
        End If

'Close logon form and open splash screen
              'If strAccess = "admin" Then
  DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "StartupScreen"

     Else
        DoCmd.OpenForm "StartupScreen1"
 
End If


How can I fix this.  When I Dim strAccess as String, access stsill does not know what strAcess is


Do you have some sample coding in VBA that will do what I want to do here.

jjc9809
jjc9809Asked:
Who is Participating?
 
mbizupCommented:
Try this:

Dim strAccess as string
If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value
        strAccess = "" & DLookup("strAccess", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) 
       
        End If

'Close logon form and open splash screen
              If strAccess = "admin" Then
  DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "StartupScreen"

     Else
        DoCmd.OpenForm "StartupScreen1"
  
End If

Open in new window

0
 
Dale FyeCommented:
How about something like:

Dim varAccess as variant
Dim strCriteria as string

strCriteria = "[lngEmpID] = " & me.cboEmployee.Value & " AND [strEmpPassword] = " & chr$(34) & me.txtPassword.Value

varAccess = DLookup("strAccess", "tblEmployees", strCriteria)
If isnull(varAccess) then
    msgbox "Invalid EmployeeID/Password combination"
    Exit Sub
Elseif varAccess = "admin" Then
    DoCmd.OpenForm "StartupScreen"
Else
    DoCmd.OpenForm "StartupScreen1"
End If

0
 
Dale FyeCommented:
Oops, that should be:

strCriteria = "[lngEmpID] = " & me.cboEmployee.Value & " AND [strEmpPassword] = " & chr$(34) & me.txtPassword.Value & chr$(34)

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.