Trying to loop through records in a listbox on a form to write records to a table

I'm using the following code in an attempt to write records to a table using the values in a listbox on a form.  But it is not working.  No records are being added when a new record is created on the form.  Do I have something out of order or is it totally wrong?

    Dim RS As DAO.Recordset
   
    Set RS = CurrentDb.OpenRecordset("Select * from tblInboundOwnersSplitDetail where InboundRecordID = " & Me.txtID.Value)
   
    With RS
        If .RecordCount = 0 Then
        Do Until RS.EOF
        MsgBox "This appears to be a new Inbound record which does not already exist in the program.  A new record is being created."
        RS.MoveNext
        .AddNew
        !InboundRecordID = Me.txtID
        !OwnerID = Me.listOwnerPercentages.Column(7)
        !ApplesOwned = Me.listOwnerPercentages.Column(2)
        !Surcharge = Me.listOwnerPercentages.Column(3)
        .Update
        Loop
    Else
        Do Until RS.EOF
        MsgBox "This record already exists and will be edited if you have made changes."
        RS.MoveNext
        .Edit
        !InboundRecordID = Me.txtID
        !OwnerID = Me.listOwnerPercentages.Column(7)
        !ApplesOwned = Me.listOwnerPercentages.Column(2)
        !Surcharge = Me.listOwnerPercentages.Column(3)
        .Update
        Loop
        End If
    End With
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.

Dale FyeOwner, Developing Solutions LLCCommented:
Is this a multi-select listbox?  There is nothing in that code which would imply you are attempting to loop through the items selected in a listbox.
0
SteveL13Author Commented:
It is just a listbox that displays records based on a query.  The listbox itself is locked so no selections can be made.  It needs to loop through all displayed records.
0
Dale FyeOwner, Developing Solutions LLCCommented:
can you give me a screen shot so I can see what it is you are talking about?

If the listbox is locked and the user cannot make a selection, why are you referring to the listbox in your code?

Is there other code, somewhere else, that automatically selects a single line in the listbox?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
If the user is not selecting anything from the listbox, there would be no active record.  This is not the method you want to use.  Use an append query that selects the items from the same query that the listbox uses and append the values to the target table.  The only code you need is the one line to run the append query.
0

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
Gustav BrockCIOCommented:
Not quite sure what you are trying to do, but your logic skips the editing. Try this:

    Dim RS As DAO.Recordset
    
    Set RS = CurrentDb.OpenRecordset("Select * from tblInboundOwnersSplitDetail where InboundRecordID = " & Me.txtID.Value)
    
    With RS
        If .RecordCount = 0 Then
            MsgBox "This appears to be a new Inbound record which does not already exist in the program.  A new record is being created."
            .AddNew
        Else
            MsgBox "This record already exists and will be edited if you have made changes."
            .Edit
        End If
        !InboundRecordID = Me.txtID
        !OwnerID = Me.listOwnerPercentages.Column(7)
        !ApplesOwned = Me.listOwnerPercentages.Column(2)
        !Surcharge = Me.listOwnerPercentages.Column(3)
        .Update
    End With 

Open in new window


If you wish to add all items of the listbox, you must use a loop and add the listrow parameter n :

Me.listOwnerPercentages.Column(7, n)

Open in new window

where n is the row number which is from:

Abs(Me!listOwnerPercentages.ColumnHeads)

Open in new window

to:

Me!listOwnerPercentages.ListCount

Open in new window

0
SteveL13Author Commented:
Gustav,

Here is what I have now.  But note the last two lines.  I don't know how to make this loop.

    Dim RS As DAO.Recordset
    
    Set RS = CurrentDb.OpenRecordset("Select * from tblInboundOwnersSplitDetail where InboundRecordID = " & Me.txtID.Value)
    
    With RS
        If .RecordCount = 0 Then
            MsgBox "This appears to be a new Inbound record which does not already exist in the program.  A new record is being created."
            .AddNew
        Else
            MsgBox "This record already exists and will be edited if you have made changes."
            .Edit
        End If
        !InboundRecordID = Me.txtID
        !OwnerID = Me.listOwnerPercentages.Column(7, n)
        !ApplesOwned = Me.listOwnerPercentages.Column(2, n)
        !Surcharge = Me.listOwnerPercentages.Column(3, n)
        .Update
    End With
    
    
    
   Abs(Me!listOwnerPercentages.ColumnHeads)
   
   Me!listOwnerPercentages.ListCount

Open in new window

0
Gustav BrockCIOCommented:
That would just be to wrap the .AddNew to .Update in a loop incrementing n.

But you check for a record and if found wish to edit. That doesn't make sense - to edit one record n times.
0
Helen FeddemaCommented:
I think Pat's advice is the best way to accomplish this task -- but if you really, really want to work through the listbox, it can be done by first selecting all items, then working with the ItemsSelected collection, then deselecting all items.  Here is some code -- you will need to plug in your Append code in place of the Debug.Print statement:

Private Sub cmdDisplay_Click()
'Display data from all rows in listbox

   Dim lst As Access.ListBox
   Dim intIndex As Integer
   Dim intRows As Integer
   Dim varItem As Variant
   Dim rst As DAO.Recordset
   Dim strData As String
   
   Set lst = Me![lstSelectMultiple]
   intRows = lst.ListCount - 1
   
   'Select all rows in listbox
   For intIndex = 0 To intRows
      lst.Selected(intIndex) = True
   Next intIndex
   
   'Do something with each selected item in listbox
   For Each varItem In lst.ItemsSelected
      strData = Nz(lst.Column(1, varItem))
      Debug.Print "Data in 2nd column of listbox: " _
         & strData
NextItem:
   Next varItem
   
   'Deselect all rows in listbox
   For intIndex = 0 To intRows
      lst.Selected(intIndex) = False
   Next intIndex
   
End Sub

Open in new window

0
PatHartmanCommented:
There appear to be two loops at play here.  One for the recordset that you are adding/updating and another for the listbox.  How are you matching the two?  Why do you want to write VBA rather than use a query?  Your original problem statement indicated that this was an add only process.  Later, you added the update component.  As long as there is some FK from the RowSource of the listbox that can be used to join to your target table, you can use either two queries - one to add and one to update - or if you are using Jet/ACE as the BE, you can use the magic right join technique that Access supports which will update or insert as needed.

Of course if there is no FK in the listbox, you can't do the update in code either.  Your query contains no order by which is REQUIRED if you are attempting to match records.
0
SteveL13Author Commented:
Pat, be assured... I am trying both solutions.  More to come.
0
SteveL13Author Commented:
I ended up using an update query but the other suggestions are worth handing on to for future.
0
PatHartmanCommented:
Good choice :)
0
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.