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 SubASKER
Am getting End With without With
I think you are missing the Next from your For
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Seems to be working except I'm getting nothing in the table for SelectedLoadID.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
worked?
ASKER
Yes. My code worked.
Glad to hear. :) Cheers.
As long as you understand that the .Edit is only ever updating the first record in the recordset.
My suggestions of NEXT and ENDWITH clearly helped you. I suggest you to select your answer as accepted and my comment as assisted solution. I am opening the question for you.
ASKER
It worked as I stated earlier.
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