Results 1 to 11 of 11

Thread: List Boxes (1)

  1. #1
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Boxes (1)

    In Access 97 there is not an additem funtion for list boxes. Can anyone give me a hint how to populate a list box from a recordset, where the string will be more than 255 characters.

    Thank you

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    IIRC, to add items to a list box or a combo box in Access, you need to use a callback function.

    I've never done anything like this before, but this may give you what you need.

    Have a look at http://www.mvps.org/access/forms/frm0049.htm for a multicolumn listbox callback function.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    Bryan,

    Thanks for th hint, though I don't fully understand this.
    Is this function call ment to go in the rowsource of the list box?

    Nobody knows if their is a way jsut to loop through the recordset to set the values?

  4. #4
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    Yes, the callback function is the RowSource of the ListBox.

    In Access, AFAIK, you can't set individual values in a ListBox like you can in VB.

    I think that's why you need to use a CallBack function.

    I have never used them before, so I can't really help, other than point you to that page.

    Hopefully someone else may be able shed more light for you.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (1)

    Are you building a SQL string that contains more than 255 characters, passing in a value list, or what? Have you tried to use a longer string and received an error message? If so , what was the error?

    You can populate a listbox or combobox using a function, a saved query, or other methods including the callback function Bryan mentioned, and all of those get around any 255 character limits. It isn't clear what you mean by populating the list "from a recordset", but if you explain further there may be more possibilities. If you decide on a callback, you can find a thread on it here that might help, and on-line help provides information and an example if you look up RowSourceType.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    Charllotte,

    Our first attempt was to populate the listboxs creating a string from our recordsets, but we did hit the 255 character mark. The reason we do not want to work with storing an SQL string in the row source is due to the fact that we are using ADO as our connection and want to move away from linked tables. This is causing the problem, if we worked with linked tables then we could set the lists box rowsource easily.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (1)

    In any case, you can't set a combo or listbox to a recordset. In Access, those controls are more akin to ActiveX controls than to the stripped down versions you find in VB or MS Forms. If you can wait til this evening, I'll post an A97 demo that I'll have to convert from 2000 to show you how you can do it.
    Charlotte

  8. #8
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    I shall wait Charlotte.

    Thanks again.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (1)

    OK, here it is. You need to be aware that depending on what is installed on your machine you may have reference problems or need to tweak the code to use the Jet 3.51 OLEDB provider instead of Jet 4.0. I used ADO 2.5, since 2.1 is next to useless and 2.5 is the next lowest common denominator. Plat around with it and yell if you have problems.
    Attached Files Attached Files
    Charlotte

  10. #10
    Star Lounger
    Join Date
    Jan 2002
    Location
    London, Gtr London, England
    Posts
    63
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Boxes (1)

    Just one further question, do you think this would also work for reports? I have a
    colleague asking me and I am unsure as I haven't done much work in access
    reports, most my programming exports to word or excel.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: List Boxes (Access 97)

    Would *what* work with reports? Listboxes, ADO recordsets, callbacks or something else? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    It is possible to create unbound reports, but the coding is extensive and it's not suitable for all kinds of reports. You can use a listbox on a report, but I don't see the point. Access reports in mdbs don't have an exposed recordset property, so you can't create an ADO recordset and then bind a report to it. Callbacks have a variety of uses but I don't see how they apply here except to populate a list or combobox.

    I'm sorry I can't give you a better answer, but f you ask a more specific question I'm sure someone can give you a useful response.
    Charlotte

Posting Permissions

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