473,324 Members | 2,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

bre1603
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

20 4523
colintis
255 100+
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
8,834 Expert 8TB
@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
259 100+
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
munkee
374 256MB
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
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
You are absolutely correct in that I am easily misled!
Aug 4 '10 #9
NeoPa
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
munkee
374 256MB
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
It does seem to land us in sticky messes when we do generally, I must admit :D
Aug 5 '10 #17
bre1603
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
32,556 Expert Mod 16PB
That makes perfect sense Bre :) Good thinking.
Aug 5 '10 #19
ADezii
8,834 Expert 8TB
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
32,556 Expert Mod 16PB
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

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

Similar topics

5
by: TG | last post by:
Dear PHP Group, I have two forms that are used to collect user information. The first one takes user inputted values such as fullname, city, address etc. I want these values to display in the...
2
by: NotGiven | last post by:
Please help me understand the big picture of allowing users to upload pictures and keep them separate and tied to their record in the database. I want the whole thing automated and I'm just...
0
by: NotGiven | last post by:
I'd like help with the mysqldump command so that the fields are delimited by the four characters, *&&*. I'd also like for any apostophe,', or quote,", not to be escaped. Any help is MUCH...
1
by: william cline | last post by:
Hi, I am a beginner and below I have code for a long in form. My goal is for the form to read a file of a list of users and thier passwords ....compare the text box inputs to the file and either...
2
by: TechBoy | last post by:
Re:Access 2002 In my application when the main Invoice input form loads, I have an Access app that has all the normal security (hide the container, hide the toolbars, menubars, etc.) Right now,...
1
by: Bob Alston | last post by:
Anyone have experience in building Access apps with user definable fields? (Not the kind of fields where you just let the user define the label for a pre set number of predefined fields.) I...
2
by: JNariss | last post by:
Hello, I finally figured out how to get my form to its database and have an email come my way. However I am now trying to set up the body of the email to include the form details. However the...
4
by: billcute | last post by:
I have a form (frmSewer) which bounded to tblSewer. The code at the On Load Event is designed to open frmSewer is appropriate date is updated into tblFilingdatesat the end of each quarter. In...
2
by: prash.marne | last post by:
hi all , this is a very simple problem but i need some help, My situation is : i have a form which opens in a pop-up window , in which user will insert his email-id & name , there are two...
8
by: Mai Le | last post by:
Hello, I used Microsoft Access to create a login form with Name Password Access Level Admin and User I would like to let Admin login and open MRB form and User login then open other form like...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.