list box width limit? (XP)

Topic

New Reply

Background:[/u]
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:

Me.txtSpecifiedNote = Me.lstAllNotes.Column(2)

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:[/u]
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)

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote