Hi Nick!
You will need some way for the user to log in so the system knows who it is. For now I will assume that you have security and can use CurrentUser.
Create a table with the fields fldUserId (which will be filled automatically using CurrentUser), fldControl1, fldControl2 etc. Of course you will want to find a way to identify each field for example:
for a name field you would use fldUser in the table, chkName on the option form and txtName on the form they need to access. All of the fields will be y/n fields.
Create a series of check boxes on the option form for each control the user may want to see. Create a button labeled Save Options and in the click event of this button you can use the following code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)
rst.AddNew
rst!fldUser = CurrentUser
rst!fldControl1 = chkControl1
rst!fldControl2 = chkControl2
etc.
rst.Update
Set rst = Nothing
Of course you will need to adjust this to look for existing records if you want to all the users to change their options.
In the open event of the form that the user needs access to you can put this code:
Dim rst As DAO.Recordset
Dim strCriteria As String
Dim strControlName As String
Dim fld As Field
strCriteria = "Select fldControl1, fldControl2, etc. From YourTable Where fldUser = '" & CurrentUser & "'"
Set rst = CurrentDb.OpenRecordset(strCriteria, dbOpenDynaset)
For Each fld In rst
strControlName = "txt" & Right(fld.Name, 4)
Me.Controls(strControlName).Visible = fld
Next fld
set rst = Nothing
Of course it looks like I have assumed that every control will be a text box but I would recommend that you name each control with the same first three letters no matter what they are. If you don't like txt then use ctr for control or something similar.
hth
Jeff Bridgham
bridgham@purdue.edu
