+ Reply to Thread
Results 1 to 11 of 11

Recordset displayed in Listbox

  1. #1
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Recordset displayed in Listbox

    At the moment I am retrieving certain records from an Access database and writing them to tables where an array of the table is created for display in a listbox for selection. Can these recordsets be displayed directly into the listbox.

    Typical retrieval code

    Please Login or Register  to view this content.

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Recordset displayed in Listbox

    Hi again Nigel,

    It's not possible to test this fully without access to your workbook and database, but see if the following code gets you moving in the right direction:

    Please Login or Register  to view this content.

    Hope this helps - as always, please let me know how you get on.

    Hope you're all staying Covid-Safe down there in the deep south!

    Regards,

    Greg M

  3. #3
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Recordset displayed in Listbox

    Hi Greg
    I get an error here
    Please Login or Register  to view this content.
    User-defined type not Defined

    all good down South - finally I get vaccine tomorrow, been a strange year +

    Ta Nigel

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Recordset displayed in Listbox

    Hi Nigel,

    Sorry, I forgot to mention that you need to include a Reference to Microsoft ActiveX Data Objects n.n Library (Tools >> References >> select from list)

    Having looked at the database and workbook you sent me, I was able to get the following routine to work correctly:

    Please Login or Register  to view this content.

    Strange year indeed! I managed to get my second jab last Sunday - life should start returning to normal next week.

    As always, please keep me posted re my suggested approach.

    Best regards,

    Greg M

  5. #5
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Recordset displayed in Listbox

    Greg, many thanks, perfect. I will endeavour to adapt for remaining screens over next few days.

    Ultimately I will aim to have application as an addin controlled by a ribbon menu so I am trying to minimise interaction with worksheets.

    Roll on normality ...badly missed

    I'll leave thread open for a while in case I have any problem adapting.

    Regards

  6. #6
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Recordset displayed in Listbox

    You don't actually need to transpose the data - you can just use the Column property instead of List:

    Please Login or Register  to view this content.
    for example.
    Rory

  7. #7
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Recordset displayed in Listbox

    Hi Rory
    gives Message: Either BOF or EOF is True, or the current record is deleted. Requested operation requires a current record

    Records are present

    Sorted. Many thanks Rory. Forgot to remove earlier reference to getrows
    Last edited by nigelog; 05-17-2021 at 06:44 AM.

  8. #8
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Recordset displayed in Listbox

    Hi again Nigel,

    Many thanks for your feedback and also for the Reputation increase - much appreciated!

    You're welcome - glad I was able to help.

    Best regards,

    Greg M

  9. #9
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Recordset displayed in Listbox

    Hi Rory,

    Many thanks for your input - I love to learn about little gems like that!

    Regards,

    Greg M

  10. #10
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Recordset displayed in Listbox

    No worries - that one has made a lot of my code a lot simpler over the years!

  11. #11
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: Recordset displayed in Listbox

    @ Greg and Rory

    I am motoring through changing the code to remove the data writing to tables and all good so far.

    This has been 30 years in the making... Access just daunted me but I can comprehend quizzing the database through VBA

    Many thanks for your help

+ 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] Recordset Into Listbox With Headers
    By stfeliciasredpanda in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2020, 06:05 PM
  2. 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
  3. excel vba populate listbox from mysql recordset
    By rodriguez76 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-04-2013, 02:24 PM
  4. 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
  5. 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
  6. Filling a listbox whith recordset data
    By LuisM in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-31-2006, 12:05 AM
  7. 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

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