Solved

can I filter a subform table with combo boxes?

Posted on 2011-02-28
9
847 Views
Last Modified: 2013-11-28
Hello experts!

I have a table on my subform that I would like to filter based on combo boxes in the same subform. I have a table that is in datasheet view that shows a bunch of items and i want to be able to filter it based on criteria i select in a combo box.  For example, I have a combo box where I can choose obs2, and I want the table to show me the items that are associated with obs2.

thanks!
0
Comment
Question by:jtovar3
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 3

Expert Comment

by:sunezapa
ID: 35002600
you can set the datasource of the view.
in the combobox add an event, OnChange or AfterUpdate for exemple, and make the code in the style of:
 ctlView.RecordSource = "SELECT something FROM table WHERE record= '" & combobox.value & "'"

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 35003463
You can create a query for the subform instead of a table:

SELECT * FROM myTable WHERE obs Like " & Me!obs2 & "*" OR <the rest of the fields formatted the same>;

Now if nothing is entered, you will get all the fields - if something is entered in one or more of the controls, you will filter the subform accordingly
0
 
LVL 10

Accepted Solution

by:
t_hungate earned 500 total points
ID: 35009526
You can build a filter based on your combobox entries:
This example uses three comboboxes to build a filter for a subform.  Null values are allowed.  It will only filter on the criteria provided.

Function BuildFilter()
'Allow filtering of subform data using the mainform comboboxes

Dim sFilter As String

If Nz(Me.cmbo0, "") <> "" Then
  sFilter = "Cmbo_Name='" & Me.cmbo0 "'"
End If

If Nz(Me.cmbo1, "") <> "" Then
  If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
  sFilter = sFilter & " Cmbo1_Name='" & Me.cmbo1 & "'"
End If

If Nz(Me.cmbo2, "") <> "" Then
  If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
  sFilter = sFilter & " Cmbo2_Name='" & Me.cmbo2 & "'"
End If

Me.SubForm_Control.Form.Filter = sFilter
Me.SubForm_Control.Form.FilterOn = True

End Function

Open in new window


TLH
0
 

Author Comment

by:jtovar3
ID: 35027901
I love your suggestions, however, I don't know where to implement these. should i have the entire subform selected and change the source object? or do i go into the form and change the control sources. I've included a screenshot of what i am working with.

i would like the recommendations subform to filter based on the observation ID selected by the combo box above. I figure that i should put one of your suggestions in the after update of the combobox, but how do i reference the table below?



thanks! combobox subform filter
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 3

Expert Comment

by:sunezapa
ID: 35029809
if the subform-control is named "child1" and the combobox named combox1 then...

yes, on combobox1 After Update do one of following choices:
make a complete select-statemenet based on the value of combobox1
Select case combobox1.value
   case 1
       sql=" Select something From tableA WHERE something = whatever
end select

Open in new window

or add the combobox1-value to a Select-statement
sql=" Select something From tableA WHERE something =" & combobox1.value

Open in new window


and next step: use the Select-statement as the source for the subform
subformCtl.Form.RecordSource = sql
subformCtl.Form.refresh

Open in new window

the .refresh may not be necessary...

this gets the subform loaded without having to load all records (that access has a bad tendency to do).

the Filter will be just the same, except not setting the .RecordSource but .Filter and the value will be only the "WHERE something=" & combobox1.value
0
 
LVL 10

Expert Comment

by:t_hungate
ID: 35030981
Can you post an example of your DB.  That way we can get all your control names and provide you with accurate code to produce your desired effect.

TLH
0
 

Author Comment

by:jtovar3
ID: 35031400
Hi TLH,

Here is a sample of the database, in the combo subform, I would like to use the Observation ID combo box (ObsCombo) to update the Recommendation Table to only display the recommendations for a given observation ID.

Thanks!
AuditTrackerVersionTest.accdb
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 35047197
The combo boxes should not be in the subform.  Put them in the main form.  Combo boxes in the subform would be used to change the value of a field in the current record.  See my Fancy Filters sample database for an illustration of filtering a suform by values selected in combo boxes in the main form.  Here is a link for downloading it:

http://www.helenfeddema.com/Files/accarch129.zip

And here is a screen shot of the form:

Fancy-Filters-Form.jpg
0
 

Author Comment

by:jtovar3
ID: 35072942
Wow Helen, your Fancy Filters Form is amazing, but I think it is much more than what I am trying to do. I would just simply like to sort the subform after the ObsID combo box is selected.

I just don't know what to in order to set the filter for the subform table and whether to put something in the afterupdate of the obsID Combo box.
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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
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.

747 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

16 Experts available now in Live!

Get 1:1 Help Now