Trying to write selected values from a listbox to a table

SteveL13
SteveL13 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

Commented:
Am getting End With without With
John TsioumprisSoftware & Systems Engineer

Commented:
I think you are missing the Next from your For
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!

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
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

Author

Commented:
Seems to be working except I'm getting nothing in the table for SelectedLoadID.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Glad that it worked. You can print/debug this Me.listLoadIDs.Column(2) and see what you are getting..
Distinguished Expert 2017

Commented:
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.

Author

Commented:
I am trying to write the values of a selected multi select listbox to a table.  Just the ones that have been selected.
Distinguished Expert 2017

Commented:
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.
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
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
worked?

Author

Commented:
Yes.  My code worked.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Glad to hear. :) Cheers.
Distinguished Expert 2017

Commented:
As long as you understand that the .Edit is only ever updating the first record in the recordset.
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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.

Author

Commented:
It worked as I stated earlier.

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