By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 2,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Filtering forms

P: 5
Hi,
I was wondering if you could help me with a problem. I have 2 combos in an access form and I would like to be able to filter based on both of them.
My first combo has usernames and the second one has company names. So I would like users to be able to pick their name from the first drop down and then pick the company name from the second drop down list so they can see what issues for that company are assigned to them.

I am currently filtering each one using this method as recommended in a previous post:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCompany_AfterUpdate() 
  2. If Not IsNull(Me![cboCompany]) Then 
  3.   Me.Filter = "[Company]='" & Me![cboCompany] & "'" 
  4.   Me.FilterOn = True 
  5. End If 
  6. End Sub
It works great if I only want to filter one at a time.
Any help you can provide is great! I am using access 2007.
Thanks!
Jan 7 '09 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 634
@linwork
Hi

Perhaps something like this
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboCompany_AfterUpdate()
  2.     SetFilter
  3. End Sub
  4. Private Sub cboUsername_AfterUpdate()
  5.     SetFilter
  6. End Sub
  7. Private Sub SetFilter()
  8.     Dim FilterCriteria As String
  9.     If cboCompany & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Company]='" & cboCompany & "'"
  10.     If cboUsername & "" <> "" Then FilterCriteria = FilterCriteria & " AND [Username]='" & cboUsername & "'"
  11.     If FilterCriteria = "" Then
  12.             Me.FilterOn = False
  13.     Else
  14.         FilterCriteria = Mid(FilterCriteria, 6) 'REMOVE THE LEADING " AND "
  15.         Me.Filter = FilterCriteria
  16.         Me.FilterOn = True
  17.     End If
  18. End Sub
(disclaimer!)
Note: Untried air code.

MTB

(ps. I know there is some redundant code in the first IF statment, but it does mean you can add criteria anywhere without worrying!!)
Jan 8 '09 #2

P: 5
This works perfectly. Thank MTB!
Jan 8 '09 #3

NeoPa
Expert Mod 15k+
P: 31,186
A tutorial related to this can be found at Example Filtering on a Form.

I doubt you'll need it as MTB has clearly answered your question, however other searchers may be interested hence the post.

Welcome to Bytes!
Jan 8 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.