Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: How to allocate an ADO rs to a ListBox?

    Once I have this recordset into memory:

    Code:
    Set cnn = CurrentProject.Connection
    Set rs1 = New ADODB.Recordset
    sQry = "SELECT MemRef, Count(AddrCode) AS CountOfAddrCode " & _
           "FROM tblTemp " & _
           "GROUP BY MemRef " & _
           "HAVING (((Count(AddrCode))>5));"
    
    rs1.Open sQry, cnn, adOpenStatic, adLockReadOnly
    How can I assign all the records in rs1 to a listbox? I tried this:

    Code:
    lstSubsExceptions.RowSource = rs1!MemRef
    But it doesn't work. Any help much appreciated.
    Last edited by bcass; 03-07-05 at 12:53.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You can't ... The RowSource has to have a NAMED query or table or an SQL statement ... Try putting your SQL as the RowSource of the listbox and issue a requery ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Feb 2004
    Posts
    25

    Row Source

    I thought you could create a value list on the fly as long as it isn't too big by looping through your result RS and appending a string to itself and delimit each value with a semicolon?

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    You could do it that way, but you're just adding a step since you're going to have to loop thru the rs to build the list. Why not just use the SQL you use to create the rs as the source for the combo.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I ended up using the original SQL as the RowSource. It just seems a bit inefficient to run the same query twice when the recordset is already in memory as rs1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •