By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,761 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,761 IT Pros & Developers. It's quick & easy.

Choose Form to open Based on Values in Table

P: 8
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.
Apr 3 '12 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,489
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.
Apr 4 '12 #2

100+
P: 759
Or (pseudo cod):
Expand|Select|Wrap|Line Numbers
  1. If UserIsAdmin then
  2.     DoCmd.OpenForm "AdminFormName"
  3. Else
  4.     DoCmd.OpenForm "UsersFormName"
  5. End If
Apr 4 '12 #3

P: 8
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.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private intLogonAttempts As Integer
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. 'On open set focus to combo box
  6. Me.cboEmployee.SetFocus
  7. End Sub
  8.  
  9. Private Sub cboEmployee_AfterUpdate()
  10. 'After selecting user name set focus to password field
  11. Me.txtPassword.SetFocus
  12. End Sub
  13.  
  14. Private Sub cmdLogin_Click()
  15.  
  16. 'Check to see if data is entered into the UserName combo box
  17.  
  18.     If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  19.             MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  20.             Me.cboEmployee.SetFocus
  21.         Exit Sub
  22.     End If
  23.  
  24. 'Check to see if data is entered into the password box
  25.  
  26.     If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  27.             MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  28.             Me.txtPassword.SetFocus
  29.         Exit Sub
  30.     End If
  31.  
  32. 'Check value of password in tblEmployees to see if this matches value chosen in combo box
  33.  
  34.     If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  35.  
  36.         lngMyEmpID = Me.cboEmployee.Value
  37.  
  38. 'Close logon form a
  39.  
  40.         DoCmd.Close acForm, "frmLogon", acSaveNo
  41. 'Open dashboard form
  42.  
  43.                 'Dim straccess As String
  44.         'straccess = DLookup("straccess", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
  45.         'If Len(straccess) > 0 Then
  46.             'If straccess = "Admin" Then
  47.               'DoCmd.OpenForm "admin_dashboard"
  48.             'Else
  49.               'DoCmd.OpenForm "admin_dashboard"
  50.             'End If
  51.         'End If
  52.  
  53.  
  54. 'If password Invalid
  55.  
  56.         Else
  57.         MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
  58.         Me.txtPassword.SetFocus
  59.     End If
  60.  
  61. 'If User Enters incorrect password 3 times database will shutdown
  62.  
  63.     intLogonAttempts = intLogonAttempts + 1
  64.     If intLogonAttempts > 3 Then
  65.         MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
  66.         Application.Quit
  67.     End If
  68.  
  69. End Sub
Apr 4 '12 #4

P: 8
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.
Apr 4 '12 #5

100+
P: 759
This is YOUR code
Expand|Select|Wrap|Line Numbers
  1. 'If straccess = "Admin" Then
  2.     'DoCmd.OpenForm "admin_dashboard"
  3. 'Else
  4.     'DoCmd.OpenForm "admin_dashboard"
  5. '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 !
Apr 4 '12 #6

P: 8
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".
Expand|Select|Wrap|Line Numbers
  1.     'If straccess = "Admin" Then
  2.         'DoCmd.OpenForm "admin_dashboard"
  3.     'Else
  4.         'DoCmd.OpenForm "BreakDowns"
  5.     'End If
Apr 4 '12 #7

NeoPa
Expert Mod 15k+
P: 31,489
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).
Apr 4 '12 #8

P: 8
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.


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub cmdLogin_Click()
  3.  
  4.   'Check to see if data is entered into the UserName combo box
  5. Dim lngEmpID As Long
  6. If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  7.     MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  8.     Me.cboEmployee.SetFocus
  9.     Exit Sub
  10. End If
  11. lngEmpID = Me.cboEmployee.Value
  12. 'Check to see if data is entered into the password box
  13.  
  14. If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  15.     MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  16.     Me.txtPassword.SetFocus
  17.     Exit Sub
  18. End If
  19.  
  20. 'Check value of password in tblAdmins to see if this matches value chosen in combo box
  21.  
  22. If Me.txtPassword.Value <> DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & lngEmpID) Then
  23.             MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
  24.             Me.txtPassword.SetFocus
  25.             Me.txtPassword = Null
  26.             intLogonAttempts = intLogonAttempts + 1
  27.             'If User Enters incorrect password 3 times database will shutdown
  28.             If intLogonAttempts >= 3 Then
  29.                 MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
  30.                 Application.Quit
  31.             End If
  32.  
  33. Else
  34.     Me.txtPassword = Null
  35.     'Open correct form
  36.     Dim strAccess As String
  37.  
  38.     strAccess = DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
  39.  
  40.     If strAccess = "Admin" Then
  41.         MsgBox "Welcome " & DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
  42.         DoCmd.Close
  43.         DoCmd.OpenForm "admin_dashboard"
  44.     ElseIf strAccess = "User" Then
  45.         MsgBox "Welcome " & DLookup("strEmpName", "tblEmployees", "[lngEmpID]=" & lngEmpID)
  46.         DoCmd.Close
  47.         DoCmd.OpenForm "BreakDowns"
  48.     End If
  49. End If
  50.  
  51.  End Sub
Apr 5 '12 #9

NeoPa
Expert Mod 15k+
P: 31,489
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.
Apr 5 '12 #10

P: 8
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.

Apr 5 '12 #11

NeoPa
Expert Mod 15k+
P: 31,489
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.
Apr 5 '12 #12

P: 8
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.
Expand|Select|Wrap|Line Numbers
  1.     Option Compare Database
  2.     Option Explicit
  3.     Private intLogonAttempts As Integer
  4.  
  5.     Private Sub Form_Open(Cancel As Integer)
  6.     'On open set focus to combo box
  7.     Me.cboEmployee.SetFocus
  8.     End Sub
  9.  
  10.     Private Sub cboEmployee_AfterUpdate()
  11.     'After selecting user name set focus to password field
  12.     Me.txtPassword.SetFocus
  13.     End Sub
  14.  
  15.     Private Sub cmdLogin_Click()
  16.  
  17.     'Check to see if data is entered into the UserName combo box
  18.  
  19.         If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
  20.                 MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
  21.                 Me.cboEmployee.SetFocus
  22.             Exit Sub
  23.         End If
  24.  
  25.     'Check to see if data is entered into the password box
  26.  
  27.         If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
  28.                 MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
  29.                 Me.txtPassword.SetFocus
  30.             Exit Sub
  31.         End If
  32.  
  33.     'Check value of password in tblEmployees to see if this matches value chosen in combo box
  34.  
  35.         If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value) Then
  36.     Dim lngMyEmpID As String
  37.             lngMyEmpID = Me.cboEmployee.Value
  38.  
  39.     'Open dashboard form
  40.  
  41.                     Dim straccess As String
  42.             straccess = DLookup("straccess", "tblEmployees", "[lngEmpID]=" & Me.cboEmployee.Value)
  43.             If Len(straccess) > 0 Then
  44.                 If straccess = "Admin" Then
  45.                   DoCmd.OpenForm "admin_dashboard"
  46.                 Else
  47.                   DoCmd.OpenForm "breakdowns"
  48.                 End If
  49.             End If
  50.  
  51.     'Close logon form a
  52.  
  53.             DoCmd.Close acForm, "frmLogon", acSaveNo
  54.  
  55.     'If password Invalid
  56.  
  57.             Else
  58.             MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
  59.             Me.txtPassword.SetFocus
  60.         End If
  61.  
  62.     'If User Enters incorrect password 3 times database will shutdown
  63.  
  64.         intLogonAttempts = intLogonAttempts + 1
  65.         If intLogonAttempts > 3 Then
  66.             MsgBox "You do not have access to this database.  Please contact your system administrator.", vbCritical, "Restricted Access!"
  67.             Application.Quit
  68.         End If
  69.  
  70.     End Sub
  71.  
  72.  
Apr 5 '12 #13

NeoPa
Expert Mod 15k+
P: 31,489
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.
Apr 5 '12 #14

Post your reply

Sign in to post your reply or Sign up for a free account.