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
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