Startup with Conditional Views per User

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97
I have a multi-user front end mdb.
I would like to control the user's access to tabs, and controls on those
tabs, by setting the visible property accordingly on start-up.
I am looking at a table as a form of matrix where against the user's login I
set a series of Yes/No flags in the table to Show or NotShow.

Is there any way/example available to help me do this as efficiently as
possible?
I guess it may come to a series of Dlookups or Case statements?

Any ideas appreciated.

WSF
 
If you will only ever need to do this on one form, you can have one table,
with primary key = username, and other fields defining the options for that
single form. But if you need to do it on several forms, you need the primary
key to be username + formname, so the same user can have several rows, one
for each of the forms involved.

Whatever, you will end up with a single row, representing the current user's
permissions for the current form. You'll need to get that row in a
recordset, so you can access the many option fields within that row. With
DLookup, you could only access a single option field at a time, and that
would not be efficient.

So for example:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("SELECT * FROM TheTable WHERE UserName=""" &
currentuser() & """")

Now you have the option fields (from TheTable) in rs. So if Field1 was a
boolean field defining whether the user should or should not be able to see
a control named Ctl99 on your form:

Ctl99.visible = rs![Field1]

and so on for the other controls.

You could put that code in the form's Open or Load event. When all the
options have been set, close the code down properly:

set rs = nothong
set db = nothing

BTW, I would seriously consider disabling the non-allowed controls, not
making them invisible. Otherwise, your form may look like a patchwork quilt!
Each user will say, "Gee, he could have designed this form to look neater!"

HTH,
TC
 
Thanks TC
This will do nicely.
WSF

TC said:
If you will only ever need to do this on one form, you can have one table,
with primary key = username, and other fields defining the options for that
single form. But if you need to do it on several forms, you need the primary
key to be username + formname, so the same user can have several rows, one
for each of the forms involved.

Whatever, you will end up with a single row, representing the current user's
permissions for the current form. You'll need to get that row in a
recordset, so you can access the many option fields within that row. With
DLookup, you could only access a single option field at a time, and that
would not be efficient.

So for example:

dim db as database, rs as recordset
set db = currentdb()
set rs = db.openrecordset ("SELECT * FROM TheTable WHERE UserName=""" &
currentuser() & """")

Now you have the option fields (from TheTable) in rs. So if Field1 was a
boolean field defining whether the user should or should not be able to see
a control named Ctl99 on your form:

Ctl99.visible = rs![Field1]

and so on for the other controls.

You could put that code in the form's Open or Load event. When all the
options have been set, close the code down properly:

set rs = nothong
set db = nothing

BTW, I would seriously consider disabling the non-allowed controls, not
making them invisible. Otherwise, your form may look like a patchwork quilt!
Each user will say, "Gee, he could have designed this form to look neater!"

HTH,
TC


WSF said:
Access97
I have a multi-user front end mdb.
I would like to control the user's access to tabs, and controls on those
tabs, by setting the visible property accordingly on start-up.
I am looking at a table as a form of matrix where against the user's
Click to expand...
login
I
set a series of Yes/No flags in the table to Show or NotShow.

Is there any way/example available to help me do this as efficiently as
possible?
I guess it may come to a series of Dlookups or Case statements?

Any ideas appreciated.

WSF
Click to expand...
Click to expand...
 
Back
Top