Multi Combo boxes for 1 subform

[IamSam]

Basically I have a subform with 2 combo boxes. I want to be able to select
from each combo box to filter what is displayed on the form.

 

[John W. Vinson]

Basically I have a subform with 2 combo boxes. I want to be able to select
from each combo box to filter what is displayed on the form.

Ok. What problems are you having? What have you done so far? Where is the data
coming from?

 

[IamSam]

I have a query that pulls data from 3 tables. I want to display the data on a
subform. The data is broken down by year and state. The combo boxes are
unbound, but look up the value year or state, on my subform. The problem is
when i select year, the subform refreshes to the first state, (Delaware).
When I select state the year resets to 2006, the first year in the query. I
would like to have the combination of the 2 or more combo boxes filter what
is dispalyed on the subform. I do not know sql yet as I am teaching myself in
my spare time. This is the event procedure for the year. The same is for the
state. I have searched for an answer on the internet but it appears that I am
the only one that has this issue.
Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Year] = " & Str(Nz(Me![Combo33], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Ed Robichaud]

One of the simpler techniques would be to make your combo boxes unbound,
remove current code in the combo AfterUpdate events and replace it with:
Me!MysubForm.Requery
Create a query to use as the recordsource of your subform and set the
criteria of the query to something like:
IIF(Forms!MyForm!MyCombo is null, "*",Forms!MyForm!MyCombo)

Use something similar for each combo box.
This will filter the records in your subform to those that match the
criteria in your unbound comboboxes and the query will refresh if any combo
box value changes.

You could also use a command button to run the query with any criteria or
show all records.
-Ed

I have a query that pulls data from 3 tables. I want to display the data on
a
subform. The data is broken down by year and state. The combo boxes are
unbound, but look up the value year or state, on my subform. The problem
is
when i select year, the subform refreshes to the first state, (Delaware).
When I select state the year resets to 2006, the first year in the query.
I
would like to have the combination of the 2 or more combo boxes filter
what
is dispalyed on the subform. I do not know sql yet as I am teaching myself
in
my spare time. This is the event procedure for the year. The same is for
the
state. I have searched for an answer on the internet but it appears that I
am
the only one that has this issue.
Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Year] = " & Str(Nz(Me![Combo33], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Ok. What problems are you having? What have you done so far? Where is the
data
coming from?

 

[IamSam]

I am now getting a syntax error when I try to run the code you suggested Ed.
My combo boxes are unbound and I used the code you suggested.

One of the simpler techniques would be to make your combo boxes unbound,
remove current code in the combo AfterUpdate events and replace it with:
Me!MysubForm.Requery
Create a query to use as the recordsource of your subform and set the
criteria of the query to something like:
IIF(Forms!MyForm!MyCombo is null, "*",Forms!MyForm!MyCombo)

Use something similar for each combo box.
This will filter the records in your subform to those that match the
criteria in your unbound comboboxes and the query will refresh if any combo
box value changes.

You could also use a command button to run the query with any criteria or
show all records.
-Ed

I have a query that pulls data from 3 tables. I want to display the data on
a
subform. The data is broken down by year and state. The combo boxes are
unbound, but look up the value year or state, on my subform. The problem
is
when i select year, the subform refreshes to the first state, (Delaware).
When I select state the year resets to 2006, the first year in the query.
I
would like to have the combination of the 2 or more combo boxes filter
what
is dispalyed on the subform. I do not know sql yet as I am teaching myself
in
my spare time. This is the event procedure for the year. The same is for
the
state. I have searched for an answer on the internet but it appears that I
am
the only one that has this issue.
Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Year] = " & Str(Nz(Me![Combo33], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

On Wed, 26 Mar 2008 21:08:00 -0700, IamSam
<[email protected]>
wrote:

Basically I have a subform with 2 combo boxes. I want to be able to
select
from each combo box to filter what is displayed on the form.

Ok. What problems are you having? What have you done so far? Where is the
data
coming from?

 

[IamSam]

The combo boxes I am useing have year and state. If I do not change the code
for the combo boxes I am only able to have California as an option.

I am now getting a syntax error when I try to run the code you suggested Ed.
My combo boxes are unbound and I used the code you suggested.

One of the simpler techniques would be to make your combo boxes unbound,
remove current code in the combo AfterUpdate events and replace it with:
Me!MysubForm.Requery
Create a query to use as the recordsource of your subform and set the
criteria of the query to something like:
IIF(Forms!MyForm!MyCombo is null, "*",Forms!MyForm!MyCombo)

Use something similar for each combo box.
This will filter the records in your subform to those that match the
criteria in your unbound comboboxes and the query will refresh if any combo
box value changes.

You could also use a command button to run the query with any criteria or
show all records.
-Ed

I have a query that pulls data from 3 tables. I want to display the data on
a
subform. The data is broken down by year and state. The combo boxes are
unbound, but look up the value year or state, on my subform. The problem
is
when i select year, the subform refreshes to the first state, (Delaware).
When I select state the year resets to 2006, the first year in the query.
I
would like to have the combination of the 2 or more combo boxes filter
what
is dispalyed on the subform. I do not know sql yet as I am teaching myself
in
my spare time. This is the event procedure for the year. The same is for
the
state. I have searched for an answer on the internet but it appears that I
am
the only one that has this issue.
Sub Combo33_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Year] = " & Str(Nz(Me![Combo33], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


:

On Wed, 26 Mar 2008 21:08:00 -0700, IamSam
<[email protected]>
wrote:

Basically I have a subform with 2 combo boxes. I want to be able to
select
from each combo box to filter what is displayed on the form.

Ok. What problems are you having? What have you done so far? Where is the
data
coming from?

 

[John W. Vinson]

Basically I have a subform with 2 combo boxes. I want to be able to select
from each combo box to filter what is displayed on the form.

One alternative way to do this involves no query at all - just use the
Master/Child Link Fields properties of the subform control. Assuming you have
a combo box cboDate and another combo box cboState on the mainform, and fields
TheDate and State in the subform's recordsource, you can use

[cboDate];[cboState]

as the Master Link Field, and

[TheDate];[TheState]

as the Child Link Field. Access won't offer you control names if you use the
combo box wizard, but you can just type them into the property, and they will
work.