Solved

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

Posted on 2011-03-23
4
218 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
  • 3
4 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Perfect! Thanks!
0
 
LVL 15

Expert Comment

by:Simon Ball
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now