Solved

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

914 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

15 Experts available now in Live!

Get 1:1 Help Now