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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
Oops, that should be:

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

0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.