Solved

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

Posted on 2011-03-23
4
255 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 500 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

Independent Software Vendors: 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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

724 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