How do I unlock fields on a form to specific users?

[bre1603]

I have a form in Access 2007 that has several locked fields. I would like to have different fields unlocked for different groups of users - depending on their department.

Currently, the fields are unlocked based on user IDs in the Form_Open event. But I'm thinking it would be easier to have code that compares the user's ID to values in a table and locks/unlocks fields accordingly. The coding can get cumbersome, especially when the number of users grow or change.

I am using a module (UserID Module) to reference the user's ID. I also created a table to use in the new code that includes the fields: Name, UserID, and Department.

I'm certain this can be done, but I'm blanking on how.

Thanks in advance for any help.

Here's a sample of my current code:

If Me.UserName = "bmor109" Then   
        Me.Leadership_Contact_Subform.Locked = False
        Me.HIBS_Contact_Subform.Locked = False
End If

[colintis]

I'll just do a rough talk first.

You can use ADO hook up with a database table that contains your list of department or username, extract the data you want for the comparison, such as user ID and department.

Your SQL to extract the data could be the following:

Dim strSQL As String
strSQL = "SELECT userID, Department " & _
         "FROM userTable " & _
         "WHERE userID = " & Me.UserName & ";"

Once the data is stored into the variable for comparison, you can use Select Case to perform different unlocks to different groups of users. Here's an example:

Select Case < User ID / Department / Group (either one)>

    Case <Something it match (e.g.bmor109) >

      Me.Leadership_Contact_Subform.Locked = False
      Me.HIBS_Contact_Subform.Locked = False

    Case Else

      Do something else

[ADezii]

I have a form in Access 2007 that has several locked fields. I would like to have different fields unlocked for different groups of users - depending on their department.

Currently, the fields are unlocked based on user IDs in the Form_Open event. But I'm thinking it would be easier to have code that compares the user's ID to values in a table and locks/unlocks fields accordingly. The coding can get cumbersome, especially when the number of users grow or change.

I am using a module (UserID Module) to reference the user's ID. I also created a table to use in the new code that includes the fields: Name, UserID, and Department.

I'm certain this can be done, but I'm blanking on how.

Thanks in advance for any help.

Here's a sample of my current code:

If Me.UserName = "bmor109" Then   
        Me.Leadership_Contact_Subform.Locked = False
        Me.HIBS_Contact_Subform.Locked = False
End If

Dim frm1 As SubForm
Dim frm2 As SubForm

Set frm1 = Me.Leadership_Contact_Subform
Set frm2 = Me.HIBS_Contact_Subform

Select Case Me.UserName
  Case "User1", "User2", "User23", "User100"
    frm1.Locked = True
    frm2.Locked = True
  Case "User17", "User19"
    frm1.Locked = False
    frm2.Locked = False
  Case "User45"
    frm1.Locked = True
    frm2.Locked = False
  Case "User233", "User23", "User24", "User99", "User 448"
    frm1.Locked = False
    frm2.Locked = True
  Case "Else"       'All 'other' Users
    frm1.Locked = True
    frm2.Locked = True
End Select

[Jerry Maiapu]

Try putting some bound textboxes for User ID / Department / Group, set Invisible = true to make it invisible and compare the values displayed to lock or unlock.

Then you can use, say, if..else or case statement,

like:

if Department=something And Group=Something then
Me.Leadership_Contact_Subform.Locked = False 
      Me.HIBS_Contact_Subform.Locked = False 
else

'do something else...
end if

or alternatively you can use select case statement

Just a suggestion...

[munkee]

I have a login form that pops up when you first enter the database using an autoexec macro. I then read the information from the database and write it to a hidden form which is always open in the background:

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click
'-----------------------------------------------------------------------------------------------------------------------------
' This code is used to validate users found in the tblSecurity table. If the wrong user name or password is
' provided access is denied.
'-----------------------------------------------------------------------------------------------------------------------------
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstV As Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)
    
    If Not IsNull(Me.txtUser) And Not IsNull(Me.txtPassword) Then
        rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUser & "'"
    
        If rst.NoMatch Then
            MsgBox "You entered the wrong User Name or Password." & Chr(13) & _
            "Please enter the correct User Name and Password or " & Chr(13) & _
            "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
        ElseIf Me.txtPassword = "password" Then
            MsgBox "This is the first time using the database or your passowrd has been reset." & Chr(13) & _
            "You must change your password before you can enter the database.", _
            vbOKOnly + vbExclamation, "Change Password"
            stDocName = "frmUserLogonNew"
            stLinkCriteria = "[UserID]=" & "'" & Me![txtUser] & "'"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        Else
            stDocName = "frmSplashScreen"
            DoCmd.OpenForm stDocName, , , stLinkCriteria
        End If
    Else
        MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
        "Please enter the correct User Name and Password or " & Chr(13) & _
        "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
    End If
    
    
    With Forms!frmHidden
        .txtViewID = rst.Fields("ViewID")
        .txtAccessID = rst.Fields("AccessID")
        .txtActive = rst.Fields("Active")
        .txtPassword = rst.Fields("Password")
        .txtUserID = rst.Fields("UserID")
        .txtSecurityID = rst.Fields("SecurityID")
        .txtFName = rst.Fields("FName")
        .txtSName = rst.Fields("SName")
        .txtEMailAd = rst.Fields("EmailAd")
        .txtUdept = rst.Fields("UDept")
    End With
    
    
    rst.Close
    
Exit_cmdOk_Click:
    Exit Sub

Err_cmdOk_Click:
    MsgBox err.Description
    Resume Exit_cmdOk_Click

End Sub

I then use this form to validate the opening of any of my other forms:

Private Sub Form_Open(Cancel As Integer)

'check to see user access ID and enable disable buttons to meet criteria
On Error GoTo Err_Form_Open

 
    Select Case Forms!frmHidden!txtAccessID
    
    
    
    Case 1
    
        Me.cmdadminarea.Enabled = True
        Me.btnexportsearchanalyse.Enabled = True
        Me.btnsubmitncc.Enabled = True
        Me.cmdQuickGo.Enabled = True
    
    Case 2
    
        Me.cmdadminarea.Enabled = False
        Me.btnexportsearchanalyse.Enabled = True
        Me.btnsubmitncc.Enabled = True
        Me.cmdQuickGo.Enabled = True
    
    Case 4
    
        Me.cmdadminarea.Enabled = False
        Me.btnexportsearchanalyse.Enabled = False
        Me.btnsubmitncc.Enabled = True
        Me.cmdQuickGo.Enabled = False
        
    Case Else
        Me.cmdadminarea.Enabled = False
        Me.btnexportsearchanalyse.Enabled = False
        Me.btnsubmitncc.Enabled = False
        Me.cmdQuickGo.Enabled = False
        
    End Select
       
    
Exit_Form_Open:
    Exit Sub

Err_Form_Open:
    MsgBox err.Description
    Me.Visible = True
    Resume Exit_Form_Open
End Sub

[bre1603]

Thank you all for the responses. I tried using the Select Case statement, but I'm still not getting the controls to unlock based on the user's department. Here's the code I have in place. Maybe I missing something...

Dim strSQL As String
Dim frm1 As SubForm
Dim frm2 As SubForm

strSQL = "SELECT Department " & vbCrLf & _
         "FROM Users " & vbCrLf & _
         "WHERE UserID = " & Me.UserName & ";"
         
Set frm1 = Me.Leadership_Contact_Subform
Set frm2 = Me.HIBS_Contact_Subform
         
Select Case strSQL
    Case "Insurance Accounting"
      frm1.Locked = True
      frm2.Locked = True
    Case "PEBB Customer Service"
      frm1.Locked = False
      frm2.Locked = False
    Case Else
      frm1.Locked = True
      frm2.Locked = True
End Select

or maybe I'm completely off base! Thanks in advance for your feedback.

[ADezii]

Dim frm1 As SubForm
Dim frm2 As SubForm
Dim strDept As String

'Assumes there exists a Department for each and every [UserID], and that
'the [UserName] Field is Not Null and restricted to existing Users. If
'either one or both conditions are not True, then Validation Code must be added.
strDept = DLookup("[Department]", "Users", "[UserID] = '" & Me.UserName & "'")
  
Set frm1 = Me.Leadership_Contact_Subform
Set frm2 = Me.HIBS_Contact_Subform
  
Select Case strDept
  Case "Insurance Accounting"
    frm1.Locked = True
    frm2.Locked = True
  Case "PEBB Customer Service"
    frm1.Locked = False
    frm2.Locked = False
  Case Else
    frm1.Locked = True
    frm2.Locked = True
End Select

[NeoPa]

I suspect ADezii may have been a little misled by the name of the UserName control. Would I be right in guessing this is a ComboBox which displays names but returns a numeric ID value? If so, then the third parameter for the DLookup() call (in line #8) would need the single-quotes (') removed :

strDept = DLookup("[Department]", "Users", "[UserID] = " & Me.UserName)

[ADezii]

You are absolutely correct in that I am easily misled!

[NeoPa]

I wasn't trying to imply you are easily misled (as I'm sure you know really :p). The name used here is unfortunate and liable to confuse anyone reading the code. It was only by cross-referencing other information posted that I guessed (I may still be proven wrong) that this was a reference to the numeric ID field rather than the textual name field from the table.

[bre1603]

DLookup - of course! I replaced the strSQL with the strDept and it worked perfectly. This will allow my users to manage who has access to specific controls by means of the table data, and keep them out of the form's code. Thank you so much.

As for the UserName control, I think normally you would be right, NeoPa. But in this case, it's a text control with the control source "=fOSUserName() " which references a module, "UserID Module". So it wasn't necessary to remove the single quotes this time.

Thanks again for all the help!!

[NeoPa]

Does that mean the [UserID] field in your table is a string field? That's very unusual, but does happen sometimes.

Scratch that. I just went back to post #1 (OP) and your example VBA code indicates that the field is indeed a string field. Sorry I failed to spot that earlier. I guess that would be what the user signs in as, as opposed to a random AutoNumber type field. I think I get it now.

PS. I would still advise a name somewhat more like [UserID] for the control, especially as you have a [Name] field in your table. This isn't critical by any means, but could help to reduce misunderstandin gs. I'm not trying to be argumentative here, just offering some advice from experience.

[munkee]

To be honest I would go with your suggestion NeoPa. I have been using a similar set up as my code further up can suggest with User IDs and User etc etc it can get really misleading after a while and lots of switching between your code and your tables to make sure you are actually referencing the right things. Keeping things as seperate as possible make it much easier to understand, plus if anyone were to take over your legacy im sure they would appreciate it too.

[ADezii]

As a side note, and for the sake of brevity, you should be able to bypass the Text Box, and access the Return Value of the Function directly, as in:

strDept = DLookup("[Department]", "Users", "[UserID] = '" & fOSUserName() & "'")

[NeoPa]

Good thinking ADezii, but that may throw up issues. The returned value may not always be the same (probably would in this case mind), and it also triggers the function to be run again, which shouldn't be necessary.

I know you love your VBA coding (:D), but using the value already prepared may be easier in this case :)