need similar solution to "cascading combos"...

[Guest]

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

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

OK. So your unbound combo boxes should have queries in their row source. If
you open the properties sheet 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

 

[Guest]

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.

OK. So your unbound combo boxes should have queries in their row source. If
you open the properties sheet 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

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

 

[Guest]

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

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.

OK. So your unbound combo boxes should have queries in their row source. If
you open the properties sheet 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

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

:

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?

:

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?

:

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.

:

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

 

[Guest]

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

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

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.

OK. So your unbound combo boxes should have queries in their row source. If
you open the properties sheet 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

:

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

:

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?

:

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?

:

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.

:

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

 

[Larry Linson]

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.

The answer to your basic question is "No, you cannot set the RowSource of a
ComboBox to a Recordset." You can set the Row Source to a Table, a Query,
or a Value List.

However, if you would try to not be so "generic" in your description,
perhaps someone could offer a useful suggestion. Unless you fear that you
are going to reveal a trade secret or confidential data, tell us what data
you have, how it is laid out in tables, and what you are trying to
accomplish.

Also "Filter" is a very specific term in Access, and I think you are using
that in a generic sense, as well. If you were actually using Access' Filter
feature, the solution would likely be different than what you'll need to do
to with Combo Boxes.

What you've described so far is _how_ you want to do something, and you want
to do it in a way that you can't do in Access. But, almost certainly, if
the data content is limited based on one or more Combo Boxes, you _can_
restrict later choices to the already-selected subset... if, indeed, that is
what you want to do. Others, and I, do that frequently.

This may, indeed, be easy to accomplish in Delphi, but Delphi is a
programming language, not a database -- so there'll be more to the "Delphi
environment" than just Delphi, and, AFAIK, there's nothing like Access'
flavor of Combo Boxes available to Delphi. It was possible in classic VB to
emulate the AutoExpand feature of Access Combo Boxes, but it took some
programming to do so -- programming not needed in Access. And, as we say to
many who want Access to be, or to work like, some tool they've used before,
that kind of expectation can lead to nothing but frustration.

We wold really like to be of help, but we need more specific information
than what you provided. Perhaps having the answer to that basic question
will get the discussion "off dead center."

Larry Linson
Microsoft Access MVP

 

[Guest]

Hello Larry,

You missed one option that just may be the thing I was looking for: you
can create a user-defined function for the rowsource type property which
allows for virtually any kind of data to populate a combobox, including a
recordset. I found the example for it (mislinked in the help file) on the web
and it looks quite straightforward. If anyone is interested in browsing it,
here's the link.

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarofftalk/html/office04062000.asp

Thanks for assistance everyone but I fully expect this to the be solution
that will hold me in good stead.. if not, I'm sure I'll be back on here to
let everyone know . And yes, you are right Larry, there are some very nice
aspects to Access which I like very much. That's why I was suprised to find
that I couldn't do something that should be atleast possible, if not obvious.
I learned from Delphi programming that if I sniff around, there usually IS an
easier and better way to do something.

dave

 

[Larry Linson]

Yes, you are correct - I did overlook that option. You can populate the list
using a "callback function" (the only place in Access, I think, that a
callback can be used) and I have done that before (in the somewhat distant
past -- Access 2.0 days... to create a different list of optional insurance
amounts for specific employee position codes). I am certain that I'd
approach the problem differently today.

That's not quite the same as setting it to a recordset, but the values are,
I'm sure, what is important.

But, unless you are doing some really unusual and complex logic, there is
likely an easier way. Want to clarify the information and see if someone
can make a good suggestion?

Thanks for following up and catching my mis-call.

Larry Linson
Microsoft Access MVP

 

[Guest]

You were right in your first assertion Larry: I am trying to do in Access
something that was easy (and very useful) to do in Delphi. Basically I
require the freedom to to use recordsets with list controls, the only other
way I see to use recordsets is to iterate through the records and populate
the combo manually, but that's a dicey proposition in Access2000 (Access2000
combos have a 2048 character limition in Value mode). My current project is
becoming too complex without it. This callback function doesn't look that bad
to me. I will try this in my project and if it works, I'll compile a demo
project. Thanks for taking an interest in my case.

 

[Larry Linson]

No, no... you are still telling us _how_ you want to solve the problem, not
what problem you are trying to solve. A statement of a problem would be
something like "the user wants to be able to select available homes by size
range, then by number of bedrooms, then by distance from a school..." not
"the user wants to be able to click a combo box to....".

Unless it were homework for a class, and there's no indication that it is,
no business requirements are going to be to "use recordsets with list
controls". That would be an implementation technique to solve some "real
business problem".

Becoming fixated on a particular implementation techniqe can lead to serious
frustration; the most successful application developers in my rather long
experience focus on the business problem and then use the tools at hand to
solve it in a way compatible with the tools.

If the callback procedure isn't as applicable, or as easy, as you
anticipate, post back here telling us what you are trying to accomplish and
maybe someone can help. In any case, good luck with your project.

Larry Linson
Microsoft Access MVP

 

[Guest]

I've already explained it as best as I know how Larry. It's a search form
with three combo's looking at 3 lookup tables related to the main table of
shipping rates. I want to limit the view to a certain trucking carrier,
trucker destination, or trucker origin (with some other complicating
factors). Once a combo is selected, the other combo's need to limit their
view to what's viewable in the main table so the user isn't always picking
combinations that result in a null result set (this is a pretty standard
search form design pattern, no?).

I do this by changing the other two combo's (I.E. the ones that are still
blank) rowsource properties to look at the main table with a SQL query using
the DISTINCT keyword. (I also color code the "master combo" so it's less
confusing). I thought that if I could query the main table's filtered
resultset, I'd be saving myself a lot of work (since the query would be
"Select Distinct * from recordset").. but it's not possible, atleast with
DAO. I don't think it is with ADO either though.

I've since found that the original way I did it (and recommended by MS)
is the easiest way to do it. But I also learned that in Access 2002 and
later, you can set list controls to in memory recordsets.. ( but it did have
a bug which was how I learned about it).

I think I learned what I wanted to know.. and you are certainly right. You
can really beat your head against a wall when becoming insistent on doing
things a certain way.. fortunately, I am between jobs and don't have users
breathing down my neck on this one .

Dave