Using 2 combo boxes to filter a subform

J

JWCrosby

I would like to filter a subform usint the content of two combo boxes. My
database has two table:
tblCities
Fields, ID (primary), City,State

tblMeasures
Fields: ID, Year, Measure1, Measure2, etc.

Relationship is ONE (tblCities) to MANY (tblMeasures) on the ID field.

In a form I have the following: One combo box selects the year. Its record
source is a "Select Distinct" SQL statement from tblMeasures.

The other combo box selects the state; also a "Select Distinct" SQL
statement from tblCities.

I have the two combo boxes in the form header along with a button called
"Refresh."

I want to be able to select a year, then select a state, then click the
Refresh button and have the subform repopulate accordingly. (Subform is a
continuous form.)

I've read several posts and other sites but haven't found a solution yet.

Thanks in advance.

Jerry
 
J

John W. Vinson

I would like to filter a subform usint the content of two combo boxes. My
database has two table:
tblCities
Fields, ID (primary), City,State
ok...


tblMeasures
Fields: ID, Year, Measure1, Measure2, etc.


OOOPS!

Measure1, Measure2, etc. has a strong unpleasant odor of Spreadsheet. "Fields
are expensive, records are cheap" - should this not be a real one-to-many
relationship with each Measure in its own record?

I'd also suggest changing the name of the field Year - that's a reserved word.
Relationship is ONE (tblCities) to MANY (tblMeasures) on the ID field.
In a form I have the following: One combo box selects the year. Its record
source is a "Select Distinct" SQL statement from tblMeasures.

The other combo box selects the state; also a "Select Distinct" SQL
statement from tblCities.

I have the two combo boxes in the form header along with a button called
"Refresh."

I want to be able to select a year, then select a state, then click the
Refresh button and have the subform repopulate accordingly. (Subform is a
continuous form.)

You can set the Subform's Master Link Field to

cboCity; txtYear

and the Child Link Field to

ID; [Year]

This should keep the subform in synch based on the two fields.

John W. Vinson [MVP]
 
J

JWCrosby

Thanks, John. I got it to work properly.

And yes, I have my database created properly. That "Measure1" and
"Measure2" were not meant to indicate two measurements of the same thing, but
rather "the measure of one thing" and "the measure of another thing". It was
just my quick typing without thinking. Those aren't the actual field names.

Jerry

John W. Vinson said:
I would like to filter a subform usint the content of two combo boxes. My
database has two table:
tblCities
Fields, ID (primary), City,State
ok...


tblMeasures
Fields: ID, Year, Measure1, Measure2, etc.


OOOPS!

Measure1, Measure2, etc. has a strong unpleasant odor of Spreadsheet. "Fields
are expensive, records are cheap" - should this not be a real one-to-many
relationship with each Measure in its own record?

I'd also suggest changing the name of the field Year - that's a reserved word.
Relationship is ONE (tblCities) to MANY (tblMeasures) on the ID field.
In a form I have the following: One combo box selects the year. Its record
source is a "Select Distinct" SQL statement from tblMeasures.

The other combo box selects the state; also a "Select Distinct" SQL
statement from tblCities.

I have the two combo boxes in the form header along with a button called
"Refresh."

I want to be able to select a year, then select a state, then click the
Refresh button and have the subform repopulate accordingly. (Subform is a
continuous form.)

You can set the Subform's Master Link Field to

cboCity; txtYear

and the Child Link Field to

ID; [Year]

This should keep the subform in synch based on the two fields.

John W. Vinson [MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top