Combo Box Length Limit? (1 Viewer)

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
Okay, I'm stumped.

Is there any way to display more than 255 characters in a combo box? This is for Access 2016, for what it's worth.

The reason is that in an application I inherited, there is a method for users to update the 'Comments' field on multiple records at once. During this update process, a form is opened, then a front-end table is populated with all comments used in all records assigned to this recovery. (Ultra-quick info: A recovery is a group of records being reviewed that are all grouped together in some way. The grouping criteria are rather arbitrary.) These comments can range from a quick '1/3/19 - Outside filing limits' all the way up to a small novel - the field behind them is a SQL Server varchar(2000) field, which Access reads as LongText.

What's happening is that when a comment is over 255 characters in length, it's truncated at 255. The table contains the entire comment, no matter the size. The query behind the combobox passes the entire comment. The combobox itself, however, never shows more than 255 characters.

I even tried building a value list instead of using a query, and got the same result.

Is this just a case where I'm SOL due to engine limitations?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:18
Joined
Oct 29, 2018
Messages
21,357
Hi. My humble opinion is you might consider using another method besides a combobox. For example, maybe you can use a continuous form. You might be able to construct it to mimic a combobox.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
They're probably going to have to learn to live with it then. This is a convenience deal meant to allow them to take preexisting comments and apply them to new records, and I can't justify to my boss the time it would take to create and test a new form, especially for a convenience feature where the issue only arises maybe 10% of the time.

Thanks, though!
 

Cronk

Registered User.
Local time
Tomorrow, 00:18
Joined
Jul 4, 2013
Messages
2,770
I wonder the purpose of the combo box with a memo field as a row source. Surely not to select a record. If for display, then for long comments, you could use a list box but then you are limited to the physical width of the screen.


I'd either use a continuous sub form with a large text box, or where screen real estate has been limited, a smaller text box containing truncated date with elipses appended to the visible end (to indicate continuation), with a zoom box activated by single/double click.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
From what I can tell, the original dev created it so that users could just select previously entered comments and apply them to the records that were selected when the form is opened.

The combo box itself is based on a staging table that is populated with all comments already applied to the indicated recovery. Basically, they open the form, either enter a new comment or use the combobox to select an existing comment, then hit OK and apply the comment to the selected records. The only issue is that the box truncates text at 255 characters, so long comments get cut off when the box is loaded. (Saving long strings works just fine as long as they don't exceed the field length of 2000 characters.)

I'm afraid even the truncated data with ellipses and a zoom pop-up would require a large enough rewrite that the boss would get twitchy. And I guarantee you that the primary user (of 5) would scream, because she wants it either left alone or fixed to display the entire saved comment. She's...picky...and unbelievably resistant to change.

I'm going to just let her know that it can't be fixed at this time. If it becomes a big issue, she can go through the normal process for requesting application changes - I have too many other updates and fixes on my plate to spend any more time on this without a documented request so I can justify my time on it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 28, 2001
Messages
26,998
Think I have to agree with you, Frothingslosh. At some point you have to be able to tell the user "no, because it would cost too much to implement."
 

isladogs

MVP / VIP
Local time
Today, 13:18
Joined
Jan 14, 2017
Messages
18,186
I also would never use a lengthy text field, let alone a memo, in a combo

Interestingly no combobox field size limit appears in Access specifications

If you try to use the wizard to set a combo row source based on a table or query, the memo fields are not shown - obviously the intention is not to use them
However you can add memo fields manually by clicking the ellipsis and specifying the SQL.
As you say the field is truncated to 255 characters & there appears to be no way of preventing it

In your case I think I would remove that field & just place a textbox next to the combo to display the full contents of the memo field after the combo value is selected.
Development time just a couple of minutes at most...then everyone should be happy :D
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 28, 2001
Messages
26,998
If I may enter into a little bit of conjecture, I recall many cases in this forum where things get truncated to 255 bytes during processes that involve importation or implied text conversion. It almost seems that some hidden function exists that executes a Short Text conversion when picking up a value from a table that isn't used directly.

USUALLY a combo box is fed by a .RowSource that is really a query built by the wizard that built the combo box, so that is a sort of indirect use. I'm not sure we have ever figured out completely WHY the truncation occurs, but every time I have seen the problem, you have to avoid the import/export. In such cases, converting to use an Excel App Object allowed full field transfer.

This is OBVIOUSLY not an Excel transfer situation. I'm not saying it is. But I am kind of "seconding the opinion" that maybe a more direct but delayed pickup of the long text field might be required for the truncation to not occur.
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:18
Joined
May 21, 2018
Messages
8,463
It almost seems that some hidden function exists that executes a Short Text conversion shen picking up a value from a table that isn't used directly
It is not limited to a combo based on a recordsource. It will also truncate if you load it in code via additem or like the OP pointed out via a value list.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
Yeah, the problem is that you don't know the primary user.

Making the suggested changes involving a textbox would cause more drama than I really care to deal with. This is one of those people who is utterly against any form of change, and she outranks me enough to turn this into one giant political mess if I make a change even this minor.

Seriously. She's THAT user. And I have plenty of other stuff to do anyway.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:18
Joined
Feb 28, 2001
Messages
26,998
Oh, yeah, I remember the type. Drama Queen through and through. Good luck with THAT user, Frothy.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:18
Joined
Jan 23, 2006
Messages
15,362
Frothy,
Yes we've all had at least 1 of those - and they're not always female.
Sounds like the text box mentioned by Colin might be practical.
It also sounds like there might be or could be some hierarchical structure to the comments. I find it hard to believe comments are all unique and could not be categorized and refined through some analysis. But of course that's counter to the user's mind set and plan. I'm think cascading combos to identify specific records for change, but that's not likely to be feasible.

Some sample data or database with a few records might be helpful for more focused comments.

Good luck.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
Yeah, saw that. Don't like 'because it's a memo field' as an explanation. Also was hoping that things might have been improved between Access 2003 and Access 2016.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
Jdraw - that's why I said 'that user' and not 'that woman'. This time the User From Hell just happens to be a woman. Last place I worked, it was a guy.

The comments are hand-written explanations of why this particular case was rejected by Medicare, and are required to be precisely what we were told for legal reasons. This mechanism is there so that when CMS provides us with a novel, they don't have to type it individually for a thousand or more different claims. The lookup portion is because the same reason might be provided for different people or claims after calls to CMS on different days.

TBQH, I'm far more likely to just remove the whole 'select previous comments' thing altogether and tell them to continue using copy and paste than I am to go against the express instructions of my department manager and completely rework this form from the ground up without authorization (from either users or management) the way people are suggesting here. What I'm most likely to do, however, is leave it alone and tell them to suck it up.

I spent an hour of work looking into determining if a fix was possible. It isn't, and the suggestions here not only would absolutely be rejected by a user who would turn the entire thing into a political clusterf*** of epic proportions, but implementing them would directly violate the manager's instructions that significant changes may only be performed after approval from users and management. I could have gotten away with making the box stop cutting off the text because that could be called a fix. I cannot get away with actually changing the UI without good reason, and this is not good enough reason, since they already have a workaround (they just copy and paste from the original comment).
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
Sorry, guys, I'm grouchy and irritable this morning for some reason. (Moreso than normal.)

I do appreciate the suggestions, even though I can't implement them due to other concerns.
 

AccessBlaster

Registered User.
Local time
Today, 06:18
Joined
May 22, 2010
Messages
5,823
What's happening is that when a comment is over 255 characters in length, it's truncated at 255. The table contains the entire comment, no matter the size. The query behind the combobox passes the entire comment. The combobox itself, however, never shows more than 255 characters.

Would it be possible to use NVARCHAR(MAX) as your field size? I have never tried it because the goal is to be stingy on field size.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 09:18
Joined
Oct 17, 2012
Messages
3,276
According to one of the links provided earlier, combo boxes just truncate text fields at 255, period.
 

Jbooker

New member
Local time
Today, 09:18
Joined
May 12, 2021
Messages
25
As noted combo box column text properties are limited to 255 characters. It's common practice to copy the value of a combobox or one of it's columns into another field because it often saves a db round trip. It's important to consider memo fields when doing so.

I know this is an old post, but I'll use the OP case to illustrate an easy work around...

For example, if your combobox selects from a list of prior comments and populates the comment field on the existing record upon selection, then the combo box is either bound to the comment field of the current record (Control Source) or it uses an event to populate the comment using the combo selection. The latter case would be best in this case so there is likely an event in the AfterUpdate procedure of the combobox that executes something like either
Code:
Me!Comment = Me.Combo1
or
Code:
Me!Comment = Me!Combo1.Column(1)
This code will only copy the first 255 characters due to the combo text limit.

You could fix this by simply doing a lookup to get the full Comment from the table like:
Code:
Me!Comment = DLookup("Comment", "PriorComments","ID=" & Me!Combo1)
where 'PriorComments' is the record source of the combo box having a unique field 'ID' in the first column (often hidden).

This creates another db round trip but is an easy one minute fix.

Another alternative to avoid the extra db query would be to split the memo into more than one column of the combo box and concatenate them in the AfterUpdate procedure like:

Code:
Me!Comment = Me!Combo1.Column(1) & Me!Combo1.Column(2)
Now you have 2 times 255 max length.

HTH,
Josh
 
Last edited:

Users who are viewing this thread

Top Bottom