Results 1 to 11 of 11
  1. #1
    axman1000 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    13

    How to perform an update query based on combo box and text box values?

    Hi! I'm new to this forum and new to MS Access too, so please bear with me

    This is what I have done so far:

    I have a form that tracks registration and accommodation details of participants attending a conference.

    I have two tables. One table contains a list of hotels, the number of rooms in each of them which is blocked for the participants, the number of rooms available per hotel (which should be updated as and when a participant books a room or two). The second table contains a column that has a lookup to the first one to get the names of the hotels, and another column to store the number of rooms booked by the participant.

    I created a form based on the second table's data structure (as it contains a lot of other details other than just the lookup column) and the column which is the lookup is the source of a combo box in the form. In addition, I've made the number of available rooms from the first table, a second column as the source of the combo box, but display only the name of the hotel. I have a text box which is bound to the second (hidden column) of the combo box, to show the operator how many spare rooms are there in the selected hotel.

    There is a small text box I have provided for the operator to enter how many rooms are needed to be booked.

    So far so good?

    What I want is this:



    When the value is entered, and the record is saved, the number of spare rooms in the first table must be updated as the total number of rooms minus (-) the total number of rooms booked for that particular hotel, so that the next time, the text box bound to the combo box will show the new value.

    How can I do this? I tried using a query, but to no avail. I tried progamming, but to little success. How can I do what I want to? I hope I have been clear in my very long question. Please help!

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...the number of rooms available per hotel (which should be updated as and when a participant books a room or two).
    You do not need a field to hold the rooms available since it can be calculated from: BlockedRooms-BookedRooms

    In general, calculated values are not stored in tables since they are dynamic. Since you do not need the field, you would not need the update query, just a select query or expression to do the calculation.


    The second table contains a column that has a lookup
    Although Access has the capability of having lookups at the table level, they are generally not a good idea. This site has more details of why they are not recommended. The lookups are best left for forms.

    In addition to your two tables you will need a table for registrants as well as what room(s) have been assigned/selected for each registrant.

    Will you have multiple events for which participants will need to register? I assume that the blocked out rooms will vary from event to event. Do you have tables to handle that?

  3. #3
    axman1000 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jun 2011
    Posts
    13
    Maybe I was a bit unclear in what I wanted to do. I have a dropdown on the form which gives the hotel's names. The source of the dropdown is a column in a table, which also contains the total number of rooms blocked in that particular hotel. When I select the hotel's name in the dropdown, a textbox should display the number of spare rooms. When I enter the number of rooms a participant wants, it should update the number of spare rooms and reflect the new value when a new participant wishes to register. How can I do this? I've tried queries and programming, but all to no avail. Can you please guide me and let me know how I can do what I want to do?

  4. #4
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    Quote Originally Posted by jzwp11 View Post
    In general, calculated values are not stored in tables since they are dynamic. Since you do not need the field, you would not need the update query, just a select query or expression to do the calculation.
    I tried a SELECT query which went like
    Code:
    SELECT Table1.No_of_rooms-SUM(Table2.Booked_rooms) FROM Table1, Table2;
    It gave the following error: "You tried to execute a query that does not include the specified expression Table1.No_of_rooms-SUM(Table2.Booked_rooms) as part of an aggregate function."

    Where am I going wrong?

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I understand what you are trying to accomplish, but without knowing more about your table structure it is hard to help. Would it be possible for you to post a copy of your database (with any sensitive data removed)?

    You can bring the total # of blocked rooms for the hotel into the combo box and then create another textbox on your form that grabs that value from the combo box and uses it in an expression. The expression would use the DCount() domain aggregate function to get the count of booked rooms from some other table

    =comboboxname.column(x)-DCount("*", "tablethatholdsbookings","hotelID=" & comboboxname)

    The above assumes that the bound field of the combo box is the primary key of the table that holds the hotel info.

  6. #6
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    THANK YOU! I will try this method and keep you updated. I will post the structure of the database shortly. Thanks a lot!

  7. #7
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    Sorry for the delay, but here are the tables:

    Accommodation Data
    ------------------------
    Hotel_Name (contains the names of the hotels)
    Total_no_of_Rooms (contains the number of rooms per hotel blocked. It is a constant value)
    No_of_Spare_Rooms (the pain in the ***! I need this to be updated after each successful registration)

    Registration Data
    --------------------
    Hotel_of_Residence (this comes from a combo box in the registration form. I took your advice and instead of making the column a lookup, I changed the column so as to get the name from the combo box directly. The combo box looks up the Hotel_Name from Accommodation Data and stores it in this field)
    No_of_Rooms (this is the number of rooms that a candidate has booked)

    The Form
    -----------
    In the initial design, I had used the name of the hotel and the number of spare rooms as the source of data for the combo box, having hidden the number of spare rooms, and having given that column as the source for a textbox to display instead. What I had in mind, as I am sure you know by know, was to have a query run whenever the number of rooms booked changed or was added, so that the updated value appeared the next time around. But this worked only for the first set of values, ie, if two people booked a total of 5 (3+2) rooms, then only 3 would be counted as the number of rooms, rather than than 5, which caused problems.

    I am yet to try your solution, but I'm afraid that I'm a complete noob and so you might have to give me step-by-step instructions. I'm sorry about this Please help me!

  8. #8
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since you are entering the number of rooms (i.e. 2, 3 etc.) rather than adding a single record corresponding to each room booked, the DCount() won't work, you will need the DSum() function instead. I've attached a simplified sample database that I created; hopefully you will find it of value.

    There is some code in the after change event of the textbox used to record the number of rooms needed to requery the available # of rooms textbox.

  9. #9
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    Thanks for that! I'll try out the formula and when it works, I'll mark this thread as solved. Thanks a lot!

  10. #10
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    Just had a thought.. What if the sum is more than the no of rooms? What if 26 rooms have been booked, when really, only 25 rooms were there. What can be done to prevent this from happening?

  11. #11
    axman1000 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    13
    Ok, so just to try your method, I created a text box and did
    "=[Accommodation_Data].[Total_No_of_Rooms]-DSum("No_of_Rooms","Registration_Data","Hotel_of_R esidence=" & [Combo217])"

    and when I opened the form in the form view, the text box said "#Name?". What is wrong?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update a text box based on a combo box
    By fleschnj in forum Access
    Replies: 1
    Last Post: 11-08-2010, 12:14 PM
  2. Replies: 1
    Last Post: 03-27-2010, 06:13 AM
  3. Replies: 2
    Last Post: 11-18-2009, 06:49 PM
  4. Replies: 1
    Last Post: 08-26-2009, 10:45 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums