A better way than a bunch of If-Then DLookUp's

[chuckcoleman]

Hi,

I have some VBA code that runs when a form opens. The purpose of the code is to make visible or invisible 17 different Command buttons on the form. The reason for this has to giving certain users the ability to run certain reports and other users they can't. It takes a couple of seconds to load and I'm sure there is a way to improve this. My understanding is that DLookUp is typically slow which is probably part of the problem. The code below is for just two of the 17 Command buttons:

If DLookup("ControlAuto", "[Permissions Query]", "[ControlAuto] = " & 10 & "") = 10 Then
Me.PrtPreviewBalanceDueSummary.Visible = True
Else
Me.PrtPreviewBalanceDueSummary.Visible = False
End If
If DLookup("ControlAuto", "[Permissions Query]", "[ControlAuto] = " & 11 & "") = 11 Then
Me.PrtPreviewBalanceDueByLocWDetail.Visible = True
Else
Me.PrtPreviewBalanceDueByLocWDetail.Visible = False
End If

In essence, there are 17 DLookUp's. I'm not sure if using a loop is better than the If-Then or something else.

Please share your thoughts.

Thanks,

Chuck

 

[Deleted member 147267]

If you have a user access level type of variable loaded (e.g. as a TempVar or in a form control) and can use the control Tag property for the buttons, you can loop through the controls collection of a form, check only command buttons, and then check if the control tag property contains a key word (e.g. "Admin") then you can set the visibility in one pass. You could also use one DLookup to get the user permission level from a table and then check the tags. There are probably other decent ways to do this, but I agree, no more than one lookup should be required, and that would get the user permissions level from a table. I have also determined user permissions by creating a dbUser object, which defined several user properties, thus I only needed to use dbUser.Level. Regardless, you'll probably end up using a loop over the form controls.

 

[MajP]

This should not be slow the way you have it, so there may be other issues. There is a lot of wasted code there.

Slightly faster would be

Me.PrtPreviewBalanceDueSummary.Visible = (dcount("*", "[Permissions Query]", "[ControlAuto] =  10") > 0)
Me.PrtPreviewBalanceDueByLocWDetail.Visible = (dcount("*", "[Permissions Query]", "[ControlAuto] =  11") > 0)

 

[The_Doc_Man]

If there are either groups or categories that you can use, it might be so simple as to do a single lookup of a user "role" from a user table based on the user ID, then under each control button, do one or two tests against the user role. AND that role won't change in mid-session, so heck, you could look it up either in the Form_Open code (or Report_Open code), or you could even look it up in a dispatcher/switchboard type of form that is your default opening form. Then just store the user role in a public declaration in a general module. It will stay there for the life of the session. One DLookup and done.

To be honest, there are a dozen ways to skin this particular cat.

 

[gemma-the-husky]

One way is to hold the permission flags as bits in a byte.

then you can see if bits are set by "anding" the bits, so you get 8 bits for 1 byte, giving you 17 bits with only 3 reads.

ie given an (unsigned) byte with bits 01001110, you can test bit 4 with (bytevalue AND 2^4). (AND becomes a logical Boolean operator)

I m sure you could treat a 4 character string as 4 bytes, thus getting 32 bits with a single read, and a bit of math.

You could unwrap 32 bits into 32 flags for display purposes, and then wrap them up again for storage.

I am not sure how the bits line up - whether they are 0 to 7, or 1 to 8 offhand, or even 7 to 0, or 8 to 1. Also, I am not sure whether a byte value of 00000000 would mess the storage system up, but I am sure it would be doable with a bit of research.


you are aiming to get this sort of thing in the end

controlflag1 = byte1 AND 2^1 = 1
controlflag2 = byte1 AND 2^2 = 1
….
controlflag8 = byte1 AND 2^8 = 1
controlflag9 = byte2 AND 2^1 = 1
controlflag10 = byte2 AND 2^2 = 1
etc

 

[Solo712]

One way is to hold the permission flags as bits in a byte.

then you can see if bits are set by "anding" the bits, so you get 8 bits for 1 byte, giving you 17 bits with only 3 reads.

ie given an (unsigned) byte with bits 01001110, you can test bit 4 with (bytevalue AND 2^4). (AND becomes a logical Boolean operator)

I m sure you could treat a 4 character string as 4 bytes, thus getting 32 bits with a single read, and a bit of math.

You could unwrap 32 bits into 32 flags for display purposes, and then wrap them up again for storage.

I am not sure how the bits line up - whether they are 0 to 7, or 1 to 8 offhand, or even 7 to 0, or 8 to 1. Also, I am not sure whether a byte value of 00000000 would mess the storage system up, but I am sure it would be doable with a bit of research.


you are aiming to get this sort of thing in the end

controlflag1 = byte1 AND 2^1 = 1
controlflag2 = byte1 AND 2^2 = 1
….
controlflag8 = byte1 AND 2^8 = 1
controlflag9 = byte2 AND 2^1 = 1
controlflag10 = byte2 AND 2^2 = 1
etc

Yeah, the OP code is strange in that it controls 17 switches by a value of single variable (or field). So where is the [ControlAuto] value set ? It cannot carry both the value of 10 and 11 on a single pass through the sub. That means that the routine is called 17 times to pick a single value each time which loses a lot of cycles. So, I like the idea of creating a tempvar or a parameter field which stores the buttons' visibility in bits as Dave suggests.

You can store the visibility settings of buttons in a single variable and set them as true/false in the following public function:

Public Function SetButtonVisibility(ByRef Buttons As Long, seq As Integer, YN As Boolean)
   If YN = True Then
      Buttons = Buttons Or 2 ^ (seq - 1)
   Else
      Buttons = Buttons And Not (&HFFFFFFFF And 2 ^ (seq - 1))
   End If
End Function

Set the individual button's visibility on and off by calling

SetButtonVisibility controlflag, buttonNo, True (or False)

where buttonNo is a value of 1 to 17. Once this has been done you can set all the buttons in a single pass through the routine by examining each, i.e.

If controlflag And 2^(buttonNo - 1) = 1 Then 
           Suchandsuchbutton.Visible = True

Unfortunately, you cannot create an effective loop without assigning the property to numbered buttons but still, this should be much more effective handling of the chore because you save 16^2 duplicate reads !!!

Best,
Jiri

 

[gemma-the-husky]

@solo

the OP using 17 different lookup fields, and is looking for an alternative to doing 17 dlookups.

 

[isladogs]

Personally I would use the Tag property as has already been suggested.
For example, see my example app: http://www.mendipdatasystems.co.uk/set-controls/4594398114

Then it just needs one line of code.
e.g. if the 17 controls are assigned tag value A then to hide all of them you just use:

ShowControls False, "A"

 

[Solo712]

@solo

the OP using 17 different lookup fields, and is looking for an alternative to doing 17 dlookups.

I have read the OP and consider the following the gist of the matter:

The purpose of the code is to make visible or invisible 17 different Command buttons on the form. The reason for this has to giving certain users the ability to run certain reports and other users they can't.

As I pointed out most likely the reason (or the main reason) the processing is slow is that you have to vary the [ControlAuto] variable outside the sub and engage the dlookup test code seventeen times, making sixteen unnecessary tests each time. Ok? Is that not clear?

Best,
Jiri

 

[isladogs]

Isn't using the tag property significantly easier?
If you have two or more conditions affecting different groups of controls then set some controls with tag = A & others tag = B (or whatever values you like)

ShowControls False, "A", "B"

If condition1 Then 
    ShowControls True, "A"
ElseIf condition2 Then 
    ShowControls True, "B"
ElseIf condition3 Then
    ShowControls True, "A", "B"
End If

etc ....

 

[Solo712]

Isn't using the tag property significantly easier?
If you have two or more conditions affecting different groups of controls then set some controls with tag = A & others tag = B (or whatever values you like)

ShowControls False, "A", "B"

If condition1 Then 
    ShowControls True, "A"
ElseIf condition2 Then 
    ShowControls True, "B"
ElseIf condition3 Then
    ShowControls True, "A", "B"
End If

etc ....

Colin,
I am not familiar with an Access function "ShowControls". Or is that something you have created, and if so, would you like to show us what it is before arguing its merits? Thx.

Best,
Jiri

 

[isladogs]

Colin,
I am not familiar with an Access function "ShowControls". Or is that something you have created, and if so, would you like to show us what it is before arguing its merits? Thx.

Best,
Jiri

See the link in post #8. This contains an example app including a module with 3 functions ShowControls, EnableControls and LockControls each of which controls the state of tagged controls. See also this thread in sample https://www.access-programmers.co.uk/forums/showthread.php?t=293439

Here is the code

Option Compare Database
Option Explicit

Global ctrl As Control

Public Sub ShowControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to visible or not according to the control tag value
    
    For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acPageBreak
            'no code here - these can't be locked
        
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Visible = State
        
        End Select
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in ShowControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

Public Sub LockControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acCommandButton, acTabCtl, acPage, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be locked
            
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Locked = State
                    
        End Select
            
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in LockControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub
Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be disabled
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Enabled = State
        End Select
        
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in EnableControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

 

[deletedT]

Isn't using the tag property significantly easier?
If you have two or more conditions affecting different groups of controls then set some controls with tag = A & others tag = B (or whatever values you like)

ShowControls False, "A", "B"

If condition1 Then 
    ShowControls True, "A"
ElseIf condition2 Then 
    ShowControls True, "B"
ElseIf condition3 Then
    ShowControls True, "A", "B"
 End If

etc ....

Colin, I've seen this sample database you've shared, downloaded and tested it. But never had checked the code behind deeply.
While I'm downloading and testing it again, can you explain how it will help in this situation?

I have 5 buttons.
Buttons 1,2 should be visible for user 1
Buttons 1,4,5 should be visible for user 2
Buttons 1,3,4 should be visible for user 3
Buttons 2,3,5 should be visible for user 4


How the tags can control this?
thanks.


Edit:
At present I have a UserLevel table. and with several Dlookup(actually Elookup) Enable/Disable or Visble/Hide them.

 

[isladogs]

Hi Tera
For that particular example it won't reduce the code significantly.
Its intended use is for groups of controls all to be treated the same way.

However you could tag the buttons A B C D E then use Select Case based on users. Set all buttons hidden by default.

Select Case User

Case 1
ShowControls True, "A", "B"

Case 2
ShowControls True "A", "D", "E"

Etc

However its of marginal benefit only in this example

 

[The_Doc_Man]

The last time I did something like this, I did one-time lookups (NOT DLookups, but rather opened a recordset & did a .FindFirst on the username), then read out the individual flags. Bit-twiddling is even more of a time-waster than just tracking 17 Y/N flags even though that was SLIGHTLY denormalized. I eventually made a sparse child table that listed the privileges the user DID have and set the appropriate flags in a set of Public variables in a general module (and the routine that read this always reset the flags before reading the list.) The trick isn't doing 17 DLookups or writing a loop for 17 flags. It is that the buttons didn't do the lookups - the Form_Load code on my switchboard did the lookups ONCE for the lifetime of the session.

Beyond that point, everything else was just referring to static variables that were Public and in-scope to ALL forms, so speed was NEVER an issue by that point no matter HOW many forms were in use - and I had at least 20 different forms and probably a total of over 160 possible buttons divided unevenly among those forms.

Whether you use Colin's method or something like I described or some other solution, the point is that you don't want to do DLookups under the buttons. You do them ONCE when the user starts the session and then leave behind the results to be shared by all forms that potentially need them.

 

[Deleted member 147267]

I have 5 buttons.
Buttons 1,2 should be visible for user 1
Buttons 1,4,5 should be visible for user 2
Buttons 1,3,4 should be visible for user 3
Buttons 2,3,5 should be visible for user 4

How the tags can control this?

Was this not adressesd in post 2, the 1st answer you got?

 

[Solo712]

See the link in post #8. This contains an example app including a module with 3 functions ShowControls, EnableControls and LockControls each of which controls the state of tagged controls. See also this thread in sample https://www.access-programmers.co.uk/forums/showthread.php?t=293439

Here is the code

Option Compare Database
Option Explicit

Global ctrl As Control

Public Sub ShowControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to visible or not according to the control tag value
    
    For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acPageBreak
            'no code here - these can't be locked
        
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Visible = State
        
        End Select
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in ShowControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

Public Sub LockControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acCommandButton, acTabCtl, acPage, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be locked
            
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Locked = State
                    
        End Select
            
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in LockControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub
Public Sub EnableControls(State As Boolean, Tg1 As String, Optional Tg2 As String, Optional Tg3 As String, _
        Optional Tg4 As String, Optional Tg5 As String, Optional Tg6 As String)

On Error GoTo Err_Handler

    'set controls to locked or not according to the control tag value
     For Each ctrl In Screen.ActiveForm.Controls
        Select Case ctrl.ControlType
        
        Case acLabel, acImage, acLine, acRectangle, acPageBreak
            'no code here - these can't be disabled
        Case Else
            If ctrl.Tag = Tg1 Or ctrl.Tag = Tg2 Or ctrl.Tag = Tg3 Or ctrl.Tag = Tg4 _
                    Or ctrl.Tag = Tg5 Or ctrl.Tag = Tg6 Then ctrl.Enabled = State
        End Select
        
    Next ctrl
  
Exit_Handler:
    Exit Sub

Err_Handler:
    MsgBox "Error " & Err.Number & " in EnableControls procedure: " & Err.Description
    Resume Exit_Handler
    
End Sub

Colin,
I have no idea what you are doing. You are being asked to set the visibility of command buttons based on user's access level and do it in the most efficient way possible. So, in my understanding you need some control mechanism whereby you set the visibility of the buttons for a particular user of the form. So, you can tag the buttons and then step through the tags based on the business rules but the real differentiator is how you translate the user's access levels into the combinations of visible and hidden report buttons. You are not offering any solution there. In your scenario, you supply a new tag to all the buttons each time a user opens the form but you are not showing the step(s) that determine the value of the tag - and that is the real focus here. You have to have some kind of formula to set the visibility of the individual buttons. In that process, assigning the result to the button's tag is unnecessary extra step and slows down processing.

The solution I have suggested is much simpler: Keep the combination of accessible buttons for a user's access level in the "ControlAuto" variable by setting / clearing its bits. (You can use a wrapper function if that appears too difficult). You would keep it with a user profile, retrieve it when opening the form and then test all the buttons visibility against it. One button = one test. I doubt there is a simpler solution but I am open to suggestions.

Best,
Jiri

 

[isladogs]

Jiri
Similarly I don't understand what you are suggesting.

I think your understanding of the user's requirements is different to mine.

You are also misinterpreting how the tag property would be used.
It is set once in the property sheet, not each time the form is opened.
Micron had already made the same suggestion in post #2.
I just gave an example of it in use though that example wasn't based on user levels.
However, its a simple approach and it would work.
There may be a better approach. If so, fine by me.
Perhaps you would like to give an example of your approach as you think yours is simpler.

Otherwise, unless the OP returns to comment on the ideas suggested, I think there's little point discussing the merits or otherwise of each approach any further.

 

[Solo712]

J
You are also misinterpreting how the tag property would be used.
It is set once in the property sheet, not each time the form is opened.
Micron had already made the same suggestion in post #2.
I just gave an example of it in use though that example wasn't based on user levels.
However, its a simple approach and it would work.

You admit that your example "is not based on user levels". But the problem is that the user wants just that:

. The purpose of the code is to make visible or invisible 17 different Command buttons on the form. The reason for this has to giving certain users the ability to run certain reports and other users they can't.

So, let's get to it

Example:
user 1 - can access reports no.: 1-3-7-10-12-16
user 2 - 2-5-6-9-13-15-16
user 3 - 1-3-7-8-10-12-14-17
user 4 - 4-5-15
user 5 - 3-7-10-12-16-17
user 6 - 2-4-5-9-11-12-14

Perhaps you would like to give an example of your approach as you think yours is simpler.

Sure, no problem....
In my plan, as I told you, the corresponding bits are turned on in the controlflag in the user profile table. According to the access plan above the bit values of the flag would be (the digits in the argument string are the ordinals of the reports that are accessible to the user):

user 1 controlflag = GetFlag("1;3;7;10;12;16")
user 2 controlflag = GetFlag("2;5;6;9;13;15;16")
user 3 controlflag = GetFlag("1;3;7;10;12;14;17")
user 4 controlflag = GetFlag("4;5;15")
user 5 controlflag = GetFlag("3;7;10;12;16;17")
user 6 controlflag = GetFlag("2;4;5;9;11;12;14")

You set the control flag in a user table and it will not change unless the user's level or the plan changes:

Public Function GetFlag(Buttons As String) As Long
  Dim Btn As String, i As Integer, fflag As Long
  
  Btn = Buttons
  
  Do While Len(Btn) > 0
     i = Val(Btn)
     fflag = fflag Or SetButton(fflag, i, True)
     i = InStr(Btn, ";")
     If i = 0 Then Exit Do
     Btn = Mid(Btn, i + 1, Len(Btn))
  Loop
  
  GetFlag = fflag
  
End Function
'-----------------------------------
Public Function SetButton(ByRef Buttons As Long, seq As Integer, YN As Boolean) As Long
   If YN = True Then
      SetButton = Buttons Or 2 ^ (seq - 1)
   Else
      SetButton = Buttons And Not (&HFFFFFFFF And 2 ^ (seq - 1))
   End If
End Function

'------------------------------------------------------------------------------

In the form where the buttons are to be set, you retrieve the controlflag
and then you use its value to test the individual buttons:

Dim ControlAuto as Long
ControlAuto = Nz(DLookup("controlflag", "User", "ID = " & Me!ID))
'
If ControlAuto And 2^0 <> 0 Then 
Me.PrtPreviewBalanceDueSummary.Visible = True
Else
Me.PrtPreviewBalanceDueSummary.Visible = False
End If

If ControlAuto And 2^1 <> 0 Then 
Me.PrtPreviewBalanceDueByLocWDetail.Visible = True
Else
Me.PrtPreviewBalanceDueByLocWDetail.Visible = False
End If

If ControlAuto And 2^2 <> 0 Then 
Me.PrtPreviewThisOrThat.Visible = True
Else
Me.PrtPreviewThisOrThat.Visible = False
End If
' etc..................

You have to set the visibility of all the buttons; optionally - if you are not saving the design - you can preset the visibility in the property and turn the print button on (or off) based on this plan saving all the ELSEs. That's all there is to it.

So, now, kindly let me see your solution for this particular task, and I mean the whole solution...not just saying that "this works except not with user access permissions". I am really dying to see how you are going to manage the myriad of possible permutations with tagging the buttons. Really, Colin.

Otherwise, unless the OP returns to comment on the ideas suggested, I think there's little point discussing the merits or otherwise of each approach any further.

Seems to me you want to run from this, Colin. You really don't have a solution to this problem do you? If you do, show it, please !

Best,
Jiri

 

[Mark_]

@ Chuck,

As the original poster, can you please provide a specification for what you need? It will probably fall into one of three categories;
1) Each person belongs to one (or more) groups. No controls are active unless one of the groups the user belongs to has permissions.
2) Each person has individually designated permissions for each control. You cannot see the control unless you have permissions to see it.
3) All users can see all controls UNLESS they are restricted from seeing one (or more).

Depending on the approach you can either default all controls to .visible = True OR .visible = False and then only disable/enable those that would be different.