Listbox and For Loop

[WillM]

I have a list box with a select few records in it that when a button is pressed, the contents are displayed to text boxes. It partially works, but the problem I have is that the loop only picks up the last record added to the list box. I have included the code below, changed some names to protect the innocent, etc. I am at my wits end with this because I can't figure out why it grabs one and not the rest.
From what I can tell, it loops through these lines and it catches the last record, and then exits the loop. It is not taking all the records with it when it goes to the function:

For intCurrentRow = 0 To lstOld.ListCount - 1
lstOld.Selected(intCurrentRow) = True
Next intCurrentRow

Thanks for any help! Full version of code below:

  Private Sub btnAddOld_Click()
For intCurrentRow = 0 To lstOldCitations.ListCount - 1
lstOldCitations.Selected(intCurrentRow) = True
Next intCurrentRow 
  On Error GoTo Err_cmdCreateOld _Click
   
      Me.txtNotice = CreateOld (Me.lstOld )
   
  Exit_cmdCreateOld _Click:
   
      
      Exit Sub
  Err_cmdCreateOld _Click:
      MsgBox Err.Number & "-" & Err.Description
      Resume Exit_cmdCreateOld _Click
  End Sub


  Public Function CreateOld (ctlRef As ListBox) As String
  On Error GoTo Err_CreateOld _Click
   
  Dim i As Variant
  Dim dbs As DAO.Database
  Dim rsTable As DAO.Recordset
   
  Set dbs = CurrentDb
  Set rsTable = dbs.OpenRecordset("tblNew", dbOpenTable)
   
  For Each i In ctlRef.ItemsSelected
      rsTable.AddNew
      rsTable!fkReviewID = Me.ReportID
      rsTable!PrinNumber = lstOld .Column(0, i)
      rsTable!IncDate = lstOld .Column(4, i)
      rsTable!Comment = "Prior Comment: " & Chr(13) & Chr(10) & lstOld .Column(2, i) & Chr(13) & Chr(10) & "Final Comment: "
      rsTable!DateClosed = Date
      rsTable.Update
  Next i
   
  Set rsTable = Nothing
  Form!subfrmInc.Requery
   
  Exit_CreateOld _Click:
  Exit Function
   
  Err_CreateOld _Click:
      Select Case Err.Number
      Case 3022 'ignore duplicate keys
      Case Else
          MsgBoxErr.Number& "-" & Err.Description
      End Select
       Resume Exit_CreateOld _Click
  End Function

 

[MarkK]

Does the listbox allow multiple selections? If not, setting one selection will erase all the other ones.

Check the ListBox.MultiSelect property on the Other tab of the property sheet in design view. What does it say?

 

[smig]

why not simply put this code in the Button.OnClick event ?

  Dim i As Variant
  Dim dbs As DAO.Database
  Dim rsTable As DAO.Recordset
   
  Set dbs = CurrentDb
  Set rsTable = dbs.OpenRecordset("tblNew", dbOpenTable)
   
  For Each i In ctlRef.ItemsSelected
      rsTable.AddNew
      rsTable!fkReviewID = Me.ReportID
      rsTable!PrinNumber = ctlRef.Column(0, i)
      rsTable!IncDate = ctlRef.Column(4, i)
      rsTable!Comment = "Prior Comment: " & Chr(13) & Chr(10) & ctlRef.Column(2, i) & Chr(13) & Chr(10) & "Final Comment: "
      rsTable!DateClosed = Date
      rsTable.Update
  Next i
   
  Set rsTable = Nothing
  Form!subfrmInc.Requery

 

[WillM]

MarkK you are a genius! I can't believe I forgot to check that...*sigh*

Works like a charm!

Thanks again!
WillM

 

[WillM]

Smig, I am not sure why we didn't do that in the first place. However, I am neck deep in this already with everything set the way it is.

Thanks for the suggestion though, I appreciate it!