Access Help With Seating Chart

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

JeffHastern

Thread Starter
Joined
Nov 10, 2011
Messages
4
Hello people,

I'm having a problem (maybe a design problem?) with the Access 2010 database I'm trying to create. I'm attempting to create a database for taking reservations at a small family-style restaurant.

We have a number of tables, each of which holds a specified number of guests. A table may hold more than one party of guests, so a table that seats 14 can hold a group of 5 and a group of 9. To overcome the massive problems we have been having with messed up reservations, and seating issues, I decided to create an Access database to solve the issue.

The new reservation form allows the user to enter in the table number at the time the reservation is taken. But, instead of just entering a number in, I want to create a button that will pop up a form that displays the current seating layout and capacity for the chosen date, and allow the user to click on one of the available tables.

In order to do this, I created a query on the Reservations table that selects all the records where the date is = to the chosen date on the new reservation form.

That query collects the seating count based on that date, and then a query on top of that one gives the remaining seats for each table:
Table Number | Available Seats | SumOfGuestCount | Remaining Seats​

This lists all the tables and shows how many seats are left at each.

My plan was to create a number of unbound text boxes, one for each table, shaped and arranged on a form as they are in the restaurant, the content of each would be the numerical value of [Remaining Seats] from the query. That way the person taking the reservation could see exactly how many seats were remaining at each table. I was then planning to create an option group over the text boxes with transparent buttons so that the person taking the reservation could simply click on the table to place the current reservation there.

The problem I'm having is I'm unable to get the text boxes to display any value from that query. I tried entering a select query in the control source for each text box. So, for text box that represents table 11, I tried entering:
Code:
SELECT RemainingSeatsByDATE.[Table Number], RemainingSeatsByDATE.[Remaining Seats]
FROM RemainingSeatsByDATE
WHERE RemainingSeatsByDATE.[Table Number]="11";
But that gives me an error that I need to wrap my subquery in parenthesis (which I can't seem to figure out). If I try just selecting the builder, it gives me a #name error in the field since my subquery isn't the record source.

Can someone explain where I'm going wrong, and maybe suggest a better way to do what I'm trying to do? I've been unable to find an example of something like this no matter how much I search.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
JeffHastern, welcome to the Forum.
Can I suggest that we trake a look at using VBA code to do this?
 

JeffHastern

Thread Starter
Joined
Nov 10, 2011
Messages
4
Hi OBP,

I don't know how to write VB. I have managed to reverse engineer VB code in the past for previous databases, but I don't think I could handle anything lengthy or complex. The only times I've used VB have been for small things, like changing label colors based on selection, and stuff like that.

I'm certainly open to trying, but I'm concerned that it would involve too much hand-holding on your part.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
Hand Holding, or even complete writing is what I do, see this thread http://forums.techguy.org/business-applications/954425-customizing-primary-key.html
This VBA would be basically quite simple.
Open a query with the data for all the tables, use VBA to assign the values to your Unbound fields.
Unfortunately I can't work with Access 2007/10 and their forms do not lend themselves to reformatting to Access 2003.
So we will need to work through this a section at a time. First of all does your query currently have the data for all the Tables?
If not please create one and then post it's name and a list of the Fields on here.
I will then write some code for you to test which will open the query and read the data.
 

OBP

Joined
Mar 8, 2005
Messages
19,896
I am in the UK and it is late here, so I will have to respond to you tomorrow.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top