Answered by:
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!
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)
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.
- Edited by Deepak Saradkumar PanchalMicrosoft contingent staff Wednesday, February 8, 2017 1:22 AM
Wednesday, February 8, 2017 1:20 AM -
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