Using 2 combo boxes to filter a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
Click to expand...


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.
Click to expand...
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.)
Click to expand...

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]
 
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.
Click to expand...


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.
Click to expand...
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.)
Click to expand...

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]
Click to expand...
 
Back
Top