- Whether it is possible for a validation between tables so that if a value for PartySize in the Reservations table, for example, is entered, and it is inconsistent with that table's seating
capacity, then an alert will appear telling me this. e.g. If I am booking Table 7, which has a seating capacity of 4, for a party which contains 8 people,
then an alert will appear saying something to the effect of: This table's seating capacity cannot accommodate the party size. Please select another
- Whether it is possible to run some kind of query so that when I view a record from the Tables table, the Reserved (yes/no) field will be automatically
checked if there is an entry for that table in the Reservations table (to prevent constant manual checking between tables). I suppose a subform could
work (to solve the manual checking problem), but for the purpose of this assignment, we are only supposed to use one subform, and I already need to
use that for the MealOrder table.
- If there is a way to implement a query or whatever so that I enter data in the PartySize field of the
Reservations table, and any TableNo in the Tables table, whose Reserved (yes/no) field is set to No, and whose seating capacity is consistent
with the party size, will be automatically entered in the TableNo field of the Reservations table. For example, I enter 8 in the PartySize field of
the Reservations table, and table number 9, whose seating capacity is 8 or more, and which is not currently reserved, automatically appears in the
TableNo field of the Reservations table.
The short answer is yes, there's a few ways to do what you'd like. To fully answer that question we would need a detailed explanation of your booking process. Forms, queries, process etc.
A few options would be using unbound fields and using vba to check before inserting. Or using vba to check the field while you're entering the value and not allowing you to leave the field until a valid options is entered. There's a few ways to do this.
Here's a copy of my table structure for your convenience, thanks for your help. For the booking section, it will just be a regular form, with a query to search for tables according to certain criteria. Capacity, Location, etc will be parameter queries, whilst Occupied and Reserved must, for obvious reasons, be set to No. The user also needs to define the Date and Time (from the Reservations table) using parameter queries. I may not need a solution to the third question (simply because I remembered that I need to use a complex query to solve that issue, for the purpose of the assignment, and have already outlined that above) but any solution to the other two would be useful. Also, I have little experience with SQL and all those other languages and, whilst I'm happy to hear any solutions involving them, I probably need a Dummied-up explanation of how to implement them. Thanks.
Also (while I'm at it) I wonder how I can convert a query (or set of query criteria, such as the parameter queries above) to a form layout - e.g. in a Select Date AND Time AND Table capacity AND Location in a relatively simple way.
Also, when I tried setting up the above query, it worked fine, but when I added a parameter query for the user to define Smoking as Yes or No, and tried to run the query, MS Access told me the query was too complex. Any suggestions for this?
Your missing a key field "PartySize" from your reservations table. Once you have that in place, you then have some numbers to reference against Capacity.
You could apply some of the suggestions to these numbers that I gave you above. Eg, the unbound field method would essentially entail having an unbound field for the PartySize, then on the after update event for that field, you could add a bit of code similar to this:
If Me.PartySizeField > DLookup("Capacity", "TABLES", "TableNo = " & me.TableNo) Then
MsgBox "Party size is greater then table capacity. Please enter a new capacity"
Then if you have a "make reservation" button or what have you, you could add a bit of code to that button to insert the value of that unbound box into the actual table.
As far as the filtering and so forth, you may want to look into cascading combo/list boxes. If you search for cascading combo boxes in the forums, you should get a bunch of good info.