User security based on table in database

Access 2003 forms
Microsoft Access
Microsoft Access 2003
Microsoft Access forms
I am using Access 2003. I have a small (2 table) database. One table contains unit rework information. The second table contains User information(ID, FirstName, LastName, Security level). I built a form with a place for 3 users to 'sign off' - one for operators, one for supervisors & one for QA. Each of these has a corresponding record in the Security Table. My questions are: 1. How do I get the form that they do data entry on to recognize the correct security level for the user ID entered? 2. How do I require all 3 be complete prior to completing the unit?

Answer Wiki

Thanks. We'll let you know when a new response is added.

For question 1. You could use the User information table to check the security level and make the proper signoff visible when the form opens based on who the user is. Assuming you are using the Access security, this example uses the User() function; otherwise, if you have a custom made login, you would need to have that available to the form. This also assumes that the form is bound to the unit table. Example:

Sub Form_Open()
Dim SecurityLevel
SecurityLevel = Dlookup(“SecurityLevel”,”UserInformation”,”ID = ‘” & User() & “‘”)
Me![OperatorSignoff].visible = False
Me![SupervisorSignoff].visible = False
Me![QASignoff].visible = False
Select Case SecurityLevel
Case “Operator”
Me![OperatorSignoff].visible = True
Case “SupervisorSignoff”
Me![Supervisor].visible = True
Case “QA”
Me![QASignoff].visible = True
Case Else
End Select
End Sub

For question 2. If you have a button that comples the unit, just check all of the status before allowing the completion of the unit. If your button is on a form that is bound to the unit table, you can do the following in the click event:

Sub Complete_Click()
If Me![OperatorSignoff] and Me![SupervisorSignoff] and Me![QASignoff] Then
‘do what you need to to complete the unit
MsgBox “Not all sign offs are compelete”
End If
End Sub

Hope this helps

Discuss This Question: 1  Reply

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Rbrown38059
    I think I have found the actual answer I was looking for. I really don't want to use Microsoft's Workgroup Security feature and this website shows a method closer to what I was looking for: Thanks, R Brown
    85 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: