Loading a Recordset into a List Box

[markronz]

Hello everyone-
I am having a bit of a problem. First, let me say that what I am doing is a bit unorthodox. I am basically creating a recordset on my own, and not pulling the information from any database. I was unable to populate my listbox in any other way since this information that I'm displaying is not stored in any tables. I did try to create a list of things separated by semi-colons ("column1info;column2info, but that didn't work because the text I am trying to display can sometimes have semi-colons in it (column;1info;column2info), and that messes everything up.

Hopefully that made sense. Anyway, what I've decided to do is create a recordset of my own and then display it in the list box. Right now when I set the listbox's .recordSet property to my recordset it only displays the column headers properly. None of the actual data appears.

Here is a sample from my code:

Private Sub Form_Load()
    Set rsList = New ADODB.Recordset
    With rsList
        Set .ActiveConnection = Nothing
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        With .Fields
            .Append "recID", adBSTR, 10
            .Append "DateTime", adBSTR, 23
            .Append "Product", adBSTR, 25
            .Append "Type", adBSTR, 30
            .Append "DGRecommended", adBSTR, 4
            .Append "ClientTakeAction", adBSTR, 4
            .Append "Rate", adBSTR, 5
            .Append "Term", adBSTR, 6
            .Append "Amount", adBSTR, 25
            .Append "DGName", adBSTR, 30
            .Append "Comments", adBSTR, 501
        End With
        .Open
    End With
      
    With rsList
      .AddNew
      .Fields("recID") = "New"
      .Fields("DateTime") = "Contact"
      .Fields("Product") = "test1"
      .Fields("Type") = "test2"
      .Fields("DGRecommended") = "test3"
      .Fields("ClientTakeAction") = "test4"
      .Fields("Rate") = "test5"
      .Fields("Term") = "test6"
      .Fields("Amount") = "test7"
      .Fields("DGName") = "test8"
      .Fields("Comments") = "test9"
      .Update
      
    End With
    Set RecActionList.Recordset = rsList
End Sub

Does anyone know why only the column headers are displaying correctly, but the actual data isn't displaying underneath it?

Thanks

 

[CautionMP]

markronz,
I've run into this problem and don't know how to fix it but I can offer a couple of alternatives.
[ol]
[li]You can use a UDF to populate a listbox/combobox. Check the help file for the following topic: RowSourceType Property (User-Defined Function)[/li]
[li]Use a MSForms 2.0 listbox (Insert > ActiveX Controls...), it has an [tt]AddItem()[/tt] method which makeit easier to populate on the fly[/li]
[li]Build your list in code and cache it to a local file (*.csv) then use the SQL [tt]IN[/tt] statement in your [tt]RowSource[/tt] (SELECT * FROM MyFile#csv IN 'C:\' 'TEXT;'[/li]
[/ol]

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)