Exclude Subform Records From Combobox Already Selected Access Datasheet, Access 2016

[devtr]

I have database for housekeeping for hotel.
Main table: tbl_hkeeping_Mast
date
houkeeping_no (primary key)
housekeeper_No

child table: Tbl_hkeeping_Detail
housekeeping_no (ref. key)
room_no (combo box on datasheet subform)
room_status
service

in a subform, I have room_no as combobox, which gets records from Tbl_Room_Mast. How do i exclude rooms that have already been selected in subform while entering data.
I tried rowsource property in on got focus and everywhere else. but its showing an error-syntex error or operator missing.
Then I tried it in service field after update event. It excluded first selected room in second row but that first selected room number came back in third row combobox values.
Me.HK_ROOM_NO.RowSource = "SELECT room_No from tbl_room_mast " & _
"Where room_no <> " & Me.HK_ROOM_NO & ";"

my VBA knowledge is limited. I tried searching online but its all confusing.
So far I have this coding in room_no On got focus but its show ing an error: invalid sintax(missing operator) in query

Dim aTest As Variant
Dim intCtr As Integer
Dim strBuild As String
Dim strPrefix As String

strPrefix = "SELECT room_no from tbl_room_mast WHERE room_no NOT IN("

aTest = Array(Me.HK_ROOM_NO)

For intCtr = LBound(aTest) To UBound(aTest)
strBuild = strBuild & "" & aTest(intCtr) & ","
Next


Me.HK_ROOM_NO.RowSource = strPrefix & Left$(strBuild, Len(strBuild) - 1) & ")"

room_no datatype is number.
any suggestions?
thanks

 

[sneuberg]

You shouldn't need any VBA for this other than to requery the combo box in the events where the row source would change, e.g. , on current, after update, delete. The main trick is getting the row source query of the combo box right. I think you were headed in the right direction with trying a subquery in a NOT IN clause. I believe the following query which is named qryRoomNoComboRowSource in the attached database is about what you are looking for.

SELECT Tbl_Room_Mast.room_no
FROM Tbl_Room_Mast
WHERE Tbl_Room_Mast.room_no NOT IN (SELECT Tbl_hkeeping_Detail.room_no FROM Tbl_hkeeping_Detail WHERE Tbl_hkeeping_Detail.housekeeping_no=[Forms]![frmHouseKeeping]![houkeeping_no]);

You can see this work in the attached database where I suggest you look at the combo box requeries in the sfrmHousekeeping events to see how they are set up.

 

[devtr]

sorry for being late. so solution is to make subform continues instead of datasheet
thanks