I want to filter the records in an Access 2013 form based on the ID of the person logged in to the computer. The detail records do not have IDs on them, they have categories. I have another table that associates IDs with Categories.
I have working code that captures the user ID and displays it in a textbox on the form (txtUser).
I want to use that user ID in another textbox as part of a Dlookup to capture the category of that user.
Using that category, I want to filter the form so that the user only sees records for the category associated with the user ID.
This Dlookup (in the txtCategory1 textbox) works to get the category, but only because my ID is typed directly into the text15 textbox. =DLookUp("[Category]","tbl
D = '" & [Forms]![frmDetail]![text1
5] & "'")
This Dlookup (in the txtCategory2 textbox) does not work to get the category, however the txtUser textbox does contain my ID. =DLookUp("[Category]","tbl
D = '" & [Forms]![frmDetail]![txUse
r] & "'")
I can filter the form successfully when I reference the txtCategory1 box in my filter, but I need it to work when I reference txtCategory2. I am not sure why this does not work. Any ideas? Thanks.
tblDetail - Contains the detail data to be displayed and updated in the form
tblIDandCategory - Table that associates user IDs with their Category
In order to test this you would need to enter your Windows Username into the tblIDandCategory and associate it with one the two sample categories that are used in the tblDetail (BO or CT).
This runs the code to capture my ID and filters the form:
Private Sub Form_Load()
'GetUser captures the user ID and displays it in the textbox txtUser
'This filters the form based on the txtCategory1 textbox (which is populated using a Dlookup that uses ‘my hard-coded ID). I really want this to filter based on txtCategory2 which should be populated
'using the actual user ID from the txtUser textbox, rather than the hard-coded ID in the Text15 textbox.
Me.Filter = "Category = '" & Me.txtCategory1.Value & "'"
Me.FilterOn = True
This code captures the User ID and displays it in the txtUser textbox.
Const lpnLength As Integer = 255
Dim Status As Integer
Dim lpName, lpUserName As String
lpUserName = Space$(lpnLength + 1)
Status = WNetGetUser(lpName, lpUserName, lpnLength)
If Status = NoError Then
lpUserName = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
MsgBox "Unable to get the name."
Forms![frmDetail]!txtUser = lpUserName