none
Return all values in column B for same value in column A RRS feed

  • Question

  • Hello,

    I have a database in which I'm trying to manage permissions to records via UserID read from a card (fOSUserName).  I've made a table called Security that Includes: UserID (unique 8-digit user number that is read from the physical access card in CAC reader) PermissionArea (values include "Alpha", "Bravo", "Charlie", "Delta", etc. to "Gulf" which describe permissions to various records, only one PermissionArea per record), and then UserPermissionArea which concatenates the two fields.

    In general, a specific user only needs one Permission Area and so this code works fine on the BeforeChange section of the input/edit form:

    If fOSUserName() & PermissionArea.value <> DLookup("UserPermissionArea","Security") Then

    MsgBox ("You don't have permissions to change this record."), vbCritical, "Not Authorized."

    Me.Undo

    DoCmd.CancelEvent

    End If

    I used DLookup because I was hoping that if a particular user needed multiple PermissionAreas, I could re-enter their UserID into a new record in my Security table and enter the additional Permission Area.  So it might look like this:

    12345678      Alpha

    25698745      Bravo

    98653457      Bravo

    12345678      Charlie

    What I'm hoping to do is to allow user 12345678 to have access to both Alpha records and Charlie records.  Any ideas what I can do?  Any help is most appreciated!

    Tuesday, February 7, 2017 1:11 PM

Answers

  • Something like this:

    Dim dbs As DAO.Database
    Dim rsPermissions As DAO.Recordset
    Dim strPermissions As String
    Dim strCheck As String
    Dim strControl As String

    strControl = fOSUserName() & PermissionArea.value
    strPermissions = "SELECT UserPermissionArea FROM Security;"

    Set dbs = CurrentDb
    Set rsPermissions = dbs.OpenRecordset(strPermissions, dbOpenDynaset)

    rsPermissions.MoveFirst
    Do While Not rsPermissions.EOF
        strCheck = rsPermissions!UserPermissionArea
        If strCheck = strControl
        'Do something
        Else
        rsPermissions.MoveNext
        End If
        If rsPermissions.EOF Then 
        MsgBox ("You don't have permissions to change this record."), vbCritical, "Not Authorized."
    Me.Undo
    DoCmd.CancelEvent
        End If
    Loop

    Let me know if that works for you.
    • Proposed as answer by Curt Russell Tuesday, February 7, 2017 2:25 PM
    • Marked as answer by SmagBoy1 Tuesday, February 7, 2017 3:09 PM
    Tuesday, February 7, 2017 1:56 PM

All replies

  • Something like this:

    Dim dbs As DAO.Database
    Dim rsPermissions As DAO.Recordset
    Dim strPermissions As String
    Dim strCheck As String
    Dim strControl As String

    strControl = fOSUserName() & PermissionArea.value
    strPermissions = "SELECT UserPermissionArea FROM Security;"

    Set dbs = CurrentDb
    Set rsPermissions = dbs.OpenRecordset(strPermissions, dbOpenDynaset)

    rsPermissions.MoveFirst
    Do While Not rsPermissions.EOF
        strCheck = rsPermissions!UserPermissionArea
        If strCheck = strControl
        'Do something
        Else
        rsPermissions.MoveNext
        End If
        If rsPermissions.EOF Then 
        MsgBox ("You don't have permissions to change this record."), vbCritical, "Not Authorized."
    Me.Undo
    DoCmd.CancelEvent
        End If
    Loop

    Let me know if that works for you.
    • Proposed as answer by Curt Russell Tuesday, February 7, 2017 2:25 PM
    • Marked as answer by SmagBoy1 Tuesday, February 7, 2017 3:09 PM
    Tuesday, February 7, 2017 1:56 PM
  • I don't use DLookup very often but can't you use the third argument (criteria) to return what you need?

    DLookup("UserPermissionArea","Security", "[UserID] = " & fOSUserName() & " AND [PermissionArea] = " & PermissionArea.Value) 


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 7, 2017 2:18 PM
  • I tried this, Paul, and the code is legal and works, but it still only looks up until it finds the first instance of the UserName and then assigns only that level of permission.  Very frustrating, but fun!  :-)
    Tuesday, February 7, 2017 3:10 PM
  • I tried this, Paul, and the code is legal and works, but it still only looks up until it finds the first instance of the UserName and then assigns only that level of permission.  Very frustrating, but fun!  :-)

    Really? I would have expected it to return only the row where both the UserID and PermissionArea match and not any row where only the UserID matches.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 7, 2017 3:23 PM
  • I'm sorry, I wasn't clear.  Yes, it returns the row where both match, but I want it to return ALL rows where UserName matches the ID card number.  So, for my example in the question, I wanted it to return User 12345678 with permission set ALPHA, but I also wanted it to return, in addition, User 12345678 with permission set Charlie.
    Tuesday, February 7, 2017 8:35 PM
  • I'm sorry, I wasn't clear.  Yes, it returns the row where both match, but I want it to return ALL rows where UserName matches the ID card number.  So, for my example in the question, I wanted it to return User 12345678 with permission set ALPHA, but I also wanted it to return, in addition, User 12345678 with permission set Charlie.

    Yeah, I thought you were just looking for a specific one. DLookup won't help you if you are looking for more than one.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, February 8, 2017 1:00 AM
  • Hi SmagBoy1,

    I find that you had mark the answer but still it looks like some issue is there.

    did your issue is solved by that marked answer or issue is still exist.

    if your issue is solved then let me know about that I will close this thread.

    if your issue is still exist then let me know about that we will try to suggest you further to solve this issue.

    I can see that you are checking the condition like bellow.

    If fOSUserName() & PermissionArea.value <> DLookup("UserPermissionArea","Security") 

    please try to check each condition individually and then join it with and. so that if both condition are true then it will go inside if condition.

    like below.

    Sub demo()
      
        Dim usrname, area As String
        usrname = "abc"
        area = "123"
        If usrname = "abc" And area = "123" Then
        Debug.Print ("done")
        End If
    
    End Sub

    Reference:

    Application.DLookup Method (Access)

    DLookup Function

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, February 8, 2017 1:20 AM
    Moderator
  • Good morning, Deepak,

    Yes, the question is solved.  I kept looking and came up with this:

    Dim SECUREDIM As String
    SECUREDIM = Nz(DLookup("userpermissionarea", "security", "userpermissionarea = '" & Forms![frmdatainputform]!Text226 & "'"))
                                                    
    If STRCAFC <> Forms!frmdatainputform!Text226 Or IsNull(STRCAFC) = True Then
    MsgBox ("The changes you've entered are to a line that you do not have permission to change as it is not under your Directorate."), vbCritical, "Not Authorized"
    DoCmd.CancelEvent
    Me.Undo
    DoCmd.CancelEvent
    End If
    So, making the Dim checks every value to see if it matches.  At least that's what I think is happening.  :-)  It's working, so, that's a good thing.  :-)  Thank you!

    Wednesday, February 8, 2017 5:59 AM