Problem with Select Case - Help!

[matthewnsarah07]

I am using a basic logon form on my database which checks the username and password. Originally it then took all users to one screen, I have added in a SelectCase statement in order to try and direct users to their own form but whatever user I put in it still sends them to the old splash screen.

I have attached the code below, can anyone spot where I have gone wrong - Combo12(unbound) is the box in the form where the username comes from and this is linked the tblemployees for the values.

Thanks for your help

Matt

Private Sub cmdLogin_Click()
'Check to see if data is entered into the UserName combo box
If IsNull(Me.Combo12) Or Me.Combo12 = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.Combo12.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.Combo12.Value) Then
lngMyEmpID = Me.Combo12.Value
 
 
Select Case [Combo12].Value
   Case "User1"
     DoCmd.OpenForm "User1"
   Case "User2"
     DoCmd.OpenForm "User2"
   Case "User3"
     DoCmd.OpenForm "User3"
   Case Else
     DoCmd.OpenForm "frmSplash_Screen"
 
 
 End Select
 
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

 

[namliam]

You are sure your combo box is bound to the employee name?? Combo1.Value will return the bound column.

Question:
Do you aim to add all users to diffent forms??? I dont think so are you?
Maybe it would be better to create a table with Username, Formname (and additional other stuff) which you can query to see what you should do/allow/open etc for this user the moment he logs on or enters a form or...

 

[matthewnsarah07]

Thanks for your quick reply.

I did look at example a while ago which used a qury to set access levels on forms etc.

Any advice on how I would go about doing this?

 

[namliam]

Any advice on how I would go about doing this?

My advice:

Maybe it would be better to create a table with Username, Formname (and additional other stuff) which you can query to see what you should do/allow/open etc for this user the moment he logs on or enters a form or...

 

[boblarson]

Just for your knowledge, the reason why your code is failing is that you are testing, in the Select Case statement, for "User1", etc. which is a string, but according to your own code it is a long integer. So, testing for "User1," "User2," etc. will never be true and therefore the Case Else statement is executing.

 

[matthewnsarah07]

Mailman

How do I use a query to set levels of access - for example if the person logs on normally and is sent to the main switchboard how do I use the query to disable functions they do not have access to:

E.g

If the switchboard has
Forms
Reports
Users

and I wanted to disable access to Forms and Users

Thanks for your help - its appreciated

 

[namliam]

You make a table with User, Form to open
Then if a user logs on find the user in your table, and open the form assigned in the table...

The switchboard is something new to this discussion, but should work the same way...
Make a user table with the Switchboard elements you want to show to this user, link the query to your switchboard and presto, done.

 

[matthewnsarah07]

Right, I have set up a query which uses the username selected to search the tblEmployee and returns the form to go to.

To test it I had the query result returned to a combo box on the logon form and then the code picked up the form name to go to from there.

How do I now get the code to pick up the result of the query without displaying on screen or can i just get the code to pick the top result from the combo box?

 

[namliam]

You can just hide the combobox you created right??

You can run a query in VBA code offcourse.... but stick with what you know, you have the working combo, go with it.

 

[matthewnsarah07]

Last question - when the query updates the combo box - the box remains blank until you click onto it and select the result - is there anyway to make the combo box automatically display the result of the query instead of selecting it

Thanks

 

[doco]

Just for your knowledge, the reason why your code is failing is that you are testing, in the Select Case statement, for "User1", etc. which is a string, but according to your own code it is a long integer. So, testing for "User1," "User2," etc. will never be true and therefore the Case Else statement is executing.

Ahem... As Bob has pointed out: your select case is testing for string literals not LONG INTEGER VALUES. Thus the struct is working supremely and as designed.

 

[namliam]

Ahem... As Bob has pointed out: your select case is testing for string literals not LONG INTEGER VALUES. Thus the struct is working supremely and as designed.

The / My point tho is that hardcoding stuff like this is generaly not a good idea... Which I think the OP is agreeing with.

Last question - when the query updates the combo box - the box remains blank until you click onto it and select the result - is there anyway to make the combo box automatically display the result of the query instead of selecting it

OK, so you dont know... Have a search for "Cascading combobox" see if you can find a sample in the sample part of this forum

Or use something like below
Dim RS as DAO.Recordset
Set RS = Currentdb.openrecordset("YourQuery")
rs!Yourfield <= Would contain your form to open...
' Now clean it up.
rs.close
set rs = nothing
Good luck !

 

[doco]

The / My point tho is that hardcoding stuff like this is generaly not a good idea... Which I think the OP is agreeing with.

And of course I am in agreement with you as well. But does not remove the fact that the OP original code is working perfectly - just not the way he is expecting. Because he is asking the wrong question in the Select Case struct.

 

[boblarson]

And of course I am in agreement with you as well. But does not remove the fact that the OP original code is working perfectly - just not the way he is expecting. Because he is asking the wrong question in the Select Case struct.

And the question that should be asked (if using the method posted, not using the table method which is actually the better route and which I would be in favor of too) is:

Select Case Me.Combo12[color=red].Column(1)[/color]

 

[matthewnsarah07]

Thanks Bob & Mailman

Your help has been very much appreciated