+ Reply to Thread
Results 1 to 19 of 19

ListBox will not transpose from SQL recordset

  1. 03-23-2015, 11:08 AM #1
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    ListBox will not transpose from SQL recordset

    Dear all,

    I have a ListBox which I am populating using the code below. There are 12 columns of data. The ListBox is populated correctly when there is more than 1 record, however quite often only 1 row will be returned. When this occurs all the data is populated under 1 column only for the 12 column entries. This happens to be column 6 (where all the data is listed below). There is a function posted on this website that deals with this, however when I use that it only populates the first two columns of my listbox and not the 12.

    Please can you offer some advice. Thank you.


    Please Login or Register  to view this content.
    Kind regards,
    jlanghorn
    Reply With QuoteRegister To Reply
  2. 03-23-2015, 11:44 AM #2
    Kyle123
    Kyle123 is offline
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ListBox will not transpose from SQL recordset

    You can't transpose like that, you want the column property:

    Please Login or Register  to view this content.
    Might actually by Columns rather than Column (can't remember off the top of my head)
    Reply With QuoteRegister To Reply
  3. 03-23-2015, 12:15 PM #3
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Hi,

    Thank you for your reply.

    I have made that amendment and it is giving the error Run-time error '3021': Either BOF or EOF is True, or the current record had been deleted. Requested operation requires a current record.

    Its 'Column' as apposed to 'Columns'. Thank you.

    Many thanks,
    Jourden
    Reply With QuoteRegister To Reply
  4. 03-23-2015, 12:20 PM #4
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    Does this work?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.
    Reply With QuoteRegister To Reply
  5. 03-23-2015, 02:12 PM #5
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Hi,

    I have tried the suggested code and its populating the first 3 columns only. Thank you for the suggestion though. Maybe the properties in the listbox are not setup correctly.

    Many thanks,
    jlanghorn
    Reply With QuoteRegister To Reply
  6. 03-24-2015, 12:03 PM #6
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    On thoughts on the above? Many thanks.
    Reply With QuoteRegister To Reply
  7. 03-24-2015, 12:04 PM #7
    Kyle123
    Kyle123 is offline
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: ListBox will not transpose from SQL recordset

    Are you sure that more than 3 fields are being returned from the stored proc?

    Have you set the column widths somewhere?
    Reply With QuoteRegister To Reply
  8. 03-24-2015, 12:14 PM #8
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    If the original code only fails when a single record id returned then you could add code to it that checks if a single record has been returned and deal with it accordingly.
    Reply With QuoteRegister To Reply
  9. 03-24-2015, 01:05 PM #9
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Yep that was my thought as well. How do I close this item and assign credit? Thanks.
    Reply With QuoteRegister To Reply
  10. 03-24-2015, 01:42 PM #10
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    The thing is its fine if it brings back a single row. I just need to be able to display it if it does. Thanks.
    Reply With QuoteRegister To Reply
  11. 03-24-2015, 01:44 PM #11
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    So check if a single record has been returned and if that's the case use a different method to populate the listbox.
    Reply With QuoteRegister To Reply
  12. 03-24-2015, 01:48 PM #12
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    That's the question I am asking I don't know how to do that. I can't use the add item because its over 10 columns.
    Reply With QuoteRegister To Reply
  13. 03-24-2015, 01:49 PM #13
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    ie how to populate 12 columns for a single row.
    Reply With QuoteRegister To Reply
  14. 03-24-2015, 01:57 PM #14
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    Use AddItem to add a (blank) row to the listbox then loop through the fields of the record adding their values to the columns of the blank row.

    Something like this.
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  15. 03-24-2015, 02:10 PM #15
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Thank you. I'm getting a run-time error '9': Subscript out of range.
    Reply With QuoteRegister To Reply
  16. 03-24-2015, 02:26 PM #16
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    Quote Originally Posted by Me
    Something like ...
    What I posted is meant to show the idea, it isn't meant to be just plugged into the existing code.

    For example, I don't know the dimensions of MyArray when a single record is returned, especially since you are executing a stored procedure.

    You should check that yourself in the Locals window.
    Reply With QuoteRegister To Reply
  17. 03-24-2015, 04:26 PM #17
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Yes I have in the properties of the ListBox. Thanks.
    Reply With QuoteRegister To Reply
  18. 03-24-2015, 04:37 PM #18
    Norie
    Norie is offline
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: ListBox will not transpose from SQL recordset

    I don't follow.

    There's nothing in the properties of the listbox that will tell you the dimensions of the array.
    Reply With QuoteRegister To Reply
  19. 03-25-2015, 04:44 AM #19
    jlanghorn
    jlanghorn is offline
    Registered User
    Join Date
    03-23-2015
    Location
    London, England
    MS-Off Ver
    MS Office 2010
    Posts
    11

    Re: ListBox will not transpose from SQL recordset

    Please ignore the last comment. Apologies. Intended for something else. Thanks for your help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] How to transpose a recordset from a MySQL query
    By FiscalCliff in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-04-2014, 07:30 AM
  2. 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
  3. 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
  4. Filling a listbox whith recordset data
    By LuisM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2006, 12:05 AM
  5. [SOLVED] Populating a multi column listbox with ADO Recordset
    By Paul Faulkner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2005, 03:05 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1