1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access Help With Seating Chart

Discussion in 'Business Applications' started by JeffHastern, Nov 10, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. JeffHastern

    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.
     
  2. OBP

    OBP

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

    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.
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    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.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I am in the UK and it is late here, so I will have to respond to you tomorrow.
     
  6. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1026292

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice