UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query One Field Or Another, Access 2010    
 
   
spo
post Mar 19 2017, 08:32 AM
Post#1



Posts: 92
Joined: 14-January 06
From: Ireland


Hi all, I have been trying everything to get this sorted and I'm stuck!

I have a school DB, a pupil record has the fields
Provision (Either Outreach or Placement)
Mainstream_School
Class

It sounds simple - I have a form with three combo boxes: Provision_Select, Mainstream_Select and Class_Select. I want a simple form to do the following via a query:
IF the user selects "Outreach" from Provision_Select combo, show the pupil records where Mainstream_School = Mainstream_Select Combo
BUT
If the user selects "Placement" from the Provision_Select combo THEN show the pupil records where Class = Class_Select Combo.
So it is do one query or another depending on a combo. Any help will be much appreciated

Barry
This post has been edited by spo: Mar 19 2017, 08:40 AM
Go to the top of the page
 
GroverParkGeorge
post Mar 19 2017, 09:20 AM
Post#2


UA Admin
Posts: 30,452
Joined: 20-June 02
From: Newcastle, WA


The technique you need is usually referred to as Cascading Combo Boxes. There are many examples, samples and demos in our Code Archives.

--------------------
Go to the top of the page
 
spo
post Mar 19 2017, 10:20 AM
Post#3



Posts: 92
Joined: 14-January 06
From: Ireland


I've had a look, and I don't think that's what I'm Looking for. I don't wish the combo boxes to depend on a previous choice - I would like one query which returns:

Those pupils for whom Mainstream_School is a match to the Mainstream Combo Only if the "Outreach" is selected in the Provision combo

Those pupils for whom Class is a match to the Class combo Only IF "Placement" is selected in the Provision Combo.

Thanks
Go to the top of the page
 
GroverParkGeorge
post Mar 19 2017, 10:54 AM
Post#4


UA Admin
Posts: 30,452
Joined: 20-June 02
From: Newcastle, WA


Well, yes, it will be a variation of the Cascading Combo box technique.

Instead of simply requerying the down stream combo box, use the same method to change it's row source and apply the filter.

--------------------
Go to the top of the page
 
GroverParkGeorge
post Mar 19 2017, 11:06 AM
Post#5


UA Admin
Posts: 30,452
Joined: 20-June 02
From: Newcastle, WA


Let me make that a little more explicit.

You need two queries, each of which has the appropriate criteria for the filter you want.

In the After Update event, you change the row source of the downstream combo box in the same way as the Cascading Combo Box method works, except instead of requerying, you change the rowsource to one of the two queries (which have the appropriate criteria in them).

--------------------
Go to the top of the page
 
projecttoday
post Mar 19 2017, 11:12 AM
Post#6


UtterAccess VIP
Posts: 8,382
Joined: 10-February 04
From: South Charleston, WV


Is the display you're talking about on a form? It's always either 1 or the other, right? In which case you don't need the first combo box. Just filter on the desired field in the after update event of the other 2.

--------------------
Robert Crouser
Go to the top of the page
 
spo
post Mar 19 2017, 11:44 AM
Post#7



Posts: 92
Joined: 14-January 06
From: Ireland


Brilliant!
thanks.gif
Thanks ever so much for your time. - It's working perfectly.

Barry
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2017 - 05:02 AM