Access Security

[dbprogman]

Hi Everyone

I have a database that has a main form (frmMaster) on this form is a range of buttons (btn1, btn2 etc)

I also have two (2) tables (tblUser) and (tblControls) related on a one to one relationship. The tblUser contains a department field and a password field and tblControl has the buttons from frmMaster as yes/no

I have created a second form that contains the fields from each of the above tables allowing a user to choose a department and tick which buttons will be active for that department.

I have also created an initial login form that allows a user to choose his/her department and enter the assigned password.

Finally, now my problem (if you have already picked up on it) how do I or what would be the correct code structure to get form frmMaster to open enabling those buttons as stipulated in the control table.

Scenerio Example:

Manager logs on goes to main form choose security subform and then chooses department sales, then checks the controls he/she wants the sales department to have access to. Then a user from sales logs on with his/her credentials and opens the main form and when he/she clicks on a button deemed inactive by the manager will recieve a msgbox 'Access Denied'.

I sincerely appreciate your feedback and I appologise for any headaches caused. Nothing like a challenge, hey?

 

[MarkK]

One-to-one means that for each user there in one record in the control table?? How is this useful? It seems to me in this case you don't need two tables.
To describe the state of a series of controls for a series of departments I'd expect to see a tDepartment table as well. That table would have a one-to-many relationship with the tControlState table. Department would also have a one-to-many relationship with tUser since a department is likely to encompass multiple users. Then when a user authenticates you determine the department, from which you determine the control states, and then apply them to the form.

[B]tDepartment[/B]
DepartmentID (PK)
DepartmentName

[B]tUser[/B]
UserID (PK)
DepartmentID (FK)
User
Pass

[B]tControlState[/B]
ControlStateID (PK)
DepartmentID (FK)
ControlName
IsEnabled

And that's the bare minimum. In the real world I'd expect you'll want to grant access to some users for multiple departments, which is harder.
But is this useful? Did I understand your problem correctly?
Cheers,

 

[dbprogman]

The database is not reliant on a user only departments each department has a defined set of rules as to which controls are enabled on a main form. Hence the one to one relationship. These rules are then stipulated on another form, as per scenerio

 

[dbprogman]

I should add that the user logon credetials would be the same for everyone in that department as set by the manager

 

[MarkK]

What is the mechanism by which a specific rule is matched to a specific control? Notice that my tControlState table has a ControlName and the state definition IsEnabled and that there might be many of these for a single department.
It seems you need an analagous structure; a list of rules for the department/user and traverse that list, setting the behaviour of each matching control.?.?
Does that sound right? Are we still on the same page?

 

[dbprogman]

The way I thought it may work is that I have two tables or one doesnt matter but for each record in that table (which will only be five(5) the control enable property is defined and the password for that department is set. Then on the main logon screen a user chooses the department enters the password and enters database. When the main form opens the controls get their enable property based on that table re department entered.

Logonform:
if department = department sales and password = set password then
mainform open
Mainform:
When form loads
if department = department sales then
toggle1.enabled=false
etc

Later today I will endevour to use your example and attempt to apply it

I guess the main question at this point is how do I get a form to set its control property based on a table?

Thanx for your patience...
Cheers

 

[dbprogman]

You %&^%* Beauty!!!

It maybe a long winded way but I solved the problem, the code is below and thanks to lagbolt for your assistance.

Private Sub cmdLogin_Click()
If IsNull(txtPass) Then
MsgBox "You MUST enter a Password"
Me.txtPass.SetFocus
Else
If IsNull(cboDept) Then
MsgBox "you MUST choose a Department"
Me.cboDept.SetFocus
Else
If Me.txtPass = DLookup("[Password]", "tblLogin", "[Department]=" & "cboDept") And Me.cboDept = Me.txtDept Then
Me.Toggle5.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle6.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle7.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle8.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle9.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle10.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle11.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle80.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle40.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle41.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")
Me.Toggle42.Enabled = DLookup("[Name of control]", "tblLogin", "[Department]=" & "cboDept")

MsgBox " You have successfully logged into" & " " & Me.txtDept
Me.cboDept = ""
Me.txtPass = ""
Else
Me.Toggle5.Enabled = False
Me.Toggle6.Enabled = False
Me.Toggle7.Enabled = False
Me.Toggle8.Enabled = False
Me.Toggle9.Enabled = False
Me.Toggle10.Enabled = False
Me.Toggle11.Enabled = False
Me.Toggle80.Enabled = False
Me.Toggle40.Enabled = False
Me.Toggle41.Enabled = False
Me.Toggle42.Enabled = False
MsgBox "You have entered the wrong credentials, please try again", vbCritical
Me.cboDept = ""
Me.txtPass = ""

End If
End If
End If
End Sub

This was based on a single table where the Name of Control was set to the caption with a yes/no type. Then a subform was created to enable an administrator to check those controls that could be enabled etc.