Multiple If Statements in Access VBA?

Microsoft Microsoft access 2007 full ver...
September 1, 2010 at 12:43:11
Specs: Windows 7
HELP!!!!!! I have some code that checks to see if a users password is correct; if it is correct, I want the code after that to check a query to see what form to open based on the users permissions (1=Power User, 2=General User, 3=Casual User) which are directly tied to the username the person types in because of my "User ID" Table. I hope that makes sense, if it doesn't, ask a question and I can be more descriptive. Here is an example of the code I am trying to use. As you can see, I want my code to check if their permissions group is 1,2, or 3, then open the corrisponding form. Thanks!!!!!


If Me.Text26.Value = DLookup("Password", "ADMIN User ID", _
"[ID]=" & Me.Combo24.Value) Then

lngMyEmpID = Me.Combo24.Value

'checks the permissions of the user logging in and brings back
'the proper form with the permissions they are allowed
If [Find Permissions of Logged In User]![PermissionsGroup] = "1" Then
DoCmd.OpenForm "Welcome Menu Screen"
End If
If [Find Permissions of Logged In User]![PermissionsGroup] = "2" Then
DoCmd.OpenForm "Welcome Menu Screen General User"
End If
If [Find Permissions of Logged In User]![PermissionsGroup] = "3" Then
DoCmd.OpenForm "Welcome Menu Screen casual user"
End If


'Close logon form
DoCmd.Close acForm, "Logon Screen", acSaveNo
'command below will just hide the logon screen if i need to refer to the combobox
Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.Text26.SetFocus
End If


See More: Multiple If Statements in Access VBA?

Report •

#1
September 3, 2010 at 15:35:41
For selecting an option from multiple conditions, I would use a Select statement.

Example:

Select [Find Permissions of Logged In User]![PermissionsGroup]
Case = “1”
Docmd..OpenForm “Some Form’
Case =”2”
Docmd.OpenForm ‘SomeOtherForm”
Case Else = Other Command
End Select

This scenario checks for all your conditions with one statement.


Report •

#2
September 3, 2010 at 16:00:56
Sorry. I gave you an example with a syntax error. See below.

For selecting an option from multiple conditions, I would use a Select statement.

Example:

Select Case [Find Permissions of Logged In User]![PermissionsGroup]
Case = “1”
Docmd..OpenForm “Some Form’
Case =”2”
Docmd.OpenForm ‘SomeOtherForm”
Case Else = Other Command
End Select

This scenario checks for all your conditions with one statement.


Report •

#3
September 4, 2010 at 11:50:03
Thanks for your response! I will give this a shot when I get back in the office on tuesday and let you know! Thanks again!

Report •

Related Solutions

#4
September 7, 2010 at 08:17:21
After I put your code in with mine, I get a run-time error '2465'.

Microsoft Office Access can't find the field '|' referred to in your expression.

It Highlights the first "Select Case" line that refers to the field in the query I am referring to. Is there a way to specify which line from the query I am referring to? I'm thinking I get this error because I am only specifying the column. The way I set up the query is to remove the value if the user logs off. So there is allways only one value in the column in the query. I know this is really confusing, but any help you could give me would ge greatly appreciated!

Here is my code:
'Close logon form and open Module to check permissions
Select Case [Find Permissions of Logged In User]![PermissionsGroup]
Case Is = "1"
DoCmd.OpenForm "Welcome Menu Screen"
Case Is = "2"
DoCmd.OpenForm "Welcome Menu Screen General User"
Case Is = "3"
DoCmd.OpenForm "Welcome Menu Screen Casual User"
End Select

DoCmd.Close acForm, "Logon Screen", acSaveNo


Report •

Ask Question