none
Populate Listbox with Record set - Access 2010 RRS feed

  • Question

  • Fellow Developers,

    I have read over 6 articles of people showing population of a list box from a DAO records set. I am using Access 2010

    I have yet to get any of them to populate the listbox - except for one.

    The only way I have been able to get it to work is to just spin thru the rs and perform an additem. The listbox is a Value List type. It works well. But I was trying to do something more efficient.

    I have tried creating a new list box (table/query), looking at a table that is empty that has the same layout as my query. The set the recordset to the listbox.recordsource. That did not work either. a requery did not work either.

    All my attempts with different options show nothing appearing in the list box.

    Does anyone have a solid way of doing with without looping the recordset?

    Thanks,

    MG

    Saturday, August 13, 2016 4:30 PM

All replies

  • Do you have a specific reason for wanting to use a recordset? After all, a recordset has to be opened on (1) the name of a table, or (2) the name of a query, or (3) a SQL statement. You could set the Row Source of the list box to the name of that table or query, or to that SQL statement, and be done with it. You can do this in code as well as interactively.

    You could use code like this:

        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("SELECT DISTINCT strLastName FROM tblEmployees")
        Set Me.lstNames.Recordset = rst
        Set rst = Nothing
        Set dbs = Nothing

    You would get the same effect by using the single line

        Me.lstNames.RowSource = "SELECT DISTINCT strLastName FROM tblEmployees"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Saturday, August 13, 2016 4:57 PM
  • Hi MG. Seeing your code might help us help you. However, I would guess if you're using something like this:

    Me.ListboxName.Recordset = rs

    then may I suggest using this:

    Set Me.ListboxName.Recordset = rs

    Hope it helps...

    Saturday, August 13, 2016 5:05 PM
  • HI Hans,

    We have two Access db's - a front end working as GUI disconnected - call it A. the data end is Access as well with all of the data - call it b.

    My code is almost exactly like yours so I have rewritten it below

    Dim dbs As DAO.Database
     
    Dim rst As DAO.Recordset

    Set dbs = OpenDatabase(mydbconnectionToB,false)

    Set rst = dbs.OpenRecordset("SELECT lastname, firstname FROM qrymydata")
    Set Me.listboxtNames.Recordset = rst

    rst.close
    Set rst = Nothing

    dbs .closeSet dbs = Nothing

    The query is coming from DB  "B", so the query I am running is not local. thus the connection to somewhere else. I have tried this numerous ways as I explained earlier. I also have tried having both something in rowsouce and loading recordset neither are bringing back data to the listbox. When I run the additem approach in a loop, the results are perfect, of course using a Value List for the listbox type.

    The listbox I just tried had table/query as the type.  Still Nothing. 

    Any other ideas?

    MG

    Sunday, August 14, 2016 3:21 AM
  • I think the problem is that you're closing the recordset and the database. This causes the list box to be cleared immediately after it has been populated. It should work if you remove the lines

    rst.Close

    and

    dbs.Close

    But that means that the connection to the database will remain open, of course.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Sunday, August 14, 2016 8:30 AM
  • Hi Hans,

    Many thanks. Yep, I just relized that myself. I finally got it to working. the only thing I want to do now is to be able to close the connection. So, I cloned my recorset and set that clone to the listbox. But, when I close the DB, that clone gets closed to - I would have thought that it was just an object like an array and would not have any relationship to the DB connection after the clone. but that is not true when you close the DB that it was cloned from. It kills the clone too.

    If there is some other trick to use for this clone concept whereby I can close the DB connection and keep the cloned recordset, that would be great. I do not want to build array - wrong direction. If I have to do something like that, I might as well just keep my loop with a list box that is Value List and do additem for every row - simple.

    thanks MG.

    Sunday, August 14, 2016 4:33 PM
  • Hi MG. I think you might be able to use ADO and a Disconnected Recordset. Hope it helps...
    Sunday, August 14, 2016 5:04 PM
  • Thanks DBGuy. Yes, I knew I could do that with ADO but was trying to stay with DAO as much as possible for consistency. I might do it for just this one.

    Thanks again all.

    MG

    Sunday, August 14, 2016 5:18 PM
  • If there is some other trick to use for this clone concept whereby I can close the DB connection and keep the cloned recordset, that would be great. I do not want to build array - wrong direction. If I have to do something like that, I might as well just keep my loop with a list box that is Value List and do additem for every row - simple.

    Hi MG,

    To retrieve data from a table in an external database you do not need to open that database explicitely.

    You just need to make a link to that table. If the table is already link you can retrieve the data directly:

        Set rst = dbs.OpenRecordset("SELECT lastname, firstname FROM MyTable")

    But you can also use late binding such as:

        Set rst = dbs.OpenRecordset("SELECT lastname, firstname FROM MyTable IN '<full file specification for external database>'")

    If you use a query instead of a table then you can use:

        Set rst = dbs.OpenRecordset(MyQuery.SQL)

    where you insert the IN clause after the table definition part.

    Imb.


    Sunday, August 14, 2016 6:50 PM
  • Thanks DBGuy. Yes, I knew I could do that with ADO but was trying to stay with DAO as much as possible for consistency. I might do it for just this one.

    Thanks again all.

    MG

    Hi MG. Unfortnately, DAO does not support disconnected recordsets. So, you may have to make an exception this time. Good luck!
    Sunday, August 14, 2016 7:52 PM
  • Thanks IMB. but, it was my understanding that the IN condition still required a connection for the RS and that connection had to be open. We do not use linked tables/queries in this scenario.

    MG

    Monday, August 15, 2016 10:58 AM
  • Thanks IMB. but, it was my understanding that the IN condition still required a connection for the RS and that connection had to be open. We do not use linked tables/queries in this scenario.

    Hi MG.

    The IN component specifies in which external databse to look for the tables. You must supply the quoted string of the full file specification of that database. Besides, there are also other syntaxes to retrieve external data, but this one is the easiest.

    Did you try it in the meantime? I use this quite intensive for all kind of purposes.

    Imb.

    Monday, August 15, 2016 12:10 PM
  • It sounds like you don't want to be connected to the back end database any longer than you absolutely have to be.

    If you are going to use DAO simply declare your database and recordset variables in the General Declarations section of your form module, e.g

    Dim dbs as DAO.Database 
    Dim rst as DAO.Recordset

    so that they are visible to all procedures in your form module.  Then in the open or load event of your form populate your recordset and assign it to your listbox recordset property:

    Set dbs = OpenDatabase(mydbconnectionToB,false)
    Set
    rst = dbs.OpenRecordset("SELECT lastname, firstname FROM qrymydata")
    Set Me.listboxtNames.Recordset = rst

    In theory these will automatically be cleaned up when the form is closed but it's good practice to manually clean up in the form's close event:

    rst.close
    set rst = nothing

    dbs.close
    set dbs = nothing

    In this scenario you're only keeping your back end database open for as long as the form containing your listbox is open.  If you absolutely need to have a more minimal connection time to your back end database than that you'll need to use a disconnected ADO recordset as theDBguy suggests or perhaps build a function that creates a value list, e.g.

    Me.listboxtNames.RowSource = MyNameList()
    ' No AddItem needed

    Note also that in general without a persistent connection to the back end database performance will be significantly poorer.  Is there a reason why you don't want a persistent connection to the back end?

    -Bruce

    Monday, August 15, 2016 10:17 PM
  • Hi Bruce,

    Thanks for the info. Yes,  I am aware of that option as well. We have taken a different approach.

    MG

    Tuesday, August 16, 2016 11:24 AM