X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Do Not Over Book A Room, Access 2016    
post Sep 9 2019, 07:00 PM

Posts: 608
Joined: 28-May 05

I have all of the data being written to the table but I can’t get the queries to work properly. I grew up on Excel, I know I know this is not excel and I am trying to forget everything I learned to think properly for databases.
TeamRoomAssgn (Table)

From the above table I would like to assign a room to my team members. I would like to Programmatically control that I don’t assign more people to a room than possible.
I have 3 cascading combo boxes on my form so I can assign the rooms.

Combo19 is for the room type
SELECT HotelRooms.HotelRoomsID, HotelRoomType.RoomDesc, HotelRooms.HotelRoomTypeID, HotelRooms.HotelID
FROM (HotelRoomType INNER JOIN HotelRooms ON HotelRoomType.HotelRoomTypeID = HotelRooms.HotelRoomTypeID) INNER JOIN TeamRoomAssgn ON HotelRooms.HotelID = TeamRoomAssgn.TeamReservedHotelID
GROUP BY HotelRooms.HotelRoomsID, HotelRoomType.RoomDesc, HotelRooms.HotelRoomTypeID, HotelRooms.HotelID
HAVING (((HotelRooms.HotelID)=[forms]![hotel]![HotelID]));
In the after Update of this combo box I write values to the TeamRoomAssgn table
[Forms]![Hotel]![TeamRoomAssgnHotel]![TeamDetailsID] = [Forms]![TeamPerdiem]![TeamDetailsID]
TeamReservedHotelID = Forms!TeamPerdiem!TeamHotel!TeamReservedHotelID
HotelRoomsID = Me.HotelRoomsID
HotelRoomTypeId = [Combo19].[Column](3)
If Me.Dirty = True Then
Me.Dirty = False
End If

If RoomTypeID indicates a Double after I assign two people to that room I want that to disappear from the combo box as an option. In the RoomType Table I have a field with RoomOccupants where I define how many people will fit in each room type.
SELECT RoomNames.RoomNameID, RoomNames.RoomName
FROM RoomNames;

For the RoomNameID I have a random list of room names that I would like to assign to these rooms, this way I can distinguish between the rooms if I have 2 double rooms reserved.
When I select a TeamMember I write the primary key of that team member in the TeamMemberID field so that they can “Dissapear” from the combo dropdown as well.

Combo 23
SELECT TeamMember.TeamMemberID, TeamMember.FName, TeamMember.TeamDetailsID
FROM TeamMember
WHERE (((TeamMember.TeamDetailsID)=[forms]![hotel]![TeamRoomAssgnHotel]![teamdetailsid]));

Go to the top of the page
post Sep 9 2019, 08:33 PM

Posts: 3,334
Joined: 27-February 09

Never mind all that code. "Do not overbook a room." If you are adding the team members to rooms by selecting them from a mult-select listbox, if you have a room selected from a combobox, you can hide the size {1.2.3}, then you can just limit the .ItemsSelected to less than or equal to the OccupantSize or whatever.

Either that or you set .AllowAdditions to the subform to False when you reach the requisite # of occupants.

Of course, if you used an unbound listbox, you wouldn't even have to do that. You could just grab the first N selected members of the listbox.
Go to the top of the page
post Sep 11 2019, 09:18 PM

Posts: 608
Joined: 28-May 05

Thanks I will look into your ideas with a list box.
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd October 2019 - 07:42 AM