+ Reply to Thread
Results 1 to 6 of 6

Recordset Into Listbox With Headers

  1. #1
    Registered User
    Join Date
    04-26-2020
    Location
    Greater Atlanta Metro
    MS-Off Ver
    2016 Pro Plus
    Posts
    17

    Arrow Recordset Into Listbox With Headers

    I'm using VBA to query an Access DB, produce a recordset, and put the recordset into a listbox for a user to choose what to write to a range. The issue is headers aren't coming in with the recordset. I've seen various workarounds suggested, such as putting another listbox above the results one and putting the headers in. We have 117 fields, so that's not exactly feasible. I tried writing the recordset to a range and using the range as the rowsource, but it didn't refresh correctly with query updates.

    Anyone have advice or a solution for this? Seems to be a fairly common problem with no great workaround.

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Recordset Into Listbox With Headers

    Hi,

    It would be great to have an illustration of your listbox, as well as the code you are using. Headers in the classic sense, of staying at the top even when you scroll down, will only work with a range as the rowsource. For this reason, I usually use labels above the various fields, and update those as necessary (if they can't just be static). If you are looping through the recordset to add the records to the listbox, you could loop through the "headers" first and add them.

    A simple loop like the one below will provide you with the name of each field in he recordset:

    Please Login or Register  to view this content.
    Where rset1 is your recordset, and iCols is just an Integer or Long type variable used for the loop.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    04-26-2020
    Location
    Greater Atlanta Metro
    MS-Off Ver
    2016 Pro Plus
    Posts
    17

    Re: Recordset Into Listbox With Headers

    The code to write to the listbox is:

    Please Login or Register  to view this content.
    Setting columns via the field count and then equaling columns to the records.

    I actually stumbled onto a different solution writing to range which is stable. The one thing is it brings in the header row, but it's selectable. It's not just putting it into the actual header row, even if I set column heads to True. I'm thinking this might be the only/best path forward.

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Recordset Into Listbox With Headers

    Hate to be difficult, but could you attach a sample workbook? Using a range is the ideal solution, but there is a property on the list box for headers that must be set in order for them to be actual headers and not just another selectable item

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Recordset Into Listbox With Headers

    Try chage
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-26-2020
    Location
    Greater Atlanta Metro
    MS-Off Ver
    2016 Pro Plus
    Posts
    17

    Re: Recordset Into Listbox With Headers

    Long story short, we never figured out how to do it and I don't think there is a way. You either have to go with row source as a table and turn headers on - or - write the recordset to a range, load the range to listbox, and have the headers as part of that. We opted for the latter and it's working okay.
    Last edited by stfeliciasredpanda; 07-08-2020 at 05:08 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Return headers with results in Recordset
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-30-2019, 02:45 AM
  2. Userform Listbox Column headers from recordset or array?
    By puuts in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-10-2016, 09:33 AM
  3. [SOLVED] Get recordset without the headers
    By Megatronixs in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2016, 04:28 AM
  4. ListBox will not transpose from SQL recordset
    By jlanghorn in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 03-25-2015, 04:44 AM
  5. Populate VBA Userform Listbox using SQL Recordset
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2013, 06:48 PM
  6. Problem Adding Rows to ListBox from Recordset
    By sys_daw in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-06-2010, 01:19 PM
  7. Filling a listbox whith recordset data
    By LuisM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2006, 12:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1