• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 82
  • Last Modified:

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
0
SteveL13
Asked:
SteveL13
  • 4
  • 3
  • 2
  • +2
3 Solutions
 
Dale FyeCommented:
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 FyeCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now