Controlling display of forms

[Bob]

Hi,

I'm trying to control what parts of a form are visible or not depending
on the user of the computer.

I've managed to find and adapt some code to tell me who the user is and
I have a table set up with user names and a permission level (1 or 0)

I can manually alter the On_Load event of my form to hide the parts I
want (tabs)

Can anybody help me out with putting it all together so that the forms
On_Load event looks at GetUserName function which looks at the
UserPermissions table and sets Visible True or False accordingly.

This is taking me so long and it looks like it ought to be fairly simple.

Thanks

Bob

 

[UpRider]

Bob, we can use the GetUserName function like it was the actual name
(assuming it returns the user name).
We can use the name to lookup the permission in the UserPermissions table.
In the ON LOAD event:
dim UPerm as boolean
UPerm = Dlookup("Permissions","UserPermissions","UserName = " & chr$(39) &
GetUserName() & chr$(39))
if UPerm = 1 then uPerm = -1 'true
txtxxxx.visible = UPerm
txtyyyy.visible = UPerm
etc.

HTH, UpRider

 

[Bob]

Thanks for your quick answer.
But I get an error with this line
UPerm = Dlookup("Permissions","UserPermissions","UserName = " & chr$(39) &
The message is Expected: expression

Does the GetUserName function have to be in the Form OnLoad event?
I have it in a seperate module.

Any ideas.

 

[Douglas J. Steele]

UpRider's solution fell victim of line-wrap in posting. The DLookup
statement was supposed to be all on one line.

Here's the statement rewritten to avoid a line-wrap problem:

UPerm = Dlookup("Permissions", _
"UserPermissions",_
"UserName = " & chr$(39) & GetUserName() & chr$(39))

However, that will fail for a user who isn't in the UserPermissions table.
To handle that case, assuming you don't want them to see the protected
stuff, use

UPerm = Nz(Dlookup("Permissions", _
"UserPermissions",_
"UserName = " & chr$(39) & GetUserName() & chr$(39)), 0)

 

[Douglas J. Steele]

Oops, just noticed a typo: the space is missing in front of the 2nd line
continuation character.

That should be

UPerm = Nz(Dlookup("Permissions", _
"UserPermissions", _
"UserName = " & chr$(39) & GetUserName() & chr$(39)), 0)

 

[UpRider]

Well, the function itself couldn't be defined in the on load event , but it
could be in the FORM's module. It would be OK in any code module also.
Some more assumptions are that your table name is "UserPermissions", and
that the fields "Permissions" and "UserName" are in that table. If the names
are wrong, adjust to your names.

UpRider

 

[Bob]

Thankyou both,

Got it working beautifully with this code.
In my experimenting, I put the square brackets around the table fields.
Not sure of the significance of them as the code works with or without them.

Thanks again.

Bob