UtterAccess.com
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    
 
   
MrFormula
post Sep 9 2019, 07:00 PM
Post#1



Posts: 595
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)
TeamReservedHotelID
HotelRoomsID
HotelRoomTypeID
TeamMemberID
TeamDetailsID
RoomNameID

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
CODE
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.
Combo21
CODE
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
CODE
SELECT TeamMember.TeamMemberID, TeamMember.FName, TeamMember.TeamDetailsID
FROM TeamMember
WHERE (((TeamMember.TeamDetailsID)=[forms]![hotel]![TeamRoomAssgnHotel]![teamdetailsid]));


Go to the top of the page
 
MadPiet
post Sep 9 2019, 08:33 PM
Post#2



Posts: 3,299
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
 
MrFormula
post Sep 11 2019, 09:18 PM
Post#3



Posts: 595
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    20th September 2019 - 06:55 AM