Re: Bindind a listbox to a recordset using DAO in Access 2000 VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jezebel (dwarves_at_heaven.com.kr)
Date: 07/25/04

  • Next message: Helmut Weber: "Re: How do I detect Muliple selections and then how do I work with them?" 
    Date: Sun, 25 Jul 2004 18:02:30 +1000

    Unless things have changed since W2000, you can bind a listbox to a
    recordset at all. RowSource works only in Excel, and only to bind the list
    to a range of cells.

    You'll need to populate the list by hand:

    Do until rs.EOF
        Forms("form1").Controls!List3.AddItem rs(1)
        :

    "markawmaw" <work@markaw.com> wrote in message
    news:96cbe965.0407242346.36089294@posting.google.com...
    > Hi All,
    >
    > I can't seem to get this to work. I know the recordset is being
    > created and I know that is has the right number of records in it, but
    > the Listbox doesn't appear to bind itself; I end with a blank list
    > box.
    >
    > The form (form1) has a listbox on it (list3) and a command buton
    > (command2)
    >
    > Option Compare Database
    >
    > Private Sub Command2_Click()
    >
    > On Error GoTo exitSub
    >
    > Dim wks As DAO.workspace
    > Dim db As DAO.Database
    > Dim rs As DAO.Recordset
    >
    > Set wks = CreateWorkspace("", "admin", "", dbUseJet)
    > Set db = wks.OpenDatabase("c:\tmp\db1.mdb")
    > Set rs = db.OpenRecordset("SELECT cmp_s_name FROM tblCompany",
    > dbOpenSnapshot)
    >
    >
    > Forms("form1").Controls!List3.ColumnCount = rs.Fields.Count
    > Forms("form1").Controls!List3.RowSourceType = "Table/Query"
    > Forms("form1").Controls!List3.RowSource = "SELECT * FROM
    > tblCompany"
    > Forms("form1").Controls!List3.Requery
    >
    > MsgBox rs.Fields.Count
    >
    > Exit Sub
    >
    > exitSub:
    > Debug.Print Err.Number
    > Debug.Print Err.Description
    > Set rs = Nothing
    > Set db = Nothing
    > Set wks = Nothing
    >
    > End Sub

  • Next message: Helmut Weber: "Re: How do I detect Muliple selections and then how do I work with them?"

    Relevant Pages

    • Re: Building an array within another array ...
      ... Dim rs As DAO.Recordset ... ' Set the number of Columns = number of Fields in recordset ... ' Load the ListBox with the retrieved records ... This code is great to fill a listbox without a condition... ...
      (microsoft.public.word.vba.general)
    • Re: Building an array within another array ...
      ... This code is great to fill a listbox without a condition... ... Dim pStr As String ... Dim db As DAO.Database ... ' Retrieve the recordset ...
      (microsoft.public.word.vba.general)
    • Re: Building an array within another array ...
      ... basis for filtering the data that you then want to load into a listbox. ... Dim rs As DAO.Recordset ... ' Set the number of Columns = number of Fields in recordset ... ' Load the ListBox with the retrieved records ...
      (microsoft.public.word.vba.general)
    • Re: Building an array within another array ...
      ... Dim rs As DAO.Recordset ... ' Set the number of Columns = number of Fields in recordset ... ' Load the ListBox with the retrieved records ... optionbutton1 - in which I choose the column I need to search the string ...
      (microsoft.public.word.vba.general)
    • Re: Combo box
      ... this routine loads a listbox with client details stored in a table in ... ' Open the file containing the client details ... Dim MyArray() As Variant ... ' Set the number of Columns = number of Fields in recordset ...
      (microsoft.public.word.vba.general)