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
> Count Records Continuous Form Based On Criteria, Access 2016    
 
   
MrFormula
post Jan 19 2020, 07:57 AM
Post#1



Posts: 652
Joined: 28-May 05



My goal is to assign Room Mates while my team is on site at the project.

I can lookup team members with this Combo23 (I know i need to rename this to a better name(
CODE
SELECT TeamMember.TeamMemberID, TeamMember.FName, TeamMember.TeamDetailsID
FROM TeamMember LEFT JOIN TeamRoomAssgn ON TeamMember.TeamMemberID = TeamRoomAssgn.TeamMemberID
WHERE (((TeamMember.TeamDetailsID)=[forms]![hotel]![teamdetailsid]) AND ((TeamRoomAssgn.teammemberid) Is Null));


I then assign the RoomTypeCBO

CODE
SELECT HotelRooms.HotelRoomsID, HotelRoomType.RoomDesc, HotelRooms.HotelRoomTypeID, HotelRooms.HotelID, HotelRoomType.RoomOccupants
FROM HotelRoomType INNER JOIN HotelRooms ON HotelRoomType.HotelRoomTypeID = HotelRooms.HotelRoomTypeID
GROUP BY HotelRooms.HotelRoomsID, HotelRoomType.RoomDesc, HotelRooms.HotelRoomTypeID, HotelRooms.HotelID, HotelRoomType.RoomOccupants
HAVING (((HotelRooms.HotelID)=[forms]![hotel]![HotelID]));


Then I have a button to add a "new Room"
CODE
RoomMate = Me.RoomAssgnID


So now I am at the "Problem" I would like to have a text box "RoomMateTotal" to tell me how many people are in This room. I think it would be something like =count(roomMate, "roomMate=" & me.me.roomAssgnID)

The Goal would be to make sure that RoomMateTotal does not exceed RoomOccupants. (roomOccupants are defined by room type on the HotelRoomType table)

Rest of the senerio, I will have a combo box that when I am adding more people to rooms that will be able to lookup and see what rooms that RoomMateTotal < RoomOccupants so I can select the roomMate for this new Member.
Go to the top of the page
 
theDBguy
post Jan 19 2020, 09:28 AM
Post#2


UA Moderator
Posts: 77,520
Joined: 19-June 07
From: SunnySandyEggo


Hi. Try using DCount().

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:35 AM