Authorizing Access to controls on a form 2

[puforee]

I have a form that needs authorization for certain of its controls. When the form opens a text control contains the users ID of the person who opened the DB, (we will call it UserID). TekTips gave me this code-thanks. I use a simple VBA code to check for specific User ID's, if they match the restricted controls become active. This check code is stored on the On Current event of the Form. This is fine if I want to change the VBA code every time a user is added or removed.

So, I built a table to store the authorized userID's The table is called Access_control. The ID's are stored in a field called "User". This way the list of user's can be administered by one of the DB users. Changes to this table are password protected and only 1 or 2 people have the password.

So, I need VBA code that runs when the form is opened should compare the forms UsetID field to the Access_control table, User field to see it it matches one of the table entries. If it matches, the restricted controls are enabled.

I am using MS Office 2013 and this DB is to be used at work.

OR...is there a better way to control access to the restricted controls?

I anxiously look forward to your replies...please include sample code.

 

[Andrzejek]

I don't know how you know which controls to allow access to for your Admin users, but I would use Tag property to mark which controls are available to Admin and not to 'regular' user. So if I would set the Tag property of certain controls to, let's say: "ADMIN_CNTR", and you already have UserId of whoever is accessing your app, I could use code like this:

Dim blnIsAdminUser As Boolean
Dim cntr As Control

blnIsAdminUser = Len(DLookup("Access_control", "User", "User = " & UserId))

For Each cntr In Me.Controls
    If cntr.Tag = "ADMIN_CNTR" Then
        cntr.Enabled = blnIsAdminUser
    End If
Next cntr

---- Andy

There is a great need for a sarcasm font.

 

[puforee]

Andy,
I have heard of Tags before but never used them. This sounds good. Question: Can you break down the statememt blnIsAdminUser = Len(DLookup("Access_control", "User", "User = " & UserId)) so I can understand why it needs Len and "& UserID. The table that stores the User will be exactly like the control on the form that shows UserID.

I would appreciate it. Thanks,

 

[strongm]

It is a programming trick to turn the result of the lookup into a Boolean value - True if the lookup finds a match, False if it does not.

 

[Andrzejek]

OK, breaking down the statement:
[tt]blnIsAdminUser = Len(DLookup("Access_control", "User", "User = " & UserId))
[/tt]
[tt]"Access_control" [/tt]is the table you use for DLookup
[tt]"User" [/tt]is the field in this table
[tt]"User = " & UserId[/tt] is the 'where' part of your lookup, so if you login into your application and UsedId is puforee, your DLookup will look for record where [tt] User = puforee[/tt]

Now that I look at this statement, If UserId is Text - it should probably be:
[tt]blnIsAdminUser = Len(DLookup("Access_control", "User", "User = [highlight #FCE94F]'[/highlight]" & UserId * [highlight #FCE94F]"'"[/highlight]))
[/tt]so you get [tt] User = 'puforee'[/tt] (unless your UserID is numeric)

That DLoopkup will return something - if found, and nothing if nothing is found.
The Len() of this 'something' will be either 0 of >0

It is little known fact that Boolean is False = 0, and True when any other value than 0.
So whatever your DLookup will return, you can easily turn it into a Boolean value (of False or True) and use it to Enabled some controls with certain Tag.

The 'long' version of this one statement would be:
[tt]
ReturnValue = DLookup("Access_control", "User", "User = " & UserId)
intSomeInteger = Len(ReturnValue)
blnIsAdminUser = intSomeInteger
[/tt]

"I have heard of Tags before but never used them."
Try them, you may like it....

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I think this is a bit off:

blnIsAdminUser = Len(DLookup("Access_control", "User", "User = " & UserId))

"Access_control" is the table you use for DLookup
"User" is the field in this table
"User = " & UserId is the 'where' part of your lookup, so if you login into your application and UsedId is puforee, your DLookup will look for record where User = puforee

With all Domain functions, the "table" or query is the second argument. The first is the field or expression to return. The optional third argument applies the filter. There is a fairly good reference at faq705-4978

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

No argument here, it could be "a bit off". I don't use DLookup's, I write my own look-ups. But the idea should be correct

Thanks Duane


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

OK I have given this a try and I am getting a type mismatch. Also, my apologizes but I had some names wrong in the initial post.

Table Name - "MetUsers" Field Name in table "UserID".
On Form non-bound text field for comparison - "User" (this field A text box and is not viable and returns the current user with the =fOSUserName() code)
On the Form the button control "MET" is not visible and will become visible if table field "UserID" and text box "User" match.

Since both the table field and Form control are text I used this code that Andy supplied above.

Now that I look at this statement, If UserId is Text - it should probably be:
blnIsAdminUser = Len(DLookup("Access_control", "User", "User = '" & UserId * "'"))
so you get User = 'puforee' (unless your UserID is numeric)

So, substituting the correct names I came up with this:

blnIsAdminUser = Len(DLookup("MetUsers", "UserID", "UserID = '" & User * "'"))

When the form opens the on current event runs the code and I get the type mismatch.

Can you show me where I am off please.

Also for the If statement I changed to this (make visible instead of enable.

   For Each cntr In Me.Controls
    If cntr.Tag = "ADMIN_CNTR" Then
        cntr blIsAdminUser.Visible = True
    End If
   Next cntr

Will this work assuming I can get this far in the code.

Thanks for all of your help

 

[Andrzejek]

If you read Duane's note, he pointed out that my code was flipped. So the line:
[tt]
blnIsAdminUser = Len(DLookup([highlight #FCE94F]"MetUsers"[/highlight], [highlight #8AE234]"UserID"[/highlight], "UserID = '" & User * "'"))
[/tt]
should be
[tt]
blnIsAdminUser = Len(DLookup([highlight #8AE234]"UserID"[/highlight], [highlight #FCE94F]"MetUsers"[/highlight], "UserID = '" & User [highlight #FCAF3E]&[/highlight] "'"))
[/tt]
Edit:
O yeah, I've missed [highlight #FCAF3E]that[/highlight], Thanks Duanne

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

The "[highlight #FCE94F]*[/highlight]" after User should be "[highlight #FCE94F]&[/highlight]"

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Andy, I installed your suggestion and I still get a type match error when I run the code. Here is the complete code as it stands now. Remember UserID and User are both text fields. One is from the table and the other is stored in the Form text box. I run this code for the form using the ON CURRENT event Could this be causing a problem?

Also is the code below the bad part look OK. Instead of enabling the control/s I am making it visible.

   Dim blnIsAdminUser As Boolean
   Dim cntr As Control

   [highlight #EDD400][b]blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User * "'"))[/b][/highlight]
   
   For Each cntr In Me.Controls
  [indent][/indent][b][highlight #EDD400]If cntr.Tag = "ADMIN_CNTR" Then
        cntr blIsAdminUser.Visible = True[/b][/highlight]
    End If
   Next cntr

 

[Andrzejek]

Fix [blue]this[/blue], try again.

[pre]
Dim blnIsAdminUser As Boolean
Dim cntr As Control

blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & [blue]User & "'"[/blue]))

For Each cntr In Me.Controls
If cntr.Tag = "ADMIN_CNTR" Then
[blue]cntr.Visible = blnIsAdminUser[/blue]
End If
Next cntr
[/pre]
Step thru your code line by line and - if you have an error - let us know which line of code creates what error.



---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

There might be an issue with attempting to find the length of a null value.
Are only admins in the MetUsers table? You might want to try:

blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'") & "")

The test with Len() wouldn't be my first choice since it is a bit obscure. I would probably use DCount()

blnIsAdminUser = (DCount("UserID", "MetUsers", "UserID = '" & User & "'") > 0)

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

One of the reason puforee has all those problems - he or she keeps inventing code that was not mentioned anywhere above:
[tt]
... "UserID = '" & Use[highlight #FCE94F]r * "[/highlight]'"))
...
cntr [highlight #FCE94F]blIsAdminUser[/highlight].Visible = True
[/tt]
And even after pointing this out, all of it is still there... :-(


---- Andy

There is a great need for a sarcasm font.

 

[puforee]

I hear you Andy and I did get the code to run, sort of. At the time I was typing your code in and my old eyes deceived me. But I finally got it in correctly I believe. I am a "he" by the way.
So, on-words. As I said, I ran the code and got a null response. I am using my own UserID of course.

Dim blnIsAdminUser As Boolean
   Dim cntr As Control
   
   MsgBox User
   blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'"))
   MsgBox "the variable is" +binIsAdminUser
   For Each cntr In Me.Controls
    If cntr.Tag = "ADMIN_CNTR" Then
        cntr.Visible = blIsAdminUser
    End If
   Next cntr

As you see I used MsgBox's to display information at key points. The first box told me my User field on the form showed correctly. The second MsgBox show
blank.
What values should I expect blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'")) to return.

By the way, I then tried Duan's code and got the same results.

I appreciate you guys sticking with me.

Thanks,
John

 

[dhookom]

John,

You need to:
[ul]
[li]add "Option Explicit" at the top of every module [highlight #FCE94F](hint you have one variable with three different spellings)[/highlight][/li]
[li]Compile your code after every change.[/li]
[li]Don't use + for combining strings unless you really understand the difference between & and +[/li]
[/ul]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

OK, Dummy me. Exactly where at the top of the module does the "Option Explicit" go? See total code below.

I corrected the code and it works. Here is the complete code (I will comment out the MsgBox's later).

Form Current

Option Compare Database

Private Sub Form_Close()
    DoCmd.Restore
End Sub

Private Sub Form_Current()

    DoCmd.Maximize

   Dim blnIsAdminUser As Boolean
   Dim cntr As Control

   
   MsgBox User
    blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'"))
   MsgBox "The variable is" & " " & blnIsAdminUser
   For Each cntr In Me.Controls
    If cntr.Tag = "ADMIN_CNTR" Then
        cntr.Visible = blnIsAdminUser
    End If
   Next cntr
      
End Sub

Code is run for the On Current Event of a Form.

 

[dhookom]

In the General Declarations:

Option Compare Database
Option Explicit  [COLOR=#4E9A06]' this will make sure all variables are declared[/color]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

[tt]Option Explicit[/tt] - you should have it for all code in every Form, Module, etc. Either the very first line, or second (like Duanne showed you).

For all new development, you want to go to Tools - Options - Editor tab, and check "Require Variable Declaration" check box.

" I am a "he" by the way" - didn't want to insult anybody by assuming the gender...

"At the time I was typing your code ..." - it is better to Copy / Paste instead of typing. That's why we do try to use your naming information in our responses (if we can)

"I corrected the code and it works" - that's where Copy / Paste would save you time and effort.


---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

BTW - good job on using [tt]MsgBox[/tt]

You can also put some break points in your code, and when stopped - hover over the variable to see its value.

---- Andy

There is a great need for a sarcasm font.