Question Simple Problem That I Can't solve

[Bob Ross]

Hi guys, I've got my ICT GCSE courcework to hand in on monday, and I thought I did it all to later find I did the database section wrong. And no I'm not a douche bag as I don't want you to do it for me, but I've had my head in access books all week trying to figure this out.

I am asked to design a system for a coach driver to find a list of passengers based on their Tour Code Collection Point and Dietary Code.

The tour code represents the the tour that the passenger is going on, this is a number stored as text, the values are either 351, 352, 353 or 354.

The collection point is like the bus stops of each tour, they are 4 collection points for each tour. This value is also stored as text but is a number. Either 1, 2, 3 or 4.

The dietary code is a number stored in a text field that represents the passengers dietary requirement such as vegetarian. The values are 1, 2, 3 and 4.

Now I am asked to produce a report of the passengers, but the driver has to type in the tour code and collection point to filter the passengers, for when he is picking them up.

I am also asked to produce another report of passengers (one for each tour code), and the driver has to type in the collection point and dietary code to filter the passengers.

So I have created quiries for each report, and in the criteria of the queries I use the square brackets for the tour code, collection point and dietary code to bring up the window that allows the driver to input the value, and it works.

The problem is when I was asked to test the system by entering collection point 351 and all dietary codes except 1 (so thats 2, 3 and 4). I can't enter multiple values in the window that pops up, and I ccan't use <> in the window along with any other syntax. I don't lmopw what to do and it's stopping me from working!!!

Please help, or point me to some usefull informaion. Thank you in advance.

-Bob Ross (P.S. thats not my real name )

 

[PNGBill]

You off course also have a PassengerTable.

Sounds like your only problem is how to get the more then One Dietary Codes in a form.

If it was Dates then you would put this in your Query Field Criteria.
Between[Enter Start date]and[Enter End Date]
and when the runs a pop up will ask the first question and then the 2nd question - no input fields required on your form.

If there is such thing for the first and last Dietary Code then this will work but what if you want 1, 3&4 - not 2??

VBA code could store the dietary Codes required and a message box will open up asking if you have any more, yes/no and if no then get on with selecting the data that matches the codes you entered.

Sorry if I don't have the "how to do it" for this action but maybe Google can help and or an expert will come and solve it for you.

 

[PNGBill]

This site refers to multiple select list box - does it assist?

http://www.ozgrid.com/VBA/multi-select-listbox.htm

 

[Bob Ross]

VBA code could store the dietary Codes required and a message box will open up asking if you have any more, yes/no and if no then get on with selecting the data that matches the codes you entered.

Yeah, that seems like the best way to do it. So in the query:

1) Ask for tour code
2) Ask for dietary code
3) Ask for any more dietary code (enter a number of how many more - or leave blank for no more)
4a) If left blank carry on with processing the results
4b) If a number up to 3 is entered ask that number of questions all asking for a dietary code, and then continue processing the results

But I don't know what to do from 3 onwards. could someone please shine some light? I have never used VBA but I am experienced in C++ and C# if that helps