How to exclude Subform Records From Combobox, Already Selected in Access Datasheet subform, Access 2016

dean dean
dean dean used Ask the Experts™
on
Thank you all for your time.
I have database for housekeeping for hotel.
Main table: tbl_hkeeping_Mast
date
houkeeping_no
housekeeper_No

child table: Tbl_hkeeping_Detail in datasheet view
housekeeping_no
room_no
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.
BTW roon_no is number field.

Me.HK_ROOM_NO.RowSource = "SELECT room_No from tbl_room_mast " & _
"Where room_no <> " & Me.HK_ROOM_NO & ";"

any suggestions?
I see one solution as a code loop thorough the records in the subform to create a string variable consisting of a comma delimited list of the values. But my VBA knowledge is very limited. I tried searching online but it's confusing.
thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:
Assuming there is some selected flag or date field that is populated when the room is selected, use the AfterUpdate event of the subform record to requery the combo.  In the RowSource for the combo, select rows that are not selected.

Me.cboHK_Room_No.Requery
HainKurtSr. System Analyst

Commented:
is there any way to create a small demo and upload here

Author

Commented:
Hi
Sorry late again
here is attachment.
thanks
HOUSEKEEPINGExpertsExchange.accdb
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HainKurtSr. System Analyst

Commented:
in a subform, I have room_no as combobox, which gets records from Tbl_Room_Mast.

which form is that?
HainKurtSr. System Analyst

Commented:
this is really complicated issue...

I suggest leave it as is :)

but here is the idea

1. on form load, get all the items and put into an array, or bind to list box, but make it invisible
2. on click of the listbox (make it value list), write an event to fill it from that invisible list box, and exclude if one of them are used in the grid (but not current row)
HainKurtSr. System Analyst

Commented:
I suggest leave it as is and on save click do this

for i=1 to rows.count-1
  for j=i+1 to rows.count
    if rows[i].selectbox.value = rows[j].selectbox.value then
begin
  msgbox "row " & i & " and row " & j & " has the same value, change one of them!"
  rows[i].selectbox.focus
  exit function
end;

Open in new window


* this is not a working code, just shows the logic
Distinguished Expert 2017

Commented:
It really doesn't take any code to do this.  There is no reason to use unbound listboxes that you load manually.  All you need is a way to identify what is selected.  Then your query uses a left join and selects only items that are NOT in the selected group.  I'll post a sample query but it is much more complicated than what you probably need.  The graphic is the "base" query where the qAssiginedDrawings are joined to the rest of the tables and the selection criteria is where the RevID from the assigned table is null.  That means everything that is NOT assigned is selected.  The SQL string below is the final query that is the recordsource for the Left side listbox (Drawings).  The rightside listbox is labeled Transmittal.  When the form opens initially, the leftside listbox shows all the drawings on the job and the rightside one is empty.  As drawings get added to the transmittal, they are dropped from the leftside box by a requery.Transmittal.PNGUnassigned.PNG
HainKurtSr. System Analyst

Commented:
@PatHartman

the issue looks like, until save button is clicked, nothing is saved to db!

so, you cannot do it with queries... everything is on the form... maybe I am wrong, but this is what I see...
Distinguished Expert 2017

Commented:
The left and right buttons are grouped in an In() clause to select the records changing from one state to the other (selected-dselected)

Here is the code from the left and right buttons.
Private Sub cmdAddSelected_Click()
    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim strSQLupdate As String

   On Error GoTo cmdAddSelected_Click_Error

    If CheckAuthorization(Left(Me.Job, 2)) = True Then
    Else
        Exit Sub
    End If
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    'Add drawings to a transmittal
    Dim i As Variant

    If IsDate(Me.txtSentDT) Then
        MsgBox "Transmittal is complete when sent date is populated.  No more items may be added.", vbOKOnly
        Exit Sub
    End If
    Set db = CurrentDb()
    Set td = db.TableDefs!tblTransmittalDetail
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)

    For Each i In Me.lstDrawings.ItemsSelected
        rs.AddNew
            rs!RevID = Me.lstDrawings.Column(0, i)
            'update resubmitind on drawing rec if necessary
            If Me.lstDrawings.Column(6, i) = "Yes" And Forms!frmCreateTransmittal!cboStageID.Column(2) = "Approval" Then
                strSQLupdate = "UPDATE tblDrawings INNER JOIN tblRevisions ON tblDrawings.DrawingID = tblRevisions.DrawingID "
                strSQLupdate = strSQLupdate & " SET tblDrawings.ResubmitInd = False "
                strSQLupdate = strSQLupdate & " WHERE tblRevisions.RevID = " & rs!RevID & ";"
                DoCmd.RunSQL strSQLupdate
            End If
            rs!TransmittalID = Me.TransmittalID
            rs!ReturnDT = Null
            rs!UpdateDT = Now()
            rs!UpdateBy = Environ("UserName")
        rs.Update
    Next i
    
    Me.lstDrawings.Requery
    Me.lstTransmittal.Requery
    Me.txtCountAvailable.Requery
    Me.txtCountSelected.Requery

cmdAddSelected_Click_Exit:
   Exit Sub

cmdAddSelected_Click_Error:
    Select Case Err.Number
        Case 3021
            Resume cmdAddSelected_Click_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdAddSelected_Click of VBA Document Form_frmCreateTransmittal"
    End Select
    
End Sub

Private Sub cmdRemoveSelected_Click()
    If CheckAuthorization(Left(Me.Job, 2)) = True Then
    Else
        Exit Sub
    End If
    
    If Me.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    'remove selected drawings from transmittal
    Dim i As Variant
    Dim strIN As Variant
    Dim strSQL As String

    If IsDate(Me.txtSentDT) Then
        MsgBox "Transmittal is complete when sent date is populated.  No items may be removed.", vbOKOnly
        Exit Sub
    End If
    strIN = ""
    For Each i In Me.lstTransmittal.ItemsSelected
        strIN = strIN & Me.lstTransmittal.ItemData(i) & ","
    Next i
    If strIN & "" = "" Then
        MsgBox "There is nothing to move", vbOKOnly
        Exit Sub
    End If
    strIN = Left(strIN, Len(strIN) - 1) ' remove trailing comma.
    strSQL = "Delete tblTransmittalDetail.* From tblTransmittalDetail"
    strSQL = strSQL & " WHERE TransmittalID = " & Me.TransmittalID & " AND RevID In(" & strIN & ");"
    DoCmd.RunMacro "mWarningsOff"
    DoCmd.RunSQL strSQL
    DoCmd.RunMacro "mWarningsOn"
    Me.lstDrawings.Requery
    Me.lstTransmittal.Requery
    Me.txtCountAvailable.Requery
    Me.txtCountSelected.Requery
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial