How to use drop-downs in a form to filter records?

[Ed from AZ]

Let's say I have Product, Size, and Color. Not all products are in
all colors, and not all sizes of the products come in all colors. So
I would like to drill down - like the action of the AutoFilter in
Excel - in a form using drop-down controls.

I can use the Product drop-down to select Widget. Widgets only come
in small and large, so the Size drop-down will only give me those
choices and not include medium and XL. The large widget only comes in
red and blue, so I won't see green and black.

Can someone give me the "For Dummies" explanation of how to do this?
Is there possibly an on-line tutorial that can walk me through this?

Ed

 

[Pat Hartman]

The for dummies summary of cascading combos:
1. The RowSource of each dependent combo should be a query that has criteria
that refers to its "parent" combo.

For Cbo2:
Where SomeField = Forms!yourform!cbo1
For Cbo3:
Where SomeField = Forms!yourform!cbo2

2. The AfterUpdate event of each "parent" combo needs to include code to
requery its immediate "child" combo and to set its "grandchildren" to null.
So in a three-tiered setup:

For Cbo1:
Me.cbo2 = null
Me.cbo3 = null
Me.cbo2.Requery

For Cbo2:
Me.cbo3 = null
Me.cbo3.Requery

3. You may also need to requery all three combos in the Form's Current
event.

Me.cbo1.Requery
Me.cbo2.Requery
Me.cbo3.Requery

 

[Ed from AZ]

Thanks for the help, Pat.

"cascading combos" - sounds like a dance! 8>)

I knew there had to be a query behind it somewhere, but I didn't know
how to fire it. Or how many queries there should be. I should be
able to get the results into the next combo box - I have managed to do
that already from another query. (I'm quite new at this, if you
haven't caught that yet!)

Let's see if I have this right:
-- I need to set up a query for the data I want to sort.
-- The result of the first combo box gets set as the criteria for the
first term of the query, and the AfterUpdate event of that control
updates the query.
-- The values of the second combo box are linked to the the filtered
results of the query's second field.
-- The selected value of the second combo gets set at the query's
second criteria term.
-- And we repeat as long as we have combos.

I've done VBA in Word and Excel, and I've been told macros and VBA in
Access are quite a different animal. I assume the code for the
AfterUpdate events goes in the form?

Ed

 

[Pat Hartman]

To refer to the combos in queries, you will need to have the first column be
the bound column. You can make its width 0 so that it is hidden. This is
because the query can only refer to the default property of a control which
is the .value property. If you bind the combos to something other than the
first column, you may have trouble referencing the control.

- the AfterUpdate events do not update the RowSource queries, the pseudo
code I gave you reruns the query so that the selection criteria value is
current.
- the RowSource of each of the dependent combos, refers to the value in the
most immediate parent combo.

Macros in Word and Excel are used primarily to automate repetitive interface
tasks. Macros in Access are intended to accomplish actual programming tasks
which is why there is no recorder but the actual actions you can do with a
macro are quite limited.

VBA is VBA. The biggest difference is the object models of the various
products. To my mind, the Access object model is the most understandable.
It is also the smallest. I have done a fair amount of Word automation and I
always feel like I've got a blindfold on and I'm trying to poke data into
holes with a stick without being able to name those holes as you can with
Excel and Access. Excel is somewhat better but with Access, you always have
direct control of your data.


Thanks for the help, Pat.

"cascading combos" - sounds like a dance! 8>)

I knew there had to be a query behind it somewhere, but I didn't know
how to fire it. Or how many queries there should be. I should be
able to get the results into the next combo box - I have managed to do
that already from another query. (I'm quite new at this, if you
haven't caught that yet!)

Let's see if I have this right:
-- I need to set up a query for the data I want to sort.
-- The result of the first combo box gets set as the criteria for the
first term of the query, and the AfterUpdate event of that control
updates the query.
-- The values of the second combo box are linked to the the filtered
results of the query's second field.
-- The selected value of the second combo gets set at the query's
second criteria term.
-- And we repeat as long as we have combos.

I've done VBA in Word and Excel, and I've been told macros and VBA in
Access are quite a different animal. I assume the code for the
AfterUpdate events goes in the form?

Ed

 

[Ed from AZ]

Pat:

I'm not sure I can get this to work on my limited understanding.
Would it be easier to simply select the values I need from combo boxes
on my form, and then use code with a CommandButton to run one query
with those values to return the record I need? Something like:

With qry1
.Criteria(1) = cbx1.Value
.Criteria(2) = cbx2.Value
.Criteria(3) = cbx3.Value
.Run
End With

with the values showing up in a subform that becomes visible when the
query is run? (subfrm.Visible = True after the End With)

Ed

 

[Pat Hartman]

The muddy point of my first paragraph is that SQL and VBA are separate
languages and although there are some VBA elements that can be referenced in
Access' SQL, not everything will work. You can reference form fields using
the syntax - Forms!formname!controlname and you can reference user defined
functions but you cannot reference control properties such as the .Column
property of a combo's RowSource.

I'm afraid I don't understand your most recent question.

Pat:

I'm not sure I can get this to work on my limited understanding.
Would it be easier to simply select the values I need from combo boxes
on my form, and then use code with a CommandButton to run one query
with those values to return the record I need? Something like:

With qry1
.Criteria(1) = cbx1.Value
.Criteria(2) = cbx2.Value
.Criteria(3) = cbx3.Value
.Run
End With

with the values showing up in a subform that becomes visible when the
query is run? (subfrm.Visible = True after the End With)

Ed

 

[Ed from AZ]

Pat:

I'm afraid I don't understand your most recent question.

I probably don't understand enough yet to ask an intelligent
question. I know what a form, table, and query are, and I can put a
combobox control on a form that has the values from a table or query.
but I've never worked with any kind of control event code in Access to
obtain a control's value and initiate an action using that value.

Having done so in Word and Excel VBA, I kind of get the gist of your
explanation. But having no reference points in Access, I can't put it
into my database and make it work.

I'll do some more reading and look into the Access object model, then
post back with more questions that (hopefully) are more meaningful.

Thanks so much for your time and efforts.
Ed