Solved

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

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - finding skipped numbers 11 23
zero pad a access form field 2 15
Column Layout in Access Xp VBA Report 3 19
Help with DoEvents 8 25
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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…
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.

786 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