Using 2 combo boxes to filter a subform

[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

 

[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]

 

[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

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]

 

[John W. Vinson]

And yes, I have my database created properly.

ah... sorry, JW, I have seen your name before and should have known.

John W. Vinson [MVP]