Is If statment the best to use for multiple entries?

Minddumps

Minddumps

Registered User.
Local time
Today, 14:52
Joined
Jul 5, 2011
Messages
73
[SOLVED--SEE BELOW TRAFFIC]

I have a form in which only Users signed in with certian user access ID's can click on certian Command buttons on the form. Since there are 7 buttons on this one page, my if statements are growing very large.

Does anyone know if there an easier/better way to accomplish what I'm trying to do here?

Code: 
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
'Admin Access to all buttons Start Here
 If User.AccessID = 1 Then
        Me.Cmd1A1.Enabled = True
    Else
        Me.Cmd1A1.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A3.Enabled = True
    Else
        Me.Cmd1A3.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A4.Enabled = True
    Else
        Me.Cmd1A4.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A5.Enabled = True
    Else
        Me.Cmd1A5.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A6.Enabled = True
    Else
        Me.Cmd1A6.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A8.Enabled = True
    Else
        Me.Cmd1A8.Enabled = False
 If User.AccessID = 1 Then
        Me.Cmd1A9.Enabled = True
    Else
        Me.Cmd1A9.Enabled = False
'BDE Access to all buttons Start Here
 If User.AccessID = 7 Then
        Me.Cmd1A1.Enabled = True
    Else
        Me.Cmd1A1.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A3.Enabled = True
    Else
        Me.Cmd1A3.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A4.Enabled = True
    Else
        Me.Cmd1A4.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A5.Enabled = True
    Else
        Me.Cmd1A5.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A6.Enabled = True
    Else
        Me.Cmd1A6.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A8.Enabled = True
    Else
        Me.Cmd1A8.Enabled = False
 If User.AccessID = 7 Then
        Me.Cmd1A9.Enabled = True
    Else
        Me.Cmd1A9.Enabled = False
'Co Access to only their button starts here
 If User.AccessID = 9 Then
        Me.Cmd1A1.Enabled = True
    Else
        Me.Cmd1A1.Enabled = False
 If User.AccessID = 10 Then
        Me.Cmd1A3.Enabled = True
    Else
        Me.Cmd1A3.Enabled = False
 If User.AccessID = 11 Then
        Me.Cmd1A4.Enabled = True
    Else
        Me.Cmd1A4.Enabled = False
 
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
    End If
Exit_Form_Open:
    Exit Sub
Err_Form_Open:
    MsgBox Err.Description
    Me.Visible = True
    Resume Exit_Form_Open
 
End Sub
 
Last edited:
If I were going to do something like this, I would do the following;

1) Take advantage of the Tag property of the command buttons. So If UserID 9 is only supposed to have access to Cmd1A1 then put a 9 in the Tag property of this button. Repeat for the other buttons.

2) Then use code like the following;

Code: 
Dim ctl As Control
 
'Users 1 and 7 have access to all buttons
If UserID = 1 Or UserID = 7 Then
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            ctl.Enabled = True
        End If
    Next
Else
'Enable the command button only if the Tag matches the userID
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            ctl.Enabled = CLng(ctl.Tag) = Me.UserID
        End If
    Next
End If

This should work as long as the restricted users are each restricted to only 1 command button. Otherwise it would need to be modified a bit.

Also, the code may need to go in both the Open event and the Current event.
 
Also, if I indent the code do you see the logical flaw in it?

Code: 
  If User.AccessID = 1 Then
    Me.Cmd1A1.Enabled = True
  Else
    Me.Cmd1A1.Enabled = False
    If User.AccessID = 1 Then
      Me.Cmd1A3.Enabled = True
    Else
      Me.Cmd1A3.Enabled = False
      If User.AccessID = 1 Then
        Me.Cmd1A4.Enabled = True
      Else
        Me.Cmd1A4.Enabled = False
        If User.AccessID = 1 Then
          Me.Cmd1A5.Enabled = True
        Else
          Me.Cmd1A5.Enabled = False
          If User.AccessID = 1 Then
            Me.Cmd1A6.Enabled = True
          Else
            Me.Cmd1A6.Enabled = False
            If User.AccessID = 1 Then
              Me.Cmd1A8.Enabled = True
            Else
              Me.Cmd1A8.Enabled = False
              If User.AccessID = 1 Then
                Me.Cmd1A9.Enabled = True
              Else
                Me.Cmd1A9.Enabled = False
                'BDE Access to all buttons Start Here
                If User.AccessID = 7 Then
                  Me.Cmd1A1.Enabled = True
                Else
                  Me.Cmd1A1.Enabled = False
                  If User.AccessID = 7 Then
                    Me.Cmd1A3.Enabled = True
                  Else
                    Me.Cmd1A3.Enabled = False
                    If User.AccessID = 7 Then
                      Me.Cmd1A4.Enabled = True
                    Else
                      Me.Cmd1A4.Enabled = False
                      If User.AccessID = 7 Then
                        Me.Cmd1A5.Enabled = True
                      Else
                        Me.Cmd1A5.Enabled = False
                        If User.AccessID = 7 Then
                          Me.Cmd1A6.Enabled = True
                        Else
                          Me.Cmd1A6.Enabled = False
                          If User.AccessID = 7 Then
                            Me.Cmd1A8.Enabled = True
                          Else
                            Me.Cmd1A8.Enabled = False
                            If User.AccessID = 7 Then
                              Me.Cmd1A9.Enabled = True
                            Else
                              Me.Cmd1A9.Enabled = False
                              'Co Access to only their button starts here
                              If User.AccessID = 9 Then
                                Me.Cmd1A1.Enabled = True
                              Else
                                Me.Cmd1A1.Enabled = False
                                If User.AccessID = 10 Then
                                  Me.Cmd1A3.Enabled = True
                                Else
                                  Me.Cmd1A3.Enabled = False
                                  If User.AccessID = 11 Then
                                    Me.Cmd1A4.Enabled = True
                                  Else
                                    Me.Cmd1A4.Enabled = False

                                  End If
                                End If
                              End If
                            End If
                          End If
                        End If
                      End If
                    End If
                  End If
                End If
              End If
            End If
          End If
        End If
      End If
    End If
  End If
 
OR use Select Case

Code: 
Select case User.AccessID
  case 1
    Me.Cmd1.Enabled = True
    Me.Cmd2.Enabled = True
    Me.Cmd3.Enabled = True
    Me.Cmd4.Enabled = True
    Me.Cmd5.Enabled = True
    Me.Cmd6.Enabled = True
    Me.Cmd6.Enabled = True
  case 2
    Me.Cmd1.Enabled = False
    Me.Cmd2.Enabled = True
    Me.Cmd3.Enabled = True
    Me.Cmd4.Enabled = False
    Me.Cmd5.Enabled = False
    Me.Cmd6.Enabled = False
    Me.Cmd6.Enabled = True
  case 3
    Me.Cmd1.Enabled = True
    Me.Cmd2.Enabled = False
    Me.Cmd3.Enabled = True
    Me.Cmd4.Enabled = True
    Me.Cmd5.Enabled = True
    Me.Cmd6.Enabled = True
    Me.Cmd6.Enabled = False
  ...
  case else
end select
 
Beetle said:
1) Take advantage of the Tag property of the command buttons. So If UserID 9 is only supposed to have access to Cmd1A1 then put a 9 in the Tag property of this button. Repeat for the other buttons..
Click to expand...
Awesome, I learn something new everyday lol.

Beetle said:
2) Then use code like the following;...
Click to expand...
I tried putting this code you gave me on both on current and onOpen as you stated and came up with
Microsoft Visual Basic: Compile Error: Method or data member not found
Click to expand...
in the onOpen portion. Could it be because I'm trying to base access on an accessID rather than a userID?

pbaldy said:
Also, if I indent the code do you see the logical flaw in it?
Click to expand...
That it's definitely too long and may eventually run out of room and of course slow things down giving the system too much to read

smig said:
OR use Select Case
Click to expand...
I've seen this around before, how would I point an access ID as a case...

I'm guessing...
Code: 
[COLOR=black][FONT=Verdana]Select case User.AccessID[/FONT][/COLOR]
[COLOR=black][FONT=Verdana] case 1 'the 1 stands for the user.accessID already set?[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd1.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd2.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd3.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd4.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd5.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd6.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd6.Enabled = True[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]case 7 'the 1 stands for the user.accessID already set?[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd1.Enabled = False[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]  Me.Cmd2.Enabled = True[/FONT][/COLOR]
and so on? It's still not working for me :( see next post plz.

Thanks to all of youyou’re your input :) I look forward to your replies.
 
Last edited:
Arggg... I am not getting a case selection to work either. There aren't any errors popping up however, the "false"'s that I've been trying don't work. Maybe i have to point to the AccessID's table where the user.accesID's are coming from?
Code: 
Private Sub Form_Open(Cancel As Integer)
Select Case User.AccessID
  Case 1, 7, 8
    Me.Cmd1A1.Enabled = False
    Me.Cmd1A3.Enabled = True
    Me.Cmd1A4.Enabled = True
    Me.Cmd1A5.Enabled = True
    Me.Cmd1A6.Enabled = True
    Me.Cmd1A8.Enabled = True
    Me.Cmd1A9.Enabled = True
    Me.Cmd1A.Enabled = False
  Case 9
    Me.Cmd1A1.Enabled = True
    Me.Cmd1A3.Enabled = False
    Me.Cmd1A4.Enabled = False
    Me.Cmd1A5.Enabled = False
    Me.Cmd1A6.Enabled = False
    Me.Cmd1A8.Enabled = False
    Me.Cmd1A9.Enabled = False
    Me.Cmd1A.Enabled = False
 
  Case Else
End Select
End Sub
 
Last edited:
Minddumps said:
That it's definitely too long and may eventually run out of room and of course slow things down giving the system too much to read
Click to expand...

No, though certainly there are better ways of writing it that others have pointed out. I was pointing out the logical flaw. If this condition is true:

If User.AccessID = 1 Then

only that first control (Cmd1A1) will be set. All the other tests occur in the Else clause of that first test, so will never be executed if the AccessID is 1. If you still have a version with that code, set a breakpoint so you can step through the code, use an AccessID of 1, and run the code. You'll see what I mean.
 
pbaldy said:
All the other tests occur in the Else clause of that first test, so will never be executed if the AccessID is 1.
Click to expand...
hahaha OH! I definitely didn't catch that lol... I suppose I hadn't noticed anything bc I logged in only as the accessID 1 which is granted all access as an admin and then logged in as no accessID used (not logged in for example) thus far. Thanks for those words of knowledge :)


----You know I'm still looking at this, but I just realized that now I'm confused again because yes there is an else, but it's 'this' else 'this', then a new If starts so I dont see how that would cause another code to quit working. I assume the system would read each If statement as an individual statement. hmm.
 
Last edited:
I tried putting this code you gave me on both on current and onOpen as you stated and came up with
Quote:
Microsoft Visual Basic: Compile Error: Method or data member not found
Click to expand...

Then the code is attempting to reference something (a field, a control, a property, etc.) that doesn't exist in your application. There could be something in my example code that does not apply in your application, you could have mis-spelled something, hard to say. What line is highlighted when you debug or compile the code?
 
I had changed what u presented to mine:
Code: 
Dim ctl As Control
 
'Users 1 and 7 have access to all buttons
If User.AccessID = 1 Or User.AccessID = 7 Then
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            ctl.Enabled = True
        End If
    Next
Else
'Enable the command button only if the Tag matches the userID
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            ctl.Enabled = CLng(ctl.Tag) = User.AccessID
        End If
    Next
End If
The part that was highlighted with that message was the bottom line before the End If ("user."). Although, now I just inputted the same code and am getting a type mismatch error (Run-time error '13') instead of the data not found like before. The entire line before End If is what's highlighted now (ctl.Enabled = CLng(ctl.Tag) = User.AccessID) hmm interesting... The User.AccessID works when I use the If Statements as I posted in the very first post so I believe that part is correct (granted I only know about 80% of what I'm doing LOL).
 
Beetle said:
Then the code is attempting to reference something (a field, a control, a property, etc.) that doesn't exist in your application.
Click to expand...
I think you may just laugh at me!!! I was playing around with some things trying to fix the error I kept getting from the post before this and found out... the system logged me out so the userId was zero and that's what was giving me the error! Well I just found another task to tackle (how to completely close the program and cause the login button to pop up again when a user is automatically logged out)

Thank you again for your suggestion! It seems so much more simple than the way I was doing it earlier lol.

However if I do need to tag more than one userID; am I able to just comma-delimit in the tag properties of the button? so for example I need 1, 7,9,10, and 11 to all have access to this one button...
 
not sure where you took your original User.AccessID from:
Code: 
[FONT=Courier New]If User.AccessID = 1[/FONT]


Code: 
Select case User.AccessID
  Case 1
  Case 2
  ...
  Case else
end select
is just the same.

please notice I changed the names of the buttons ;)


what Minddumps wrote shuld also work:
Code: 
[FONT=Courier New]Select case[/FONT]
[FONT=Courier New] Case 1, 7, 8[/FONT]
[FONT=Courier New] ...[/FONT]
this is OK if 1, 7 and 8 should give the same result


for a start I suggest making sure you realy get the correct UserID using this code:
Code: 
[FONT=Courier New]Private Sub Form_Open(Cancel As Integer)[/FONT]
 
[FONT=Courier New]msgbox User.AccessID[/FONT]
 
[FONT=Courier New]end sub[/FONT]
 
However if I do need to tag more than one userID; am I able to just comma-delimit in the tag properties of the button? so for example I need 1, 7,9,10, and 11 to all have access to this one button...
Click to expand...

Yes, put a list of numbers in the Tag property, then use code like this;

Code: 
Dim ctl As Control
'Users 1 and 7 have access to all buttons
If Me.UserID = 1 Or Me.UserID = 7 Then
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            ctl.Enabled = True
        End If
    Next
Else
'Enable the button only if the UserID is in the Tag property
    For Each ctl In Me.Controls
        If ctl.ControlType = acCommandButton Then
            'use the InStr function to see if the UserID is in the Tag
            ctl.Enabled = InStr(ctl.Tag, Me.UserID) > 0
        End If
    Next
End If
 
just to answer your original question
Is If statment the best to use for multiple entries?
Click to expand...

I tink using Select is a better option
 
IMO, in a case like this I think that neither IF/Else or Select Case are good choices. If you write it using one of those, and you add more command buttons, or more users, at some point in the future, you have to go back and modify the code.

With code like the example I provided, all you have to do is add more command buttons and/or more users in the Tag properties. The code will still work with no modifications necessary.
 
smig said:
not sure where you took your original User.AccessID from:
Click to expand...
I'm signed in as admin which is ID 1.
please notice I changed the names of the buttons ;)
Click to expand...
yes I noticed and in my example a few posts above I changed the button names to my own, thank you.
Code: 
Private Sub Form_Open(Cancel As Integer)
Select Case User.AccessID
  Case 1, 7, 8
    Me.Cmd1A1.Enabled = False
    Me.Cmd1A3.Enabled = True
    Me.Cmd1A4.Enabled = True
etc...
for a start I suggest making sure you realy get the correct UserID using this code:
Click to expand...
hahaha yes that would help to make sure i'm actually signed in wouldn't it? lool[/QUOTE]

Beetle said:
Yes, put a list of numbers in the Tag property, then use code like this;
Click to expand...
Awesome, I ended up having to remove the Me. in front of my userid's for some reason. And my user ID's are referenced by User.AccessID, but other then that it worked perfect so thanks so much for your input. I really like this route.

Beetle said:
IMO, in a case like this I think that neither IF/Else or Select Case are good choices. If you write it using one of those, and you add more command buttons, or more users, at some point in the future, you have to go back and modify the code.

With code like the example I provided, all you have to do is add more command buttons and/or more users in the Tag properties. The code will still work with no modifications necessary.
Click to expand...
I like how easy the select case coding is too, but I think I agree with you... If I ever change add or change current user accesses (is that a word? lol) then I wouldn't want to have to go and change all the code in each form either.

I have one other question in reference to the tag code you provided me. What if I have a button that everyone is supposed to have access to, such as a menu button? Is there a way to tag 'All' instead of writing each user number in the tag property?

Thank you all again for all of your input, I love how intellegent you all are, I'm trying to soak in all of your 'wisdom's ;) lol
 
I have one other question in reference to the tag code you provided me. What if I have a button that everyone is supposed to have access to, such as a menu button? Is there a way to tag 'All' instead of writing each user number in the tag property?
Click to expand...
My apologies for asking a needless question, I just tried it and it worked woot! :D
 
You must log in or register to reply here.

Similar threads

D
Solved Looping on a form for several textboxes to be enabled
Replies
4
Views
588
June7
June7
D
Solved If...then or loop help
Replies
12
Views
1,056
June7
June7
D
Solved New detail records being added despite disabled button in master form
2
Replies
27
Views
846
GPGeorge
GPGeorge
A
Solved Referring to a control on a subform from the parent form.
Replies
3
Views
712
AlliCarr
A
J
VBA to print Word file and close is
Replies
8
Views
699
DickyP
DickyP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom