Filter Access Form Based On Textbox Value

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]","tblIDandCategory","EmpID = '" & [Forms]![frmDetail]![text15] & "'")

This Dlookup (in the txtCategory2 textbox) does not work to get the category, however the txtUser textbox does contain my ID. =DLookUp("[Category]","tblIDandCategory","EmpID = '" & [Forms]![frmDetail]![txUser] & "'")

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.

More detail
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
Call GetUser
'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
End Sub

Open in new window


This code captures the User ID and displays it in the txtUser textbox.

Sub GetUser()
      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)
      Else
         MsgBox "Unable to get the name."
         End
      End If
      Forms![frmDetail]!txtUser = lpUserName
   End Sub

Open in new window

TestingFilter.accdb
LVL 1
upsfaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
So what is your question...
One filter works and the other one does not...?

I am a bit confused by your design here,...
I would take a different approach.
0
upsfaAuthor Commented:
I am looking into another approach, but I guess the question here is why does the Dlookup using one textbox work, while the other does not.  The filter doesn't actually fail, it is the DLookup that fails.  I don't know if you have looked, but I did attach the database.  thanks.
0
Jeffrey CoachmanMIS LiasonCommented:
I get an error opening your form
You have inconsistent key field names
There are no relationships set.
I may not be understanding your design here, ...but I would use a main and subform to do the filtering, ...then no lookup would be needed...

...simple sample is attached

Lets see if another expert can dig deeper.


JeffCoachman
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jeffrey CoachmanMIS LiasonCommented:
0
Jeffrey CoachmanMIS LiasonCommented:
Try this syntax instead,
...it worked fine for me:

=DLookUp("[Category]","tblIDandCategory","EmpID ='" & [txtUser] & "'")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Sample with new dlookup syntax
TestingFilter-2015-04-09.accdb
0
upsfaAuthor Commented:
That's it.  Perfect.  Thank  you.
0
Jeffrey CoachmanMIS LiasonCommented:
Not sure why your syntax errored, ...but I figure I'd give the shorter syntax a try...

Sorry for the confusion...
I guess it was simpler than I thought...
;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.