Query based on form data

  • Thread starter Thread starter matty rastafairy
  • Start date Start date
M

matty rastafairy

I may be posting the same again, if so sorry but it said website too busy...

My agency represents many schools in many cities around the world. Each
school offers a similar range of English courses and each has diffferent
pricing structures
Eg School: Royal School Course GE1 Price: 1-6 weeks 200$, 7-12 weeks 180$
School: English School Course GE1 Price: 1-3 weeks 190$ 4-8 weeks 160$
etc..

I have the tables set up correctly and all the relationships are good.


I have form "orders" on which the user first selects "country" from
combobox1 which then filters combobox2 for "City" which in turn filters
combobox 3 "School" which then filters combobox4 "Course". All comboboxes are
unbound.

The user then enters "NoOfWeeks" and then runs the query based on the
"CoursesPerSchoolPerWeekPrice" table to find the "CoursePricePerWeek" and
these are the 2 values to show up on the query subform, the values are then
passed the "Orders" table and the Main form.

The total price is also calculated as "CoursePricePerWeek" x "NoOfWeeks".

So that all works ok.

What I want is, if the user has previously selected the info, when the form
is re-opened that instead of the comboboxes being empty they display the
Country, City, School and course that were previously selected.
(I dont really want to bind / store the values for Country, City, School and
Course as fields in my "Orders" table as they are sequentially linked,
however,I could do so and null all values on requery of any combobox if you
recommend it).
Im thinking that the same query could be modified with the
"CoursePricePerWeek" being the parameter and then used to populate these
comboboxes as default values on form load.

If that sounds logical (you guys read a lot worse and sense of it) then my
question is:
How do I code the form to set the parameters of the query to the value of
the comboboxes if it is a new query and to then change the parameter if the
"CoursePricePerWeek" is already known.
If it doesnt sound logical please advise me of something that does.

Thanks for reading.
 
I am presuming that the jist of your question is this: "...if the user has
previously selected the info, when the form
is re-opened that instead of the comboboxes being empty they display the
Country, City, School and course that were previously selected"

** having the form open to the most recent record can be implemented a
couple ways. I've never needed this so haven't implemented but you would
need a field in the source to hold a sequencial value or date stamp
(unfortunately autonumber is not guaranteed to always go up sequencially).
And then in your open form argument you would want to go to MAX of that field
value....
 
Hi, thanks for answering. That's not really what I want though. The form will
be opened from the Client details page so wont necessarily be the last
opened. The only info there will be is the "CoursePricePerWeek" and
"NoOfWeeks", from which I want to populate the comboboxes on open.
 
Back
Top