Log on form coding issue

[Rob Ross]

I am fairly new to the VBA coding in access and would like some help with a log on form I am using for my database. I have the following code that works fine for opening the form I have mentioned but would like the code to break it out a little further by checking to see if the admin box (yes/no)is checked and open a different form depending on yes or no. Here is the code I have presently:

'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
'Check value of password in tblEmployees to see if this matches value chosen in combo box
If Me.txtPassword.Value = DLookup("Password", "Technicians", "[ID]=" & Me.cboEmployee.Value) Then
MyID = Me.cboEmployee.Value
'Close logon form and open splash screen
DoCmd.Close acForm, "frmStartUp", acSaveNo
DoCmd.OpenForm "frmAdm"
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown
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
I use a Log in Box and a Password box for inputting the data and I have an Admin field (yes/No) in my "Technicians" table. If yes for Admin - open frmAdm and if No - open frmUser.

Can anyone help?

 

[VilaRestal]

Something like:

If Nz(DLookup("Admin", "Technicians", "[ID]=" & Me.cboEmployee.Value),0) Then
DoCmd.OpenForm "frmAdm"
Else
DoCmd.OpenForm "frmUser"
End If

to replace the DoCmd.OpenForm "frmAdm" line

 

[Rob Ross]

Thanks for the code, that is what I was looking for but now get an error:
Runtime error 2467: The expression you entered refers to an object that is closed or doesn't exist.

Any Ideas?

 

[VilaRestal]

Of course, it's because the form has already closed and the code is referring to the combobox.

Change it to:

'Close logon form and open splash screen
Dim iEmp as Long
iEmp = Me.cboEmployee
DoCmd.Close acForm, "frmStartUp", acSaveNo
If Nz(DLookup("Admin", "Technicians", "[ID]=" & iEmp),0) Then
DoCmd.OpenForm "frmAdm"
Else
DoCmd.OpenForm "frmUser"
End If

 

[Rob Ross]

That did the trick. Thanks so much. Hopefully I'll get this coding figured out soon.

 

[Rob Ross]

Can you tell me if the code that keeps the application window hidden will cause a report to not open? All forms seem to open fine but when I click a button to open a report it locks up the program without opening the report (with pop up and modal set to yes) if I mark them no then it don't open the report but it don't lock up the program. Is there a setting I am missing?

 

[VilaRestal]

No, that is the downside to hiding the application window.

I can't quite remember what I found when I looked at this but I came to the conclusion to do all reports in Word or Excel.

Maybe others know of a way round it. Perhaps you could export them to pdf and open the pdf.