By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,401 Members | 1,381 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,401 IT Pros & Developers. It's quick & easy.

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

bre1603
P: 39
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:

Expand|Select|Wrap|Line Numbers
  1. If Me.UserName = "bmor109" Then   
  2.         Me.Leadership_Contact_Subform.Locked = False
  3.         Me.HIBS_Contact_Subform.Locked = False
  4. End If
Aug 2 '10 #1

✓ answered by ADezii

Expand|Select|Wrap|Line Numbers
  1. Dim frm1 As SubForm
  2. Dim frm2 As SubForm
  3. Dim strDept As String
  4.  
  5. 'Assumes there exists a Department for each and every [UserID], and that
  6. 'the [UserName] Field is Not Null and restricted to existing Users. If
  7. 'either one or both conditions are not True, then Validation Code must be added.
  8. strDept = DLookup("[Department]", "Users", "[UserID] = '" & Me.UserName & "'")
  9.  
  10. Set frm1 = Me.Leadership_Contact_Subform
  11. Set frm2 = Me.HIBS_Contact_Subform
  12.  
  13. Select Case strDept
  14.   Case "Insurance Accounting"
  15.     frm1.Locked = True
  16.     frm2.Locked = True
  17.   Case "PEBB Customer Service"
  18.     frm1.Locked = False
  19.     frm2.Locked = False
  20.   Case Else
  21.     frm1.Locked = True
  22.     frm2.Locked = True
  23. End Select

Share this Question
Share on Google+
20 Replies


100+
P: 255
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. strSQL = "SELECT userID, Department " & _
  3.          "FROM userTable " & _
  4.          "WHERE userID = " & Me.UserName & ";"
  5.  
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:
Expand|Select|Wrap|Line Numbers
  1. Select Case < User ID / Department / Group (either one)>
  2.  
  3.     Case <Something it match (e.g.bmor109) >
  4.  
  5.       Me.Leadership_Contact_Subform.Locked = False
  6.       Me.HIBS_Contact_Subform.Locked = False
  7.  
  8.     Case Else
  9.  
  10.       Do something else
Aug 3 '10 #2

ADezii
Expert 5K+
P: 8,701
@bre1603
Expand|Select|Wrap|Line Numbers
  1. Dim frm1 As SubForm
  2. Dim frm2 As SubForm
  3.  
  4. Set frm1 = Me.Leadership_Contact_Subform
  5. Set frm2 = Me.HIBS_Contact_Subform
  6.  
  7. Select Case Me.UserName
  8.   Case "User1", "User2", "User23", "User100"
  9.     frm1.Locked = True
  10.     frm2.Locked = True
  11.   Case "User17", "User19"
  12.     frm1.Locked = False
  13.     frm2.Locked = False
  14.   Case "User45"
  15.     frm1.Locked = True
  16.     frm2.Locked = False
  17.   Case "User233", "User23", "User24", "User99", "User 448"
  18.     frm1.Locked = False
  19.     frm2.Locked = True
  20.   Case "Else"       'All 'other' Users
  21.     frm1.Locked = True
  22.     frm2.Locked = True
  23. End Select
Aug 3 '10 #3

Jerry Maiapu
100+
P: 259
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:

Expand|Select|Wrap|Line Numbers
  1. if Department=something And Group=Something then
  2. Me.Leadership_Contact_Subform.Locked = False 
  3.       Me.HIBS_Contact_Subform.Locked = False 
  4. else
  5.  
  6. 'do something else...
  7. end if
or alternatively you can use select case statement

Just a suggestion...
Aug 3 '10 #4

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdOk_Click()
  2. On Error GoTo Err_cmdOk_Click
  3. '-----------------------------------------------------------------------------------------------------------------------------
  4. ' This code is used to validate users found in the tblSecurity table. If the wrong user name or password is
  5. ' provided access is denied.
  6. '-----------------------------------------------------------------------------------------------------------------------------
  7.     Dim db As DAO.Database
  8.     Dim rst As DAO.Recordset
  9.     Dim rstV As Recordset
  10.     Dim stDocName As String
  11.     Dim stLinkCriteria As String
  12.  
  13.     Set db = CurrentDb()
  14.     Set rst = db.OpenRecordset("tblSecurity", dbOpenDynaset)
  15.  
  16.     If Not IsNull(Me.txtUser) And Not IsNull(Me.txtPassword) Then
  17.         rst.FindFirst "Password = '" & Me.txtPassword & "'" & " And UserID = '" & Me.txtUser & "'"
  18.  
  19.         If rst.NoMatch Then
  20.             MsgBox "You entered the wrong User Name or Password." & Chr(13) & _
  21.             "Please enter the correct User Name and Password or " & Chr(13) & _
  22.             "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
  23.         ElseIf Me.txtPassword = "password" Then
  24.             MsgBox "This is the first time using the database or your passowrd has been reset." & Chr(13) & _
  25.             "You must change your password before you can enter the database.", _
  26.             vbOKOnly + vbExclamation, "Change Password"
  27.             stDocName = "frmUserLogonNew"
  28.             stLinkCriteria = "[UserID]=" & "'" & Me![txtUser] & "'"
  29.             DoCmd.OpenForm stDocName, , , stLinkCriteria
  30.         Else
  31.             stDocName = "frmSplashScreen"
  32.             DoCmd.OpenForm stDocName, , , stLinkCriteria
  33.         End If
  34.     Else
  35.         MsgBox "You left the User Name and/or Password blank." & Chr(13) & _
  36.         "Please enter the correct User Name and Password or " & Chr(13) & _
  37.         "contact the Database Adminstrator for assistance.", vbOKOnly + vbCritical, "Logon Denied"
  38.     End If
  39.  
  40.  
  41.     With Forms!frmHidden
  42.         .txtViewID = rst.Fields("ViewID")
  43.         .txtAccessID = rst.Fields("AccessID")
  44.         .txtActive = rst.Fields("Active")
  45.         .txtPassword = rst.Fields("Password")
  46.         .txtUserID = rst.Fields("UserID")
  47.         .txtSecurityID = rst.Fields("SecurityID")
  48.         .txtFName = rst.Fields("FName")
  49.         .txtSName = rst.Fields("SName")
  50.         .txtEMailAd = rst.Fields("EmailAd")
  51.         .txtUdept = rst.Fields("UDept")
  52.     End With
  53.  
  54.  
  55.     rst.Close
  56.  
  57. Exit_cmdOk_Click:
  58.     Exit Sub
  59.  
  60. Err_cmdOk_Click:
  61.     MsgBox err.Description
  62.     Resume Exit_cmdOk_Click
  63.  
  64. End Sub
I then use this form to validate the opening of any of my other forms:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2.  
  3. 'check to see user access ID and enable disable buttons to meet criteria
  4. On Error GoTo Err_Form_Open
  5.  
  6.  
  7.     Select Case Forms!frmHidden!txtAccessID
  8.  
  9.  
  10.  
  11.     Case 1
  12.  
  13.         Me.cmdadminarea.Enabled = True
  14.         Me.btnexportsearchanalyse.Enabled = True
  15.         Me.btnsubmitncc.Enabled = True
  16.         Me.cmdQuickGo.Enabled = True
  17.  
  18.     Case 2
  19.  
  20.         Me.cmdadminarea.Enabled = False
  21.         Me.btnexportsearchanalyse.Enabled = True
  22.         Me.btnsubmitncc.Enabled = True
  23.         Me.cmdQuickGo.Enabled = True
  24.  
  25.     Case 4
  26.  
  27.         Me.cmdadminarea.Enabled = False
  28.         Me.btnexportsearchanalyse.Enabled = False
  29.         Me.btnsubmitncc.Enabled = True
  30.         Me.cmdQuickGo.Enabled = False
  31.  
  32.     Case Else
  33.         Me.cmdadminarea.Enabled = False
  34.         Me.btnexportsearchanalyse.Enabled = False
  35.         Me.btnsubmitncc.Enabled = False
  36.         Me.cmdQuickGo.Enabled = False
  37.  
  38.     End Select
  39.  
  40.  
  41. Exit_Form_Open:
  42.     Exit Sub
  43.  
  44. Err_Form_Open:
  45.     MsgBox err.Description
  46.     Me.Visible = True
  47.     Resume Exit_Form_Open
  48. End Sub
Aug 3 '10 #5

bre1603
P: 39
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...

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2. Dim frm1 As SubForm
  3. Dim frm2 As SubForm
  4.  
  5. strSQL = "SELECT Department " & vbCrLf & _
  6.          "FROM Users " & vbCrLf & _
  7.          "WHERE UserID = " & Me.UserName & ";"
  8.  
  9. Set frm1 = Me.Leadership_Contact_Subform
  10. Set frm2 = Me.HIBS_Contact_Subform
  11.  
  12. Select Case strSQL
  13.     Case "Insurance Accounting"
  14.       frm1.Locked = True
  15.       frm2.Locked = True
  16.     Case "PEBB Customer Service"
  17.       frm1.Locked = False
  18.       frm2.Locked = False
  19.     Case Else
  20.       frm1.Locked = True
  21.       frm2.Locked = True
  22. End Select
or maybe I'm completely off base! Thanks in advance for your feedback.
Aug 4 '10 #6

ADezii
Expert 5K+
P: 8,701
Expand|Select|Wrap|Line Numbers
  1. Dim frm1 As SubForm
  2. Dim frm2 As SubForm
  3. Dim strDept As String
  4.  
  5. 'Assumes there exists a Department for each and every [UserID], and that
  6. 'the [UserName] Field is Not Null and restricted to existing Users. If
  7. 'either one or both conditions are not True, then Validation Code must be added.
  8. strDept = DLookup("[Department]", "Users", "[UserID] = '" & Me.UserName & "'")
  9.  
  10. Set frm1 = Me.Leadership_Contact_Subform
  11. Set frm2 = Me.HIBS_Contact_Subform
  12.  
  13. Select Case strDept
  14.   Case "Insurance Accounting"
  15.     frm1.Locked = True
  16.     frm2.Locked = True
  17.   Case "PEBB Customer Service"
  18.     frm1.Locked = False
  19.     frm2.Locked = False
  20.   Case Else
  21.     frm1.Locked = True
  22.     frm2.Locked = True
  23. End Select
Aug 4 '10 #7

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. strDept = DLookup("[Department]", "Users", "[UserID] = " & Me.UserName)
Aug 4 '10 #8

ADezii
Expert 5K+
P: 8,701
You are absolutely correct in that I am easily misled!
Aug 4 '10 #9

NeoPa
Expert Mod 15k+
P: 31,768
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.
Aug 4 '10 #10

bre1603
P: 39
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!!
Aug 4 '10 #11

NeoPa
Expert Mod 15k+
P: 31,768
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 misunderstandings. I'm not trying to be argumentative here, just offering some advice from experience.
Aug 5 '10 #12

100+
P: 374
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.
Aug 5 '10 #13

ADezii
Expert 5K+
P: 8,701
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:
Expand|Select|Wrap|Line Numbers
  1. strDept = DLookup("[Department]", "Users", "[UserID] = '" & fOSUserName() & "'") 
Aug 5 '10 #14

NeoPa
Expert Mod 15k+
P: 31,768
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 :)
Aug 5 '10 #15

ADezii
Expert 5K+
P: 8,701
Gotcha, and understood. I was thinking along the lines of a User potentially overwriting or modifying the User Name displayed in the Text Box, which would create another set of problems. This can, of course, be averted by simply Locking the Text Box, but you know in this business that we can never assume the obvious.
Aug 5 '10 #16

NeoPa
Expert Mod 15k+
P: 31,768
It does seem to land us in sticky messes when we do generally, I must admit :D
Aug 5 '10 #17

bre1603
P: 39
@ NeoPa:

I can see your point about the confusion between User ID and Name. So I renamed the "UserID" field in my table to "UserName" to match the control in my form. I decided to change it to UserName (vs change my control’s name to UserID) for several reasons:

1. it’s called a "User Name" by our network and when an employee signs on to their computer, they enter their User Name and password. So this makes things consistent between the database and the network, and hopefully helps avoid confusion about what a UserName is.

2. I got a message that the word "Name" was a "reserved word" when I created the table, so I later changed it to "Staff Name" to avoid any unforeseen future errors. So each employee in the table has a User Name, a Staff Name and a Department.

3. Because you’re right – UserID sounds more like an auto-number field. But our UserNames are a combination of our initials and department numbers.

@ ADezii:

The good news is the text box with the User Name is locked. I use the text box in several ways, and I, too, didn’t want users to be able to mess with it. They like to do that... :)
Aug 5 '10 #18

NeoPa
Expert Mod 15k+
P: 31,768
That makes perfect sense Bre :) Good thinking.
Aug 5 '10 #19

ADezii
Expert 5K+
P: 8,701
Just as a trivial side note, the assignment of the UserName to the Text Box can also be done withing the confines of the Function. Setting the Control Source of the Text Box would no longer be needed, resulting in a single Function Call, and killing two birds with one stone. Whay say ye, olde NeoPa?
Aug 5 '10 #20

NeoPa
Expert Mod 15k+
P: 31,768
That could certainly work. The only issue would be whether or not the design should allow for the operator to see the UserName displayed before entering the password I suppose.

Olde you say? Well, if you don't rush out of your decade too quickly I'll join you there for a wee while :p I'm nearly done with my current one.
Aug 6 '10 #21

Post your reply

Sign in to post your reply or Sign up for a free account.