'multiple tags' code doesn't block command button access--Access 2007 (1 Viewer)

[Minddumps]

I was provided code to allow Access to read multiple tags to allow access to certain command buttons within my database:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
'Users 1 and 7 have access to all buttons
If UserAccessID = 1 Or UserAccessID = 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, UserAccessID) > 0
        End If
    Next
End If
End Sub

the InStr was to allow me to input multiple UserAccessID's in my tags; however, I just learned today that it doesn't block the command button as long as the tag is above 0 (er so I assume as above it states UserAccessID >0) Well that definitely doesn't solve my issue

This is an example of what is in one of my cmd button tag properties: "1,7,8,9,10,11,12,13,14,15,16"

Anyone know what to change in order to fix the problem I'm having?

 

[boblarson]

The > 0 just means that it will enable it if the Instr returns a value which is the start position of the matched string. It doesn't mean that the userID is above zero.

Does adding the parens work:

ctl.Enabled = (InStr(ctl.Tag, UserAccessID) > 0)

 

[thechazm]

You might also try this:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
'Users 1 and 7 have access to all buttons
If UserAccessID = 1 Or UserAccessID = 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
            if instr(ctl.Tag, UserAccessID) > 0 Then
                     ctl.enabled = true
            end if
        End If
    Next
End If
End Sub

 

[boblarson]

You might also try this:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
'Users 1 and 7 have access to all buttons
If UserAccessID = 1 Or UserAccessID = 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
            if instr(ctl.Tag, UserAccessID) > 0 Then
                     ctl.enabled = true
            end if
        End If
    Next
End If
End Sub

Although it is the same as what I posted, just with the test on different lines essentially. So the other one would work if this one would work.

 

[Minddumps]

Thanks guys for the ideas, but unfortunately neither of those solutions worked.

On this one particular page, there are 7 buttons.

cmd1A1 tag: 1, 7, 8, 9
cmd1A3 tag: 1, 7, 8, 10
cmd1A4 tag: 1, 7, 8, 11
cmd1A5 tag: 1, 7, 8, 12
... and so on

could it be I should use a semi-colon instead of a comma or something?

 

[Minddumps]

hmm maybe it's missing the If the ID isn't listed in the tag, then ctl.Enable = False ?

 

[thechazm]

If the property of the controls are set to enabled = yes before you run the form then yes that could be the problem. You would need something like this to make that work in code:

if instr(ctl.Tag, UserAccessID) > 0 Then
ctl.enabled = true
else
ctl.enabled = false
end if

 

[stopher]

Also note that (for example) UserAccessID = 2 will be valid in the following:

cmd1A5 tag: 1, 7, 8, 12

This is because the tag is being treated as a string and not a set of numbers. You can overcome this with something like this hideous expression:

If InStr("," & Replace(ctl.Tag, " ", "") & ",", "," & UserAccessID & ",") > 0 Then


Explanation: we've remove spaces and add commas at the start and end of the string:

"," & Replace(ctl.Tag, " ", "") & ","

so 1, 7, 8, 12 becomes ,1,7,8,12,

And then we can search for our UserAccessID with commas either side

so search for "," & UserAccessID & "," e.g. ,2,

It would make nicer code if your tag looked something like /1/7/8/12/ but hey..

Anyway, I don't think I'd store the valid UserAccessIDs in tags. This isn't very easy to manage. Suppose you want to set up another UserAccessID. Then you've got to go through all your code. Much better to store this data in a table:

FormName____ControlName____UserAccessID
form1_________cmd1A1_________1
form1_________cmd1A1_________7
form1_________cmd1A1_________8
form1_________cmd1A3_________1
etc

This way you can just iterate the records for the required UserAccessID/form and set the controls accordingly.

hth
Chris

 

[Minddumps]

Anyway, I don't think I'd store the valid UserAccessIDs in tags. This isn't very easy to manage. Suppose you want to set up another UserAccessID. Then you've got to go through all your code. Much better to store this data in a table:

This way you can just iterate the records for the required UserAccessID/form and set the controls accordingly.

Thank you for your suggestion Chris, as many tags as I'd have to create for each button I now have to agree with you (I was thinking 50 and realized I will need almost 10 times more lol!). I'll begin the research on cmd access from a form.