dean dean
asked on
How to exclude Subform Records From Combobox, Already Selected in Access Datasheet subform, Access 2016
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.
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.
is there any way to create a small demo and upload here
ASKER
in a subform, I have room_no as combobox, which gets records from Tbl_Room_Mast.
which form is that?
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)
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)
I suggest leave it as is and on save click do this
* this is not a working code, just shows the logic
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;
* this is not a working code, just shows the logic
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.
@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...
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...
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.
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
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Me.cboHK_Room_No.Requery