?
Solved

Having trouble filtering a table based on a value in a combo box

Posted on 2011-03-23
4
Medium Priority
?
257 Views
Last Modified: 2012-05-11
In my ComboSubform, I currently have AuditName Combo Box filter the Observation ID combo box. I want to be able to take the Observation ID selected in that combo box and have that filter the records that are displayed in the recommendations table below it. For example i want to select Testing as audit name then select Observation ID 293, then have the table below only show the recommendations associated with Observation ID 293.

I've tried to search for where to put in a filter, but I can't seem to find it.
AuditTrackerVersionTest.accdb
0
Comment
Question by:jtovar3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 35200052
Private Sub ObsCombo_AfterUpdate()
Me.ComboSubform_Rec.Form.Filter = "obs_id = " & Me.ObsCombo.Value
Me.ComboSubform_Rec.Form.FilterOn = True
Me.ComboSubform_Rec.Form.Refresh
End Sub
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35200071
added the above to the obsCombo on the form.

please find example attached

you need to think about an if in each update wher each combo value is null or ""



AuditTrackerVersionTest.accdb
0
 

Author Closing Comment

by:jtovar3
ID: 35200712
Perfect! Thanks!
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35205161
These two should handle it for you where the relevant box is changed from "something to nothing"..
Private Sub ComboAudit_AfterUpdate()
If (ComboAudit.Value <> "" And IsNull(ComboAudit.Value) = False) Then
   On Error Resume Next
   ObsCombo.RowSource = "Select tbl_Observation.Obs_ID, tbl_Observation.Company_Name " & _
            "FROM tbl_Observation " & _
            "WHERE tbl_Observation.Audit_ID = " & ComboAudit.Value & " " & _
            "ORDER BY tbl_Observation.Obs_ID;"
Else
ObsCombo.RowSource = "Select tbl_Observation.Obs_ID, tbl_Observation.Company_Name " & _
            "FROM tbl_Observation ORDER BY tbl_Observation.Obs_ID;"
            
End If
End Sub


Private Sub ObsCombo_AfterUpdate()
If (ObsCombo.Value <> "" And IsNull(ObsCombo.Value) = False) Then
Me.ComboSubform_Rec.Form.Filter = "obs_id = " & Me.ObsCombo.Value
Me.ComboSubform_Rec.Form.FilterOn = True
Me.ComboSubform_Rec.Form.Refresh
Else
Me.ComboSubform_Rec.Form.Filter = ""
Me.ComboSubform_Rec.Form.FilterOn = False
Me.ComboSubform_Rec.Form.Refresh
End If

End Sub

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question