Results 1 to 8 of 8
  1. #1
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36

    User Restrictions based on Field Content in MS Access

    Hi everyone,



    got some really good tips from this forum yesterday so I figured I'd try my luck again.

    I need to set up user permissions within the same table, based on the value of a field. I know that this is not directly possible in Access but a post on Allenbrown.com points to a way of doing this, see here. I'm not proficient in coding so I'm hoping that I can get some directions from you. Here are the details:

    I have two tables in the database, a parent one populated via a form and a children one populated via a subform. The parent contains companies and the child contain subsidiaries of those companies. In the child table, I have a field called "Domicile" and I want to discriminate user access based on that. Because the database will be used by a variety of people worldwide, my plan is to create user groups based on location and allow users to edit (or add) information based on a match between their location (as specified in the group) and the domicile of the subsidiary. For example, a person in Europe will only be allowed to edit data for subsidiaries that are in Europe, even though companies from other domiciles may be stored in the same table.
    I'm looking for some guidance here as well as suggestions as to how you think may be done most effectively. I'm not partial to this method, that's just something I came up with to put some logic behind what I'm doing.

    Thank you so much!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So first have to establish a procedure whereby Access 'knows' who the user is. This can be a username/password setup or, as I do, get the username from local network login. Either way, need a table of users and credentials and permissions (region, department, authority, etc). You seem to already understand that.

    I save this user info on a form that never closes (main menu) so that it is available for reference at any time from anywhere. TempVars may be an alternative method to hold the info.

    Then the dataset of the subform must include a field that indicates the geographic region for the subsidiary and apply a filter parameter based on the user's assigned region.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Ok I think we are on the same page here, thank you!

    I am creating groups and I already included the domicile in my subforms. First two questions I have are:

    1) Do I need the name of the domiciles in the user groups I'm setting up be exactly the same as the ones I have in the "domicile" list?
    2) I'm not sure what you mean with saving users on a form? I created a custom category and from there a custom group through the navigation pane...are you talking about something else?

    Finally, i agree with you that I'm, gonna have to create some sort of filter. That's where I get stuck. Could you please exapnd on your last point? Are you implying that I should build a query that displays to the user the data he can see and/or alter based on his geographical group?

    Thanks,
    Bernardo

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1) I think yes - if you want to filter subsidiary dataset to return records where subsidiary region = user region, both datasets must have common value. How you input the region into subsidiary and user datasets is up to you. If you have lookup source table of regions (or domiciles) to aid in that effort, then good.

    2) To filter the subsidiary records there must be a parameter referenced. Setting the filter directly in the form RecordSource is one way, another is the Filter property of form. That filter must look somewhere for the parameter (user region). If you don't save that parameter somewhere when the user logs in, what will the filter look for? One method to make the user info available is to save it (populate textboxes) on a form that never closes. In my case, I have a procedure that identifies the user from the Network login and opens Main Menu form bound to Users table and filtered to the user record. Part of that procedure recognizes if the user is new (not already in Users table) and first opens Login form to create new Users record, then they never see the Login form again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Thank you so much June7. Sorry if I'm being insistent but my experience with Access is truly limited. You said:

    One method to make the user info available is to save it (populate textboxes) on a form that never closes.

    Could you please expand on this? Also, if I went the other way you propose, where would the recordsource be in the form, meaning, how could I alter it?

    Actually, we work with computers that require a network login (corporate PCs) so maybe your procedure would work? if so, could u please share it?

    Thanks!!
    Last edited by June7; 05-29-2014 at 11:33 AM.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    There is only one place for the RecordSource to be and that is the form RecordSource property. What varies is method to filter the recordset.

    1. static parameter (not what you want for this purpose)

    2. dynamic parameterized query, review http://www.datapigtechnologies.com/f...tomfilter.html

    3. apply filter to the WHERE CONDITION of DoCmd.OpenForm (or OpenReport)
    DoCmd.OpenForm "form name", , , "Region=" & reference to control on a form

    For details on my login process, review https://www.accessforums.net/access/...gin-23585.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    gori1084 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2014
    Posts
    36
    Thanks june7. Here is what I did for the login box. Thoughts?

    Private Sub Combo5_AfterUpdate()
    'After selecting user name set focus to password field
    Password.SetFocus
    End Sub

    Private Sub Command9_Click()
    'Check to see if data is entered into the UserName combo box

    If IsNull(Employee) Or Employee = "" Then
    MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
    Employee.SetFocus
    Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Password) Or Password = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Password.SetFocus
    Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Password.Value = DLookup("[Password]", "Users", _
    "[LoginID] =" & Employee.Value) Then

    LoginID = Employee.Value

    'Hide logon form and open splash screen
    Visible = False
    DoCmd.OpenForm "Sponsors"

    Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
    "Invalid Entry!"
    Password.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database.Please contact admin.", _
    vbCritical, "Restricted Access!"
    Application.Quit
    End If
    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Have you tested? Debugged? Review link at bottom of my post.

    Login procedure is common topic in forum. You will find many threads and examples searching here and web.

    What you show looks like a lot I have seen.

    It is not what I do but if that is the approach you want, it is your prerogative.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-08-2014, 10:49 AM
  2. Replies: 5
    Last Post: 04-23-2013, 01:42 PM
  3. populate field in a form based on user???
    By DCV0204 in forum Forms
    Replies: 1
    Last Post: 01-17-2013, 08:16 AM
  4. How do you select a field based on user input?
    By technesurge in forum Queries
    Replies: 5
    Last Post: 06-20-2012, 02:04 PM
  5. Field content based on date calculation
    By jlmnjem in forum Programming
    Replies: 6
    Last Post: 09-23-2010, 10:24 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums