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:

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

Open in new window

SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please try this -

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
      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
SteveL13Author Commented:
Am getting End With without With
John TsioumprisSoftware & Systems EngineerCommented:
I think you are missing the Next from your For
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

LEARN MORE
Pawan KumarDatabase ExpertCommented:
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("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
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
SteveL13Author Commented:
Seems to be working except I'm getting nothing in the table for SelectedLoadID.
Pawan KumarDatabase ExpertCommented:
Glad that it worked. You can print/debug this Me.listLoadIDs.Column(2) and see what you are getting..
PatHartmanCommented:
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.
SteveL13Author Commented:
I am trying to write the values of a selected multi select listbox to a table.  Just the ones that have been selected.
PatHartmanCommented:
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.
SteveL13Author Commented:
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("tblSelectedIDs")

    If DCount("[RecordID]", "tblSelectedIDs", "[RecordID] = " & Forms!frmBillOfLadingSelector!txtRecordID) > 0 Then
        With RS
        Set ctl = Me.listIDs
        For Each varItem In ctl.ItemsSelected
        RS.Edit
        RS!RecordID = Forms!frmBillOfLadingSelector.txtRecordID
        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!frmBillOfLadingSelector.txtRecordID
        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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
worked?
SteveL13Author Commented:
Yes.  My code worked.
Pawan KumarDatabase ExpertCommented:
Glad to hear. :) Cheers.
PatHartmanCommented:
As long as you understand that the .Edit is only ever updating the first record in the recordset.
Pawan KumarDatabase ExpertCommented:
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.
SteveL13Author Commented:
It worked as I stated earlier.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.