[RESOLVED] Help access form filter issue

[Brian Foshee]

Access 2007 coding in access vba

I am working on a database to keep track of the computers in my company. I have a form that shows all the fields in the table. I can edit them delete them insert new ones. All that works fine on the form. I can also search using the built in search function but I dont really like it. So in the header section of my form I have an unbound textbox and two buttons one is filter the other is clear.

Private Sub cmdFilter_Click()

Dim strWhere As String

If Len(Me.txtSearch & vbNullString) > 0 Then
strWhere = "([Location] Like """ & Me.txtSearch & "*"")"

End If

Me.Filter = strWhere
Me.FilterOn = True

Me.Requery

End Sub

This works fine I can type in a city name or part of it and it will filter the form to only show computers in our office in that city. However I want it to be able to filter anything. Say I know a serial number or I just want to see how many Dell pc's we have in our company I want to be able to type what ever information I know into that box and click filter and it show me the matches. Can someone please help me modify my code to allow for this. Thanks

[si_the_geek]

Welcome to VBForums

I think this is the kind of thing you want:

If Len(Me.txtSearch & vbNullString) > 0 Then
  strWhere = "([Location] Like """ & Me.txtSearch & "*"")"
  strWhere = strWhere & " OR ([SerialNumber] Like """ & Me.txtSearch & "*"")"
End If

..you should be able to add multiple copies of that line, and just change the field name each time.

[Brian Foshee]

Thank you so much that worked perfectly. I knew it was something simple with using OR but I tried and tried yesterday and just couldnt get the placement right.

[si_the_geek]

No problem.

As you now have it sorted out, could you please do us a little favour, and mark the thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do it by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved". (like various other features of this site, you need JavaScript enabled in your browser for this to work).

[Brian Foshee]

I dont suppose you would be able to give me any advice on another function I would like this form to do. I have 2 tables one called assets another called userhistory the assets table has the fields (pc name,serial,make,model,user,department,location) user history has just (pc name, user name). What I would like to do is when I have a record pulled up on my form I would like a box also on my form that lists the previous users for that PC. Any advice on this I cant even figure out where to start.

[si_the_geek]

As that is not related to the question at the start of this thread, it does not belong here - create a new thread for that question, and I'll reply to it if my idea is not posted by somebody else before I see it.