×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Authorizing Access to controls on a form
2

Authorizing Access to controls on a form

Authorizing Access to controls on a form

(OP)
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.

RE: Authorizing Access to controls on a form

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:

CODE

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.

RE: Authorizing Access to controls on a form

(OP)
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,

RE: Authorizing Access to controls on a form

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.

RE: Authorizing Access to controls on a form

OK, breaking down the statement:
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

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)

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:

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


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

---- Andy

There is a great need for a sarcasm font.

RE: Authorizing Access to controls on a form

I think this is a bit off:

Quote (Andy)

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: DLookup Function Examples

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

RE: Authorizing Access to controls on a form

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 smile

Thanks Duane thumbsup2


---- Andy

There is a great need for a sarcasm font.

RE: Authorizing Access to controls on a form

(OP)
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.

CODE -->

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:

CODE -->

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.

CODE -->

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

RE: Authorizing Access to controls on a form

If you read Duane's note, he pointed out that my code was flipped. So the line:

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

should be

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

Edit:
O yeah, I've missed that, Thanks Duanne

---- Andy

There is a great need for a sarcasm font.

RE: Authorizing Access to controls on a form

The "*" after User should be "&"

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

RE: Authorizing Access to controls on a form

(OP)
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.

CODE -->

Dim blnIsAdminUser As Boolean
   Dim cntr As Control

   blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User * "'"))
   
   For Each cntr In Me.Controls
  
If cntr.Tag = "ADMIN_CNTR" Then cntr blIsAdminUser.Visible = True End If Next cntr

RE: Authorizing Access to controls on a form

Fix this, try again.

Dim blnIsAdminUser As Boolean
Dim cntr As Control

blnIsAdminUser = Len(DLookup("UserID", "MetUsers", "UserID = '" & User & "'"))
   
For Each cntr In Me.Controls
   If cntr.Tag = "ADMIN_CNTR" Then
      cntr.Visible = blnIsAdminUser
   End If
Next cntr 
 
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.

RE: Authorizing Access to controls on a form

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:

CODE --> vba

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()

CODE --> vba

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

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

RE: Authorizing Access to controls on a form

One of the reason puforee has all those problems - he or she keeps inventing code that was not mentioned anywhere above: ponder

... "UserID = '" & User * "'"))
...
cntr blIsAdminUser.Visible = True

And even after pointing this out, all of it is still there... sad


---- Andy

There is a great need for a sarcasm font.

RE: Authorizing Access to controls on a form

(OP)
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.

CODE -->

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

RE: Authorizing Access to controls on a form

John,

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

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

RE: Authorizing Access to controls on a form

(OP)
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

CODE -->

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.

RE: Authorizing Access to controls on a form

In the General Declarations:

CODE --> vba

Option Compare Database
Option Explicit  ' this will make sure all variables are declared 

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

RE: Authorizing Access to controls on a form

Option Explicit - 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... smile

"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. smile


---- Andy

There is a great need for a sarcasm font.

RE: Authorizing Access to controls on a form

BTW - good job on using MsgBox thumbsup2

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.

RE: Authorizing Access to controls on a form

(OP)
OK, I checked Tools - Options - Editor Tab and the Require Variable Declaration check box.

Again, please be patient, Where to I type Option Explicit? Duane said at the top of the page but the code page does not like it? Or did the check box do it? Also, in short, what does it do?

Thanks for all your help,

John

RE: Authorizing Access to controls on a form


Specifying Option Explicit checks the spelling of your memory variables when you compile (which you need to do regularly).

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

RE: Authorizing Access to controls on a form

"the code page does not like it?" - what do you get when you have:

CODE

Option Compare Database
Option Explicit  

Private Sub Form_Close()
    DoCmd.Restore
End Sub

... 

Error messages?

"what does it do?" - requires you to declare all variables that you use in the code.
So you cannot use anything not declared, and also you cannot misspell the names of your variables.


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close