Trying to write selected values from a listbox to a table
I have the following code in an afterupdate event of a form. I've tried my best to construct it but am not sure it is right. Three things...
1) Will the code work?
2) Why am I getting a "Else without If" when I try to compile?
3) Is the after update event of the form the right place for this?
Here's my code so far:
1) Will the code work?
2) Why am I getting a "Else without If" when I try to compile?
3) Is the after update event of the form the right place for this?
Here's my code so far:
Private Sub Form_AfterUpdate()
Dim RS As DAO.Recordset
Dim varitem As Variant
Set RS = CurrentDb.OpenRecordset("tblSelectedLoadIDs")
If DCount("[DASContractNumber]", "tblSelectedLoadIDs", "[DASContractNumber] = " & Forms!frmBillOfLadingSelector!txtDASContractNumber) > 0 Then
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.Edit
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS!DASContractNumber = Me.txtDASContractNumber
RS.Update
Else
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.AddNew
RS!DASContractNumber = Me.txtDASContractNumber
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS.Update
End With
End If
RS.Close
Set RS = Nothing
End Sub
Please try this -
Private Sub Form_AfterUpdate()
Dim RS As DAO.Recordset
Dim varitem As Variant
Set RS = CurrentDb.OpenRecordset("t
If DCount("[DASContractNumber
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.Edit
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS!DASContractNumber = Me.txtDASContractNumber
RS.Update
End With
Else
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.AddNew
RS!DASContractNumber = Me.txtDASContractNumber
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS.Update
End With
End If
RS.Close
Set RS = Nothing
End Sub
Private Sub Form_AfterUpdate()
Dim RS As DAO.Recordset
Dim varitem As Variant
Set RS = CurrentDb.OpenRecordset("t
If DCount("[DASContractNumber
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.Edit
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS!DASContractNumber = Me.txtDASContractNumber
RS.Update
End With
Else
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.AddNew
RS!DASContractNumber = Me.txtDASContractNumber
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS.Update
End With
End If
RS.Close
Set RS = Nothing
End Sub
Apart from End With , you are also missing the next statement for For Each loop. Please try this -
Private Sub Form_AfterUpdate()
Dim RS As DAO.Recordset
Dim varitem As Variant
Set RS = CurrentDb.OpenRecordset("t
If DCount("[DASContractNumber
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.Edit
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS!DASContractNumber = Me.txtDASContractNumber
RS.Update
NEXT
END WITH
Else
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.AddNew
RS!DASContractNumber = Me.txtDASContractNumber
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS.Update
NEXT
End With
End If
RS.Close
Set RS = Nothing
End Sub
Refer this for more details - https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/for-each-next-statement
Private Sub Form_AfterUpdate()
Dim RS As DAO.Recordset
Dim varitem As Variant
Set RS = CurrentDb.OpenRecordset("t
If DCount("[DASContractNumber
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.Edit
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS!DASContractNumber = Me.txtDASContractNumber
RS.Update
NEXT
END WITH
Else
With RS
For Each varitem In listLoadIDs.ItemsSelected
RS.AddNew
RS!DASContractNumber = Me.txtDASContractNumber
RS!SelectedLoadID = Me.listLoadIDs.Column(2)
RS.Update
NEXT
End With
End If
RS.Close
Set RS = Nothing
End Sub
Refer this for more details - https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/for-each-next-statement
Glad that it worked. You can print/debug this Me.listLoadIDs.Column(2) and see what you are getting..
I am puzzled by the code. Why do you have two loops through the items collection?
The Edit vs Add implies that you are looping through the table's recordset but you are not. You can't edit a record unless you read it first. If the DCount() returns more than 0, you loop through the selected itmes in the listbox but you are updating only the first record of the table's recordset.
Please try to describe what you want to do with words.
The Edit vs Add implies that you are looping through the table's recordset but you are not. You can't edit a record unless you read it first. If the DCount() returns more than 0, you loop through the selected itmes in the listbox but you are updating only the first record of the table's recordset.
Please try to describe what you want to do with words.
I am trying to write the values of a selected multi select listbox to a table. Just the ones that have been selected.
That doesn't tell me the relationship between tblSelectedLoadIDs and the listbox or why you have both add and update. As I said, to update REQUIRES that you read a record and you are not reading a record in the table that matches those selected in the listbox.
After working on this all weekend I got it:
Private Sub Form_Close()
Dim RS As DAO.Recordset
Dim varItem As Variant
Dim item As Integer
Dim i As Variant
Dim ctl As Object
Set RS = CurrentDb.OpenRecordset("t
If DCount("[RecordID]", "tblSelectedIDs", "[RecordID] = " & Forms!frmBillOfLadingSelec
With RS
Set ctl = Me.listIDs
For Each varItem In ctl.ItemsSelected
RS.Edit
RS!RecordID = Forms!frmBillOfLadingSelec
RS!BillOfLadingID = Me.BillOfLadingID
RS!ContractNumber = Me.txtContractNumber
RS!SelectedID = ctl.ItemData(varItem)
RS.Update
Next varItem
End With
Else
With RS
Set ctl = Me.listIDs
For Each varItem In ctl.ItemsSelected
RS.AddNew
RS!RecordID = Forms!frmBillOfLadingSelec
RS!BillOfLadingID = Me.BillOfLadingID
RS!ContractNumber = Me.txtContractNumber
RS!SelectedID = ctl.ItemData(varItem)
RS.Update
Next varItem
End With
End If
RS.Close
Set RS = Nothing
End Sub
Private Sub Form_Close()
Dim RS As DAO.Recordset
Dim varItem As Variant
Dim item As Integer
Dim i As Variant
Dim ctl As Object
Set RS = CurrentDb.OpenRecordset("t
If DCount("[RecordID]", "tblSelectedIDs", "[RecordID] = " & Forms!frmBillOfLadingSelec
With RS
Set ctl = Me.listIDs
For Each varItem In ctl.ItemsSelected
RS.Edit
RS!RecordID = Forms!frmBillOfLadingSelec
RS!BillOfLadingID = Me.BillOfLadingID
RS!ContractNumber = Me.txtContractNumber
RS!SelectedID = ctl.ItemData(varItem)
RS.Update
Next varItem
End With
Else
With RS
Set ctl = Me.listIDs
For Each varItem In ctl.ItemsSelected
RS.AddNew
RS!RecordID = Forms!frmBillOfLadingSelec
RS!BillOfLadingID = Me.BillOfLadingID
RS!ContractNumber = Me.txtContractNumber
RS!SelectedID = ctl.ItemData(varItem)
RS.Update
Next varItem
End With
End If
RS.Close
Set RS = Nothing
End Sub
Premium Content
You need an Expert Office subscription to comment.Start Free Trial