RE: need similar solution to "cascading combos"...

Not at all, it is kind of complicated to explain. If I find the solution,
I'll post it here so sign up to be notified if there's another response to
this thread. Thanks for all your efforts Beetle and keep playing those '60's
pop tunes :-).

Dave

"Beetle" wrote:

Aha! Now I see what your saying. I'm sorry, up til now I haven't quite
understood what you were asking, but now I do. I suppose I've just been
telling you a lot of things you already know. Unfortunately, I don't know a
solution to your problem. I do think that's an interesting question though,
wether you could somehow use a filtered recordset clone (or something llike
that) as the basis of a query. Hopefully, one of the real Access gurus will
pick up on this thread and offer a solution.

Sorry if I gave ya carpel tunnel with all that unecessary typing :-)

Beetle

"David J. Smith" wrote:


I appreciate the time effort you spent on the reply Beetle, but I have
something similar to that already. In fact, it solves the problem you mention
about having to select a certain combo first by switching the rowsource
queries in code. It works like this:

All combo's have a full view into their respective tables when blank.
When the user selects something in the first combo, it's afterupdate event
changes the other two combos rowsource queries to a query that selects from
the subform table but using the other combos text in a "where" clause
(similiar to your example). Each combo has a similar afterupdate event. I
color code the combo that becomes "master" in each case so it's less
confusing. The clear button resets everything to the way it was at
design-time. It works but is complicated to engineer. . What I am asking is
the following:

Is there a way in code to access the subform's recordset in a query. The
subform already has the filtered view I'm looking for so I'd like to
reference it just like you reference any table or query created at
design-time. It would be much easier than creating a bunch of complex SQL
strings for each combo that are basically re-inventing the wheel each time.

"Beetle" wrote:

OK. So your unbound combo boxes should have queries in their row source. If
you open the properties *** for a combo box and click the elipse next to
the row source, it will take you to design view of the query.

So let's say that Combo1 has a query that includes CustomerID and
CustomerName from tblCustomers. CustomerID is the bound column and
CustomerName is the column that is actually displayed in Combo1.

You would then open the query design for Combo2 and add a column for
CustomerID if it's not already there. Even if Combo2 gets it's info from a
different table, you can still add CustomerID from the Customers table as
long as the two tables are related by that field. Keep in mind that if you do
add columns to a query you may also need to change the Column Count and
Column Widths in the properties tab for that combo box. Then in the criteria
row of the CustomerID column for Combo2 you would put;

Forms!NameOfYourSearchForm![Combo1]

This will cause Combo2 to filter it's data based on the value in Combo1

Then in the After Update event of Combo1 you need to put;

Me.Combo2.Requery

This will re-run the Combo2 query whenever someone selects a new value in
Combo1

This method should work well as long as you expect the users to always
select a value in Combo1 first, then Combo2, then Combo3, etc. If you have a
situation where a user might skip over Combo1 an try to select something in
Combo2 first then this method will cause problems. If that's the case, repost
and we'll have to try a different approach.

HTH

"David J. Smith" wrote:

Yes that's absolutely correct Beetle. I've been rebuilding the queries for
each combo on it's afterupdate event to accomodate the new conditions and
limit its dropdown to only records that are available in the current subform
view. That way users won't pick combinations that result in no records.

My thinking is, if there were some way to use the subForm recordset in my
query to start with, my SQL queries wouldn't be so hairy looking (they
usually end up being inner joins because of the table relationships). It was
easy to do in the Delphi environment so I figure there must be a way in
Access but maybe isn't so obvious. I tried using the subform recordset in a
combo rowsource query but not sure if I did it right. It came up empty but
with no runtime errors.

Dave

"Beetle" wrote:

I created a search form that is similar to what your are trying to do (I
think). I also based mine on the Allen Browne example but instead of text
boxes, I used several unbound combo boxes for Customer Name, Product Name,
Location, etc. Each combo uses a select query to pull information from a
particular field in an underlying table. The user can select values in the
combo boxes (no free form entry), then they click a search button an the
subform populates with records that have matching data for any criteria they
selected in the combo boxes.

I think this is basically what you are trying to do, except that you want
your combo boxes to filter based on one another (which my combo boxes don't
do).

Is this correct?

"David J. Smith" wrote:

Hi Beetle,

Thank you for the reply.

Funny you mention that article because I used it as the basis for my
search form. Those controls are free form text entry.

What I am trying to do is set up the combo's so that they select from the
data in the filtered view. The first combo shows all the options because
there's no filter applied. Once the filter is applied I want the other
combo's to reduce what you see to what's available in the filtered view (only
show products that are in the current view). For each combo I am having to
build a complex filter based on the other combo texts, table relationships,
etc. It seemed to me that it would easier if I could just access the form's
recordset (in code ) since it has the view that I'm after already. Does that
make sense?

"Beetle" wrote:

If you want a form with several combo boxes, where the user can select values
from some or all of the boxes, then have the results filtered based on what
was selected, you will find a good example at Allen Brownes website at the
following link;

http://www.allenbrowne.com/ser-62.html

The code can be modified to work in your DB, however, that might be a little
complicated if you are unfamiliar with VB. It involves building a rather
lengthy string statement based on whichever combo boxes have a selection. The
code is well documented with explanations of how it works, so you might at
least give it a try.

HTH

Maybe someone else will post with a simpler solution.

"David J. Smith" wrote:

I'm creating a search form w/ unbound combo boxes that create a filter on the
subform. Can I use the filtered view of the subform as the starting point for
my query? Like clone that recordset and reference it in code from the combo
boxes? I'm a newbie in Access so don't know VB well enough mess around with
recordsets in code yet.

Dave


.