Re: How to in query
- From: ryan.fitzpatrick3@xxxxxxxxxxx
- Date: Fri, 3 Oct 2008 09:51:51 -0700 (PDT)
Marshall,
thanks, I actually this allen brown example for the basis of my
filtering. Like i mentioned the filters work I just wanted to add
alittle extra user friendly feature. basically I wanted to have the
supplier cbox to show all suppliers, and only when a plant is
selected, the a filtered list of suppliers for that plant will show,
thsi way a person can search for any supplier or any supplier linked
to that selected plant.
On Oct 2, 3:38 pm, Marshall Barton <marshbar...@xxxxxxxxxx> wrote:
ryan.fitzpatri...@xxxxxxxxxxx wrote:
I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.
In row source here is the SQL for cboxSupplier.
[Forms]![frmItemVolumeSpend]![cboxPlant])
This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code
[tblAdageVendors]![en_vend_key]
This code pulls a table that has all active suppliers for all plants.
But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.
IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])
This doesn't work, but does what I want make sense?
I'm not sure it makes sense, but even it it did, the
criteria expression would be near incomprehensible.
Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.
Yes, these are all possible, but to be any kind of efficient
and understandable, you should use code to construct the row
source SQL statement. For a good example of code to build a
set of optional where conditions, seehttp://allenbrowne.com/ser-62.html
It may seem a little untimidating at first, but ince you get
the idea, it's fairly straightforward. Try to gain an
understanding of the example code and come on back if you
need help adapting it to your combo box scenario.
--
Marsh
MVP [MS Access]- Hide quoted text -
- Show quoted text -
.
- Follow-Ups:
- Re: How to in query
- From: Marshall Barton
- Re: How to in query
- References:
- How to in query
- From: ryan . fitzpatrick3
- Re: How to in query
- From: Marshall Barton
- How to in query
- Prev by Date: Re: How to in query
- Next by Date: Re: How to in query
- Previous by thread: Re: How to in query
- Next by thread: Re: How to in query
- Index(es):
Relevant Pages
|
