Login form to open different forms

[HBTCLaura]

I have 3 tables to store information;
---
tblStudent
StuID (PK) Autonum
StuName
StuAddress
etc
---
tblEmployer
EmpID (PK) Autonum
EmpName
EmpSite
etc
---
tblStaff
StaID (PK) Autonum
StaName
StaJob

When enquiring we enter the details of the student/employer/staff. If they want to take it futrther than enquiring I want to create a login for them so they can login and see their details.
I've managed to get this to work using the code below that checks the data entered in the username (cboEmployer) and password (txtPassword) boxes of the form match what is stored in the table and if it is opens a set form, but I have to open a seperate login window depending on if it is an employer/student/staff member trying to login.

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboMarketing) Or Me.cboMarketing = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboMarketing.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("MktPassword", "tblMarketing", _
"[MktID]=" & Me.cboMarketing.Value) Then
MktID = Me.cboMarketing.Value
'Close logon form and open splash screen

DoCmd.OpenForm "frmMarketing", , , , , , Me![cboMarketing]
DoCmd.Close acForm, "frmMarketingLogon", acSaveNo

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 admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

What I want to be able to do is have one form that will check all 3 tables and open a form based on which table they are found in.

Firstly is this possible and, if so, how do I go about it?

I am by no means an expert at this stuff so any help will be greatly appreciated!

 

[mdlueck]

air code...

if ID found in table 1 then
   open form 1
else if ID found in table 2 then
   open form 2
else if ID found in table 3 then
   open form 3
else
       Error - ID not found
endif

Would something like that work in a central start place? In my apps, I have a non visual form ahead of the first visual form. If you had such, you could have that form decide which form to open next.

I have code on the Load / Open events of that non visual form, and at the end of the form Load() which starts the application I have...

  strDocName = "projects"

  'Open the projects window and exit
  DoCmd.OpenForm strDocName
  DoCmd.Close acForm, Me.Name

Thus opening the next form to be chained on to and closing self.

 

[CBrighton]

Personally I'd cheat and add a combobox on the login form for the user to select the login type.

Although in reality I'd have a single logins table with a text column to signify the access level (or in this case staff / employer / student) to avoid the problem. I would then either base the form which is opened on the access level or possibly on another column which holds the name of the form which that user should have opened.

 

[HBTCLaura]

Hey,

Thanks for your reply.
This has been bugging me for weeks and typically as soon as I ask for help on here I figure it out!

My solution:
create another table tblLogin to keep login data;
LogID (PK) Autonum
EmpID (FK)
StuID (FK)
StaID (FK)
LogPassword
LogForm (name of the form the individual should be directed to)

I then created 3 queries to select the LogID, Emp/Stu/StaID, LogForm and the relevent names (employer/staff/student name for cbobox purposes). I then used a union query to list these in one datasheet.

On my login form I have a combobox "cboEmployer" and unbound textbox "txtPassword" and a button "btnOK". cboEmployer uses the union query data with column0=LogID, column1=FirstName, column2=Surname and column3=LogForm. Column 0 and 3 have their width set to 0" to hide them in the combobox but this means that in the code I can use colum 3 to determine what form to open. btnOK has the following VBA set to 'on click':

Private Sub btnOK_Click()

'Check to see if data is entered into the UserName combo box
If IsNull(Me.cboEmployer) Or Me.cboEmployer = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployer.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("LogPassword", "tblLogin", _
"[LogID]=" & Me.cboEmployer.Value) Then
LogID = Me.cboEmployer.Value

'Close logon form and open relevent page
Dim stDocName As String
stDocName = Forms!LOGIN!cboEmployer.Column(3)
DoCmd.OpenForm stDocName

DoCmd.Close acForm, "LOGIN", acSaveNo

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 admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If
End Sub


Not sure if this is a long winded way of doing it but it works and I'm happy!!

Thanks for your help anywho