Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Posts
    14

    Question Unanswered: List Box row source limit of 2,048

    Help please!

    When you use Value List as the Row Source Type in a list box, the Row Source string limit is 2,048.

    Is there a way to circumvent this?

    I don't want to use a Select statement since the data will not be in a table. And I don't want to create a table and append the data to it just for the list box since the database is multi-user.

    Any ideas?
    Last edited by dkuttler; 11-26-02 at 17:12.

  2. #2
    Join Date
    Nov 2002
    Posts
    150

    Re: List Box row source limit of 2,048

    Originally posted by dkuttler
    Help please!

    When you use Value List as the Row Source Type in a list box, the Row Source string limit is 2,048.

    Is there a way to circumvent this?

    I don't want to use a Select statement since the data will not be in a table. And I don't want to create a table and append the data to it just for the list box since the database is multi-user.

    Any ideas?
    Wow, that's alot a values. Sorry but all you can get in using a Value List is 2048. What is your concern for putting the data in a table? You could setup security and only allow changes for an Admin.

  3. #3
    Join Date
    Nov 2002
    Posts
    14

    Re: List Box row source limit of 2,048

    Originally posted by FirstAndGoal4
    Wow, that's alot a values. Sorry but all you can get in using a Value List is 2048. What is your concern for putting the data in a table? You could setup security and only allow changes for an Admin.
    The list box is populated on the fly. It actually is listing Outlook post items in a public folder (displays the Subject field to the user and inserts the EntryID property of the Outlook item in the hidden column of the list box) Then when the user selects an item, it opens the Outlook post item using the EntryID in that hidden column. If I where to append all of the EntryID's and Subject lines in a table, there would be conflict when another user attempts to open the same form with the list box in it. The list of Outlook items is not static since Access is filtering the list (using the Restrict method in the Outlook object model) according to the record you are on when you run the form with the list box on it. In other words, I have a public folder with thousands of Outlook items in it. Each of those items have the BillingInformation property set to an Access record ID number to facilitate this filtering by record.

    Sorry for the mouthful, but I'm stumped on the row source limit. The Outlook EntryID property happens to be a very long string!

  4. #4
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: List Box row source limit of 2,048

    Originally posted by dkuttler
    The list box is populated on the fly. It actually is listing Outlook post items in a public folder (displays the Subject field to the user and inserts the EntryID property of the Outlook item in the hidden column of the list box) Then when the user selects an item, it opens the Outlook post item using the EntryID in that hidden column. If I where to append all of the EntryID's and Subject lines in a table, there would be conflict when another user attempts to open the same form with the list box in it. The list of Outlook items is not static since Access is filtering the list (using the Restrict method in the Outlook object model) according to the record you are on when you run the form with the list box on it. In other words, I have a public folder with thousands of Outlook items in it. Each of those items have the BillingInformation property set to an Access record ID number to facilitate this filtering by record.

    Sorry for the mouthful, but I'm stumped on the row source limit. The Outlook EntryID property happens to be a very long string!
    You may want to look into using a call-back routine to populate a dynamic list of values into your control. This should get you past the 2048 limit and hopefully avoid the conflicts you're concerned about. You create and array of your data (try using your Restrict method to create it) and then use a reference to the call-back routine as your list source. The call-back routine populates whatever is in the arrayinto the control. It looks a little mysterious at first, but once you get the hang of it, it is a powerful technique.

    I have several books that show how to do that, but I don't know of an on-line reference (yet).

    If you can find a copy, try:
    Mastering Microsoft Access 2000 Development by Allison Balter.

    In the meantime, I'll try to see if I can find an on-line topic. Stay tuned ...
    Regards,
    Terry

  5. #5
    Join Date
    Nov 2002
    Posts
    14

    Re: List Box row source limit of 2,048

    Originally posted by spraguetr
    You may want to look into using a call-back routine to populate a dynamic list of values into your control. This should get you past the 2048 limit and hopefully avoid the conflicts you're concerned about. You create and array of your data (try using your Restrict method to create it) and then use a reference to the call-back routine as your list source. The call-back routine populates whatever is in the arrayinto the control. It looks a little mysterious at first, but once you get the hang of it, it is a powerful technique.

    I have several books that show how to do that, but I don't know of an on-line reference (yet).

    If you can find a copy, try:
    Mastering Microsoft Access 2000 Development by Allison Balter.

    In the meantime, I'll try to see if I can find an on-line topic. Stay tuned ...
    Thanks for the tip, Terry. I'll keep trying.

  6. #6
    Join Date
    Oct 2002
    Location
    Rochester, NY
    Posts
    84

    Re: List Box row source limit of 2,048

    Originally posted by dkuttler
    Thanks for the tip, Terry. I'll keep trying.
    Here's a web page that demonstrates a VERY complicated Callback function . It's probably a lot more than what you'll need. I can't find much on the web, but try the Balter book I recomended above. It's pretty good and covers a lot of different topics. I use it a lot.
    Regards,
    Terry

  7. #7
    Join Date
    Nov 2002
    Posts
    150

    Re: List Box row source limit of 2,048

    Originally posted by dkuttler
    Help please!

    When you use Value List as the Row Source Type in a list box, the Row Source string limit is 2,048.

    Is there a way to circumvent this?

    I don't want to use a Select statement since the data will not be in a table. And I don't want to create a table and append the data to it just for the list box since the database is multi-user.

    Any ideas?
    Alright, I think I have it. In the combobox properties, set RowSourceType to FillCombo and RowSource to blank. Create the following function in your form or a module and tweak as needed (such as dynamically allocate space for variable dbs and looping through it).

    Function FillCombo(fld As Control, id As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Static dbs(3000) As String, Entries As Integer
    Dim ReturnVal As Variant
    ReturnVal = Null
    Select Case code
    Case acLBInitialize ' Initialize.
    Entries = 0
    dbs(Entries) = CStr(Entries)
    Do Until dbs(Entries) = "" Or Entries >= 2999
    Entries = Entries + 1
    dbs(Entries) = CStr(Entries)
    Loop
    ReturnVal = Entries
    Case acLBOpen ' Open.
    ReturnVal = Timer ' Generate unique ID for control.
    Case acLBGetRowCount ' Get number of rows.
    ReturnVal = Entries
    Case acLBGetColumnCount ' Get number of columns.
    ReturnVal = 1
    Case acLBGetColumnWidth ' Column width.
    ReturnVal = -1 ' -1 forces use of default width.
    Case acLBGetValue ' Get data.
    ReturnVal = dbs(row)
    Case acLBEnd ' End.
    Erase dbs
    End Select
    FillCombo = ReturnVal
    End Function

  8. #8
    Join Date
    Nov 2002
    Posts
    14

    Re: List Box row source limit of 2,048

    Originally posted by spraguetr
    Here's a web page that demonstrates a VERY complicated Callback function . It's probably a lot more than what you'll need. I can't find much on the web, but try the Balter book I recomended above. It's pretty good and covers a lot of different topics. I use it a lot.
    Thanks again for the help. I was able to get something working.

    The only difficulty I run into at times has to do with my array. I redim the array depending on how many records my restrict statement to Outlook returns. I have two columns on my list box, 1 for the Outlook item's EntryID, and the 2nd for the subject line. So after getting the count from my restrict statement I redim like:
    ReDim LstCol(2, MyItems.Count)
    This works fine, usually. But sometimes I get an Array Out of Bounds error because for some reason the callback function is looping more times than the number of records even though I specify acLBGetRowCount as MyItems.Count.

    I'm definately on the right track, though. I'll keep working on it, I'm sure it's something stupid as always.

    dk

  9. #9
    Join Date
    Nov 2002
    Posts
    150

    Re: List Box row source limit of 2,048

    Originally posted by dkuttler
    Thanks again for the help. I was able to get something working.

    The only difficulty I run into at times has to do with my array. I redim the array depending on how many records my restrict statement to Outlook returns. I have two columns on my list box, 1 for the Outlook item's EntryID, and the 2nd for the subject line. So after getting the count from my restrict statement I redim like:
    ReDim LstCol(2, MyItems.Count)
    This works fine, usually. But sometimes I get an Array Out of Bounds error because for some reason the callback function is looping more times than the number of records even though I specify acLBGetRowCount as MyItems.Count.

    I'm definately on the right track, though. I'll keep working on it, I'm sure it's something stupid as always.

    dk
    Try specifying acLBGetRowCount as MyItems.Count - 1.

  10. #10
    Join Date
    Nov 2002
    Posts
    14

    Re: List Box row source limit of 2,048

    Originally posted by FirstAndGoal4
    Try specifying acLBGetRowCount as MyItems.Count - 1.
    As it turns out, my error was a simple matter of timing. The OnOpen event of the form where my list box is would go out and retrieve the items collection from Outlook and then set a variable to the number of items...this would then be used to specify the acLBGetRowCount.

    The problem was that the Callback function was already running before the OnOpen even could finish its work and set a new value for the variable. So when the Callback function was running, acLBGetRowCount was still set to a number from the previous time it ran.

    I solved this by removing the RowSourceType property of the list box that referenced the Callback function. Then I set the property in my code AFTER the items collection was finished the the variable for acLBGetRowCount was set to the right value. This fixed the timing problem.

    Thanks all for the help.

Posting Permissions

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