DLookup in Login Form 1

[mauricionava]

Hi, I have a login form that uses the following codes in the Login button:

If Me.quserid.Value = "" Then
    MsgBox "Please enter a username to continue."
    Exit Sub
    
Else
If Me.qpwd.Value = "" Then
    MsgBox "Please enter a password to continue."
    Exit Sub
    
    End If
End If

If Me.qpwd.Value = DLookup("PWD", "tblLogIn", "USERID = '" & Me.quserid.Value & "'") Then
DoCmd.OpenForm "frmMain22", acNormal

Else

MsgBox "Invalid Password. Please try again."
End If

and a table with two fields which are "userid" and "PWD". I would like to insert another field in this table to specify who in this list has access to some fields. For example a colunm called AUTH and having a YES or NO of whoever is authorized to use certian field.

How could I have that command in the Login button lookup and enable the fields if the user entered is YES authorized?

Thanks!

 

[Remou]

It may be easiest to add code to each form to set a suitable property (enabled, locked, visible). You could use a global variable, keep the login form open and hidden or store the log-in level in a table, amongst other things.

A table with, say:

FormName
FieldName
AuthLevel

Might be useful.

 

[fneily]

In VBA, click Insert - Module and set up a global variable such as:
Option Compare Database
Global swtch As Integer

Then in your logon code, test for yes or no and if yes set swtch to 1, if no set swtch to 0.

Then in the OnOpen event of your forms, test for the swtch. If swtch = 1, then controls.visible = true, etc. and if swtch = 0 then controls.visible = false etc.

 

[mauricionava]

fneily, I set up the global variable already.

I do not get the logon section that much.

I understand with no problem the coding for the forms but not for the logon section.

 

[fneily]

If I understand you, you don't know how to test for the Auth? Just add some coding like below:

If Me.quserid.Value = "" Then
MsgBox "Please enter a username to continue."
Exit Sub
Else
If Me.qpwd.Value = "" Then
MsgBox "Please enter a password to continue."
Exit Sub
End If
End If

If Me.qpwd.Value = DLookup("PWD", "tblLogIn", "USERID = '" & Me.quserid.Value & "'") Then
If yesno = Dlookup("Auth", tblLogin", "USERID = '" &
Me.quserid.Value & "'") = "Yes" Then
swtch = 1
Else
swtch = 0
End If
DoCmd.OpenForm "frmMain22", acNormal
Else
MsgBox "Invalid Password. Please try again."
End If

You get the idea.

Auth

 

[mauricionava]

I think this is not working for me

I tried adding the YES to the Auth colunm in the tblLogIn and:
If yesno = Dlookup("Auth", tblLogin", "USERID = '" & Me.quserid.Value & "'") = "Yes" Then
MsgBox "test 1"
to see if it pop up the msg but it did not!
So I guess it is not working in my main form either

This is frustrating, PLEASE HELP!

Thankssss

 

[fneily]

If yesno = Dlookup("Auth", tblLogin", "USERID = '" & Me.quserid.Value & "'") = "Yes"

should be

If Dlookup("Auth", tblLogin", "USERID = '" & Me.quserid.Value & "'") = "Yes"

My mistake. I hurried the answer.

 

[mauricionava]

Ok that worked OK.

And in the main form I would just put:

If swtch = 1 Then
    Me.btnDelete.Enabled = True
    Else
    Me.btnDelete.Enabled = False
End If

and it would work?

Thanks again.

 

[fneily]

Yeah, I'm assuming you have swtch =1 replacing the MsgBox statement. You would put your new code on the OnOpen Event of the form you're opening. So you could do your Enabled property or do the Visible property to hide it or make it appear. Your choice. But it looks like you have it.

 

[mauricionava]

works as I wanted.

thank you very much.

 

[OrionDeltaPex]

Sorry. I want to know if you can help me with my query. Im trying to develop a login form that upon loggin in this will prompt the user to different forms depending on their profile / account definition.

here is my source code. pls help me on this.

Private Sub Form_Open(Cancel As Integer)
'On open set focus to combo box
Me.cboEmployee.SetFocus
End Sub

Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
Me.txtPassword.SetFocus
End Sub
Private Sub cmdLogin_Click()

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

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

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

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

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


'strAccessLevel = DLookup("[strAccess]", "tblEmployees", "[strEmpName] = '" & Me.cboEmployee & "'")

' Select Case strAccess

' Case 1
' DoCmd.OpenForm "TQC Main Menu"
'strForm = "TQC Main Menu"

' Case 2
'strForm = "TQC_Main_Menu_lvl2"

'End Select

' DoCmd.OpenForm strForm

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


intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub





Thanks in advance.