DLookup - Login form not working 3

[lmmoorewi]

Hi there -

Please help, yes I am begging.

I am using Access 2007 and this is my first db. I created a login form for Analyst to enter Access to enter the work hours and tasks.

The table (Analysts) consists of:
Username, Password and Privilege

Now, when a user logs in using their username and password, I want the code to find the 'Privilege' assigned to the Username and opened a particular form.

Privilege A - I want the Form 'Privilege A Form' to open. For Privilege B - I want the code to find and open 'Privilege B Form. I want this to happen for 5 different Privileges I have assigned to different Usernames in the Analyst table.

This is where i start and end:
If Me.Username = DLookup("Username", "Analysts", "Privilege=A") Then
DoCmd.OpenForm "Privilege A Form"
Else
Thanks!!!

 

[dhookom]

Try something like...

Dim strPriv as String
Dim strFormName as String
strPriv = DLookup("Privilege","Analysts","UserName=""" & _
    Me.UserName & """")
strFormName = "Privilege " & strPriv & " Form"
DoCmd.OpenForm strFormName

Duane
Hook'D on Access
MS Access MVP

 

[lmmoorewi]

Hi!

Thanks for the response.

Ok, so I ran the code and received the following error:

Run-Time error '2102':

the form name 'Privilege FSS Technician Login Dialog' is misspelled or refers to a form that doesn't exist.

'FSS Technician' is the privilege listed in the Analyst table and 'Login Dialog' is the name of the Login form.

Here is the code I entered:

StrPriv = DLookup("Privilege", "Analysts", "Username=""" & Me.USername & """")
StrWorksheets = "Privilege " & StrPriv & " Login Dialog"
DoCmd.OpenForm StrWorksheets

 

[TheAceMan1]

How are ya lmmoorewi . . .

Perhaps the following. Note: in [blue]Dim Priv[/blue] Prev is a variant ... no type assigned. This is so [blue]null[/blue] can be handled if name and password are not found:

[blue]   Dim Priv, WS As String, Cri As String
   
   Cri = "[UserName] = '" & Me.UserName & "' AND " & _
         "[Password] = '" & Me.Password & "'"
   Priv = DLookup("Privilege", "Analysts", Cri)
   
   If Not IsNull(Priv) Then
      WS = Switch(Priv = "A", "Privilege A", _
                  Priv = "B", "Privilege B", _
                  Priv = "C", "Privilege C", _
                  Priv = "D", "Privilege D")
      DoCmd.OpenForm WS
   Else
      MsgBox "Inproper UserName or Password!", _
             vbCritical + vbOKOnly, _
             "Access Denied!"
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[randy700]

How about...

Dim strPriv As String
strPriv = DLookup("Privilege", "Analysts", "Username = '" & Me.txtUsername & "'")
Select Case strPriv
    Case "A":
        DoCmd.OpenForm "Privilege A Form"
    Case "B":
        DoCmd.OpenForm "Privilege B Form"
    Case "C":
        DoCmd.OpenForm "Privilege C Form"
    Case "D":
        DoCmd.OpenForm "Privilege D Form"
    Case "E":
        DoCmd.OpenForm "Privilege E Form"
    Case Else:
        MsgBox "Your message here"
End Select

Randy

 

[TheAceMan1]

[blue]Now, when a user logs in using their username and [purple]password[/purple] ...[/blue]

Shouldn't we check for password as well? ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lmmoorewi]

Hey TheAceMan1 -

Yes, we should check for both username and password.

I am stuck in meetings for the rest of the day (who has meetings on New Year's Eve???!!!). I will try some of the suggestions later today and let you all know what happens!

Thanks for the replies!!!

 

[lmmoorewi]

Hi -

I read from the thread from the bottom up so I used Randy700's code. It worked perfectly!!!!

Thanks so much for all replies!!

 

[TheAceMan1]

lmmoorewi . . .

Note that [blue]randy700's[/blue] code only pings on the [purple]UserName[/purple]. Instead of [purple]UserName[/purple] and [purple]Password[/purple]!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lmmoorewi]

Hi TheAceMan1 -

Ohhhh....hmmmm. How can I have it ping on both UserName and Password?

 

[dhookom]

Try something like:

Dim strPriv As Stringstr
Priv = DLookup("Privilege", "Analysts", _
    "Username = '" & Me.txtUsername & _
    "' AND Password =""" & me.txtPassWord & """ ")
Select Case strPriv
    Case "A"
        DoCmd.OpenForm "Privilege A Form"
    Case "B"
        DoCmd.OpenForm "Privilege B Form"
    Case "C"
        DoCmd.OpenForm "Privilege C Form"
    Case "D"
        DoCmd.OpenForm "Privilege D Form"
    Case "E"
        DoCmd.OpenForm "Privilege E Form"
    Case Else
        MsgBox "Your message here"
End Select

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, to avoid an error message about Null, I'd use this instead:

strPriv = Nz(DLookup("Privilege", "Analysts", _
    "Username='" & Me!txtUsername & _
    "' AND Password='" & Me!txtPassWord & "'"), "?")

 

[lmmoorewi]

Hi -

Great! I didn't receive any errors.

Thanks!!!

 

[PHV]

I didn't receive any errors
Even when the password was wrong ?

 

[lmmoorewi]

Hi -

well....when the password is wrong,I have an error message to display - "Password Invalid. Please Try again".

Is that the correct thing to do?

 

[PHV]

What is YOUR actual code ?

 

[lmmoorewi]

Ok....don't laugh. This is my first database so be gentle...

Private Sub Login_Click()
Dim StrPriv As String

'Check to see if data is entered into the UserName combo box

If IsNull(Me.USername) Or Me.USername = "" Then
MsgBox "You must select a User Name.", vbOKOnly, "Required Data"
Me.USername.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If

'Check value of password

If Me.Password.Value = DLookup("Password", "Analysts", "[Username]=" & Chr(34) & Me.USername.Value & Chr(34)) Then

VUsername = Me.USername.Value
Me.Visible = False
StrPriv = Nz(DLookup("Privilege", "Analysts", "Username = '" & Me!USername & "' And Password ='" & Me!Password & "'"), "?")
Select Case StrPriv
Case "FSS Technician":
DoCmd.OpenForm "Worksheets"
Case "FSS Manager":
DoCmd.OpenForm "FSS Managers"
Case "FSS TechnicianUS":
DoCmd.OpenForm "WorksheetsUS"
Case "FSS TechnicianTP":
DoCmd.OpenForm "WorksheetsTP"
End Select

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Password.SetFocus

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 If
End Sub

 

[lmmoorewi]

Private Sub Login_Click()
Dim StrPriv As String

'Check to see if data is entered into the UserName combo box

If IsNull(Me.USername) Or Me.USername = "" Then
MsgBox "You must select a User Name.", vbOKOnly, "Required Data"
Me.USername.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If

'Check value of password

If Me.Password.Value = DLookup("Password", "Analysts", "[Username]=" & Chr(34) & Me.USername.Value & Chr(34)) Then

VUsername = Me.USername.Value
Me.Visible = False
StrPriv = Nz(DLookup("Privilege", "Analysts", "Username = '" & Me!USername & "' And Password ='" & Me!Password & "'"), "?")
Select Case StrPriv
Case "FSS Technician":
DoCmd.OpenForm "Worksheets"
Case "FSS Manager":
DoCmd.OpenForm "FSS Managers"
Case "FSS TechnicianUS":
DoCmd.OpenForm "WorksheetsUS"
Case "FSS TechnicianTP":
DoCmd.OpenForm "WorksheetsTP"
End Select

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.Password.SetFocus

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 If
End Sub

 

[lmmoorewi]

Sorry..I sent the code twice.