Subform datasheet populated based on combobox selection

[BRZ-Ryan]

I have a subform with a few comboboxes which will contain values from a few columns in my table. When I select a value in the combobox, I want the datasheet to populate with only records that have the matching value(s) selected in the combobox(s). I think I can do this manually with a SELECT statement and entering any relevant criteria, but before I did that I wanted to know of there was a built in way of doing something like this. The select statement I'm thinking of is adding a where val= condition if there is a combo selection. Anyone?

[Ruegen]

You could have the query of the subform ask for the combo box result by putting in the related id criteria forms!frmMain!comboxname, then an after update event on the combo box requery the subform.

so if you have fieldnameID in the subform query

and fieldnameID in the combobox (row source property of the combo box)

then whatever the combobox is the subform will be when it is re-queried

the combobox must be unbound.

[BRZ-Ryan]

Tried but no luck, I don't think I understand what you are saying. I am experienced with VB but new to access.

[Ruegen]

Tried but no luck, I don't think I understand what you are saying. I am experienced with VB but new to access.

You have a form, in datasheet mode.

The query of that datasheet can have criteria. The property of the form/datasheet has a record source.

If you edit that record source whether it be a select query or a query you can tell it to only show certain things in the criteria - that way your datasheet will only show what is in that criteria.

When you open a record source it opens the query builder and you can place in some criteria for the field

one way to filter what is on the form is to tell the query to have a combo box as the criteria

so say you have a field "tableID" (the autonumber of your table)

in it's criteria you can put the combo box control using forms!yourformname!yourcomboboxname

Now the combo box is the criteria for your form.

When you change what is in the combo box and refresh the datasheet after it has changed you will get the new results because you have changed the criteria for the form/datasheet's record source.

Now you have to have a similar value in the combo box.

That is why you click the combo box in design mode and edit it's row source property. This is where you see what is in the drop down part of the combo box. It's much like the record source. You need to add the same field (tableID) and maybe a second field so you can see something other than an autonumber like 1243 - instead you might want a word like "John" or "Samantha" to show in the combo box - this is where that second field in the row source helps. Note: you can't see the other fields unless you change the column properties of that combo box.

Lastly the form/datasheet won't refresh on change unless you place an event on that combo box so that when you change the value of the combo box it refreshes the datasheet. So put the code in vba using the after_update() property:

me.requery

This is a very simple way of filtering your datasheet.

You can also use filter properties using vba. Someone else might suggest it too.


Update note: I am assuming the combo box is in the header of the form/datasheet.

[June7]

A 'built-in' way is to use a dynamic parameterized query and requery the form with the Refresh button on ribbon. That does not require any code.

What Ruegan is describing is referencing the combobox as criteria in the subform RecordSource query. The RecordSource query would look like:

SELECT * FROM sometable WHERE somefield = combobox name;

Is the main form bound?

[BRZ-Ryan]

This form is separate from the rest of my project, I can make it be whatever it needs to. I just want a data sheet on the bottom populated with data based on combo box selections as criteria

[June7]

Two ways to set up the form.

1. A single form in Continuous view with combobox in form Header section, data controls arranged in Detail section to look like datasheet. This simplifies the RecordSource reference to combobox.
SELECT * FROM sometable WHERE somefield = comboboxname;

1. A form/subform arrangement. This requires RecordSource reference to combobox to be qualified with the main form name.
SELECT * FROM sometable WHERE somefield = Forms!formname.comboboxname;

Take your pick.

[BRZ-Ryan]

I want to use a real datasheet because I want it to list all records that have a field matching my combobox value for that field. Ie. this db holds all marketing events by my company; I have comboboxes for company name, city, state, zip, scheduled by, num employees etc-- they will each contain all data from that same field in the table. I want to select one or more of the combos and have the datasheet list records that match what I select.

This way we can quickly find all events done for a given company, state, zip, or a combination of these etc

[Ruegen]

I want to use a real datasheet because I want it to list all records that have a field matching my combobox value for that field. Ie. this db holds all marketing events by my company; I have comboboxes for company name, city, state, zip, scheduled by, num employees etc-- they will each contain all data from that same field in the table. I want to select one or more of the combos and have the datasheet list records that match what I select.

This way we can quickly find all events done for a given company, state, zip, or a combination of these etc

datasheet form unless I am corrected by June is what you need. Then there are queries - which you have to manually type in the criteria and tables. Forms are what you interact with. There is single, continuous and datasheet (update: there are more but start with that). Datasheet makes your form behave and look like a query - however you can lock fields to avoid accidental editing, and do all kinds of fancy things to it.

put the combo boxes you want on the form, name them (so you can easily reference them later) when you are in the form use the criteria in the appropriate fields. So if tblName has ZipID (that links to a table with say tblZipcode which has an id of all the possible zipcodes) then put in the criteria the appropriate combo box (name of it might be "comboZipcode") so forms!frmDataSheetForm!comboZipcode.

You may need to use nz() because when you open the form the combo box will be null until you pick a selection

so in the critieria

nz(forms!frmDataSheetForm!comboZipcode,tblName!Zip ID)

This way it will show the records if there is nothing yet selected in the combox (it is null until you use it).

For the zipcodes I would use a textbox over combo box and use "like" in the criteria (ask me if you want to use that and I will go into that) because you probably just type in the zip number as you add the client/customer for the first time however if you have a complete list of all the zip codes in a table already and you select the zip code when creating a customer then go with the combo box.

[June7]

Here are tutorials on parameterized queries:

http://datapigtechnologies.com/flash...tomfilter.html
http://datapigtechnologies.com/flash...earchform.html
http://datapigtechnologies.com/flash...mtoreport.html

[BRZ-Ryan]

I've tried doing this so many times but it will not work.

I created my form and added the subform\subreport (looks like datasheet). I created a query, included in it each piece of data I want to include in the datasheet. For the criteria I reference the form combobox for each item. When I select an item in the combobox, the datasheet does not update to list records with that piece of data in the given column. I tried adding query.Requery in the _click event of the comboboxes but it does not work. Can someone give me a play-by-play way of how to do this? When I run my query manually and type in what it's asking for it does return the correct data. It just will not link to my comboboxes.

[June7]

The videos are the best play-by-play I've seen.

Using a form/subform complicates.

Why don't you do what the videos demonstrate for a start to learn?