Return all values in column B for same value in column A

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!

Answer 1

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.

Answer 2

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)

Answer 3

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!  :-)

Answer 4

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)

Answer 5

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.

Answer 6

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)

Answer 7

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 [e-mail].

Answer 8

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!