Choose Form to open Based on Values in Table

[cdoublen]

Hi All

I'm very new to VB coding for Access. At present I'm doinga dbase which has a logon screen. Upon "User" or "Admin" logon I would like a relevant form be opened based on "User" or "Admin" criterion. I have a table that contains UserList with UserName, Password, ID, AccessAS as fields. In The "AccessAS" field there are only "User" and "Admin" values. So Administartors have "Admin" value and other Usres have "User". I would appreciate any help with code that will open a form for a User when a User logs on or an Admin form when an Admin Logs on by looking the AccessAS value in the table corresponding to the individual that is logging on.

Thanking you all in Advance.

[NeoPa]

You haven't included any existing code into which to add code, so this will have to be general.

When you have determined the user, and confirmed their password is valid, access the [AccessAS] value set a string variable to the name of one form if it's "User", and the other if it's "Admin". Next, use this string in your form open command.

[Mihail]

Or (pseudo cod):

If UserIsAdmin then
    DoCmd.OpenForm "AdminFormName"
Else
    DoCmd.OpenForm "UsersFormName"
End If

[cdoublen]

Hi NeoPa

That is true I should have posted the code here. Please see below.
It includes my attempts in which now I have changed into comments.

Option Compare Database
Private intLogonAttempts As Integer
 
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
 
'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
 
        lngMyEmpID = Me.cboEmployee.Value
 
'Close logon form a
 
        DoCmd.Close acForm, "frmLogon", acSaveNo
'Open dashboard form
 
                'Dim straccess As String
        'straccess = DLookup("straccess", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
        'If Len(straccess) > 0 Then
            'If straccess = "Admin" Then
              'DoCmd.OpenForm "admin_dashboard"
            'Else
              'DoCmd.OpenForm "admin_dashboard"
            'End If
        'End If
 
 
'If password Invalid
 
        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

[cdoublen]

Mihail

I have tried your code, but it simply opens the form for "else".

BTW how does this code check the table? I thought there would have been something like a DlookUp or something to to see if the user was an Admin or not from the existing table with users.

Further help will be appreciated.

[Mihail]

This is YOUR code

'If straccess = "Admin" Then
    'DoCmd.OpenForm "admin_dashboard"
'Else
    'DoCmd.OpenForm "admin_dashboard"
'End If

So, if you say to Access to open the same form (admin_dashboard) in If clause AND in Else clause too, Access will do that. :))

Cheers !

[cdoublen]

Mihail

Still it makes it makes no difference. On Else I specified a different form so code looked like below but all the when either Admin or User logs in opens "BreakDowns".

    'If straccess = "Admin" Then
        'DoCmd.OpenForm "admin_dashboard"
    'Else
        'DoCmd.OpenForm "BreakDowns"
    'End If

[NeoPa]

Please study Before Posting (VBA or SQL) Code carefully before continuing. There is no Option Explicit line in your code and you aren't using the CODE tags as required.

CNN:
I have tried your code, but it simply opens the form for "else".

BTW how does this code check the table? I thought there would have been something like a DlookUp or something to to see if the user was an Admin or not from the existing table with users.

That indicates that whatever you are using for what Mihail called UserIsAdmin is faulty. This is not a part of the question. This is something basic you're expected to bring to the table with you. Of course Mihail didn't explain how to do that as your question implied you already had that information to hand.

Your line #44 should actually do a reasonable job of setting that for you assuming the data in your db is set up correctly and you're using the correct names for fields and variables etc (which we couldn't help with anyway).

[cdoublen]

Guys

I came across the code below from a similar query here (http://bytes.com/topic/access/answer...different-form) which I have been testing but the last portion where it should choose the form to open just does not respond, logging in password is cleared and no form opens. Here is the code.

Option Compare Database
Private Sub cmdLogin_Click()
 
  'Check to see if data is entered into the UserName combo box
Dim lngEmpID As Long
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
lngEmpID = Me.cboEmployee.Value
'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 tblAdmins to see if this matches value chosen in combo box
 
If Me.txtPassword.Value <> DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & lngEmpID) Then
            MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
            Me.txtPassword.SetFocus
            Me.txtPassword = Null
            intLogonAttempts = intLogonAttempts + 1
            'If User Enters incorrect password 3 times database will shutdown
            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
 
Else
    Me.txtPassword = Null
    'Open correct form
    Dim strAccess As String
 
    strAccess = DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
 
    If strAccess = "Admin" Then
        MsgBox "Welcome " & DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
        DoCmd.Close
        DoCmd.OpenForm "admin_dashboard"
    ElseIf strAccess = "User" Then
        MsgBox "Welcome " & DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
        DoCmd.Close
        DoCmd.OpenForm "BreakDowns"
    End If
End If
 
 End Sub

[NeoPa]

Well done for managing the [code] tags. There are other points in there you can benefit from though. Option Explicit is extremely helpful for any developer and you are losing much assistance by not using it in your code. I recommend you revisit the article as there is more in there than you've found. I'll leave that with you for now though.

As for this new post, I'm not sure what you expect us to do when you go off at a tangent like this. If you want our advice then it's here available. If you would rather take another approach then I'm sure we can live with that too. What makes little sense is to ask us to ignore what we know and start commenting on new ideas you've got from elsewhere.

I suggest you'd be better off responding to and dealing with the advice that's been offered. That way we can progress a meaningful conversation.

[cdoublen]

Apologies NeoPa

I did not realize there were additional responses after my second response to Mihail. I guess it was an issue with my internet being to slow. As I seem to have moved a step ahead with the second option I would like to stick to this in order not to loose focus. Please advice me on my previous post as to why the forms do not open. I have rechecked the names of my tables and fields and are correct please see image attached. Thank you for your advice and support.

[NeoPa]

If you want to take that approach (in spite of my comments) then I suggest you post a separate question. The question of this thread is as asked in post #1. We don't allow threads to meander around for what I would hope are obvious reasons.

I suspect you'll hit the same problems again though, as your approach to something you don't understand seems to be to back off and find a new angle, instead of to push harder until you get it. It's an understandable approach, but rarely successful.

Good luck anyway.

PS. I guess you didn't check (or maybe it works differently for you) but your picture doesn't show - either here or directly on the link.

[cdoublen]

Bingooooo! You know what NeoPa your last comment broke my feet but hey you made me go back to my original code stick to it like a tick and you know what it works. My most persistent error was due to reference to order of events when I was closing the login form prior to testing "User" and "Admin" strings. It now works like a charm. I followed your advise too with Option Explicit which is cool, I've now setup in tools for Option Explicit thus every of my code must simply use. Attached is my code that worked, in case somebody else comes faces similar problem. Cheers Thanks dude.

    Option Compare Database
    Option Explicit
    Private intLogonAttempts As Integer
 
    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
 
    'Check value of password in tblEmployees to see if this matches value chosen in combo box
 
        If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
    Dim lngMyEmpID As String
            lngMyEmpID = Me.cboEmployee.Value
 
    'Open dashboard form
 
                    Dim straccess As String
            straccess = DLookup("straccess", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
            If Len(straccess) > 0 Then
                If straccess = "Admin" Then
                  DoCmd.OpenForm "admin_dashboard"
                Else
                  DoCmd.OpenForm "breakdowns"
                End If
            End If
 
    'Close logon form a
 
            DoCmd.Close acForm, "frmLogon", acSaveNo
 
    'If password Invalid
 
            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
 
 

[NeoPa]

That's excellent news :-)

Some people are convinced I say all that stuff simply because I'm an uncaring hard-ass. It's really good to see that it can sometimes produce the intended results.

If only one in a hundred respond as you have, then it's still worth the other 99 wasted comments. Good for you.