Selection in one field determining the selection of another...

[SilverFang]

Hi,

Haven't used Access in a very long time, probably 10 years now, so have forgotten most of what little I knew, and needless to say, software always changes anyhow.

What I'm currently trying to do is create a database for hotel bookings. I have different fields in a single table for the following:


- Room Number
- Room Type

Both fields have combo boxes with other tables as their row sources. There are 6 different room types (Single, Double, Double with extra, Family, Large Family, Grand).

The first thing I want help with is to establish a link between the Room Number and Room Type fields. I want the Room Number to be changeable, while the Room Type cannot be input manually, instead displaying a certain output depending on the room number selected. I.E: Room Number is "01", which is a Family Room, so whenever "01" is selected, the Room Type value is automatically "Family Room", and will only change if the user changes the Room Number field to a different room that isn't a Family Room, in which case it will change to whichever value matches the selection of the room number.

I have some familiarity with other coding languages, and get the feeling I need some sort of if statement here, but I'm a bit unsure if I've laid out my database correctly, where to put the if statement, and even how to correctly write it in Access in relation to my fields at all.

I hope I've explained the situation well enough, and am really hoping someone can help guide me through this process. I'm under the impression this is fairly basic, but I'm quite cautious with syntax-based things and such so want to get some sure advice before I attempt anything myself. Thanks in advance.

[June7]

Hotel booking database has been topic of numerous threads and some even have db attached. Might search forum.

The combobox RowSource should be multi-column (columns can be hidden). One of those columns should be the room type. Set properties of RowSource, ColumnCount, BoundColumn, ColumnWidths. Example:

RowSource: SELECT RoomNum, RoomType FROM Rooms ORDER BY RoomNum;
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0.25";0"

Then expression in a textbox can reference the combobox column by index to display the room type. If the room type is in second column the index is 1:

=[cbxRooms].Column(1)


If you want to allow users to first select room type and then another combobox offer a list of qualifying rooms, that is known as cascading or dependent combobox/listbox. Also a frequent topic.

[orange]

but I'm a bit unsure if I've laid out my database correctly

Please show us your table(s) design.

There are free, generic data models at Barry Williams' site.
Here is one re Hotel Booking.

[SilverFang]

Thanks for the info.

Then expression in a textbox can reference the combobox column by index to display the room type. If the room type is in second column the index is 1:

=[cbxRooms].Column(1)

Where exactly do I put this code? I changed [cbxRooms] the syntax you gave to [cbxRoom Number] to better match the names used in my file, but whenever I try to put that code in any of the field properties, Access doesn't like it and refuses to save my file, so I assume it must be elsewhere.

As for my tables design, it's in a very early stage, so literally has just one table (HotelSystem) linking to three others (RoomNumber (the table in question), Nationality and Season). When I said I wasn't sure if it's laid out correctly, I was wondering whether or not it was a good idea to have the Room Types in the same table as the Room Numbers, or if it'd be easier to work with having them in a separate table.

[June7]

Expression goes in textbox ControlSource property.

[SilverFang]

I just tried entering the code into the form, as can be seen below:


However, when I put it into form view, it displays as blank, until I touch another field, and then it'll display as this:


I also checked the property box for the field, and it does acknowledge the code in the Control Source. Why is it displaying like this?

[SilverFang]

Scratch that, I go it to work. Didn't realise the field in the square brackets was just the name [Room Number], as opposed to having cbx. I thought that was meant to be part of the syntax. A simple mistake.

Thanks for the help.

[SilverFang]

Sorry for bumping, but I tried to use that same code looking at the columns in another field, only this time for Columns 2 and 3. Problem is, they ALWAYS come back blank, even though there's data in there.

=[Room Number].Column(2)

Always comes back blank, when they should display the currency values in those columns. The same goes for Column(3), despite the form having no problem showing the values for columns 0 and 1. I even tried changing these values from currency into text, but it changed nothing.

Why is my form ignoring these columns?

[June7]

Nothing wrong with the expression. Must be something else. Would have to review db. If you want to provide, follow instructions at bottom of my post.

[SilverFang]

I've attached it. As you'll notice, it's very basic and incomplete. Just a practice model for myself to get familiar with Access' functions and formulae before I make the real one later.

Even in this copy with the names and values changed, it had the same problems recognising Columns 2 and 3. It's the "Room Price" field in the forms which has the expression in question.

[June7]

I am somewhat surprised the calc with Column(1) works. Change combobox ColumnCount property to 5.

[SilverFang]

I just changed the Room Type and Room Price fields to combo boxes, and set both Column Counts to 5. Nothing has changed.

[June7]

Why would you do that? The instruction was intended to change Room Number combobox. And calcs work perfectly afterward.

[SilverFang]

Ah ok. I got it now. Is there a way to make it so that when you click on the Room Number dropbox, it doesn't display all the prices in the dropdown list without undoing the function for the room prices?

[June7]

Don't understand question. What function?