Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    list box width limit? (XP)

    Background:
    I have a list box whose record source is a query based on a linked SQL Server 2000 table. The query and list box have 3 fields: (1) an ID field (2) A UserName field, and (3) a Notes field; only column 2 is visible to the user. Next to the list box is an unbound, locked text box. When the user selects a UserName in the list box, code executes loading the Notes in the locked text box ( a look, but don't touch approach). The code is:<pre>Me.txtSpecifiedNote = Me.lstAllNotes.Column(2)</pre>

    The datatype of the Notes field in the underlying SQL table is nvarchar with a length of 500, though the linked table believes the datatype to be memo; I suppose that's as close as Access can get.

    Here's the problem:
    If a user chooses a note than contains 500 characters, only 255 characters are displayed in the text box. As far as I can tell, there is no limit associated with the text box control. If I open the list box's query, the full note of 500 characters is displayed. The finger seems to be pointed at the .Column property of the list box. Is a list box capped at 255 characters? (which, not incidentally I'm guessing, is the max. characters in the Access datatype of Text) <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: list box width limit? (XP)

    Hi Shane,
    My guess is it isn't the list box property that's truncating the field, but the query that is sorting the returned records. If you attempt to sort on a memo field it will truncate to 255 characters. Also does your field type need to be nvarchar - if you switch it to varchar, I think Access will recognize it as a text field that has 500 chars. Post back if you prove my theory wacko, and I'll delve into it more.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: list box width limit? (XP)

    You wrote:
    >>The finger seems to be pointed at the .Column property of the list box. Is a list box capped at 255 characters? (which, not incidentally I'm guessing, is the max. characters in the Access datatype of Text) <<


    I'd have to try it myself to be sure, but I think you are right in suspecting the .column property is the cause. And yes, an Access Text datatype is limited to 255 characters. If possible, you might want to use the DLookup fucntion in controlsource of the textbox, based on the value of the listbox.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: list box width limit? (XP)

    Thanks, guys, for posting responses. As it's an app for our IT group, it doesn't get a lot of attention so I'm only getting back to it today. Shoemaker's kids, y'know!

    I checked the underlying query, and confirmed that it is returning the full entry in the field, so that's ruled out. I changed the data type from nvarchar to varchar, but no dice there either. I changed the AfterUpdate event to use DLookup with the criteria from the list box selection, and VIOLA! All this leads me to believe that the maximum number of characters you can use in a single column in a list box, and probably a combo box as well, is 255 characters, though I can't find definitive proof in the documentation.

    DLookup's performance seems to be just fine right now; it's based on a linked SQL 2000 table with only a couple thousand records. I fear that performance will degrade as the record set grows, so I'll probably create a stored proc, pass it the NoteID from the list box, and return the value into the text box for display.

    Thanks again for your help! Cheers! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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