Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-03-2019, 11:57 AM   #1
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Combo Box Length Limit?

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?

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-03-2019, 12:03 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,568
Thanks: 50
Thanked 1,048 Times in 1,029 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Combo Box Length Limit?

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
Frothingslosh (01-03-2019)
Old 01-03-2019, 12:24 PM   #3
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Combo Box Length Limit?

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!

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-03-2019, 12:34 PM   #4
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,137
Thanks: 3
Thanked 466 Times in 459 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Combo Box Length Limit?

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.
Cronk is offline   Reply With Quote
Old 01-03-2019, 12:50 PM   #5
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Combo Box Length Limit?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-03-2019, 01:22 PM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,352
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Combo Box Length Limit?

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."
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 01-03-2019, 01:27 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,352
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Combo Box Length Limit?

Did a follow-up search, found this article:

https://bytes.com/topic/access/answe...ield-combo-box

It is from 2007, so this is not a new issue. Maybe it will help you but maybe not.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
Frothingslosh (01-03-2019)
Old 01-03-2019, 05:40 PM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 10,354
Thanks: 112
Thanked 2,843 Times in 2,593 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Combo Box Length Limit?

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-03-2019, 08:22 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,352
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Combo Box Length Limit?

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.

Last edited by The_Doc_Man; 01-05-2019 at 10:55 AM. Reason: fixed a typo
The_Doc_Man is online now   Reply With Quote
Old 01-04-2019, 04:30 AM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,784
Thanks: 34
Thanked 538 Times in 511 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Combo Box Length Limit?

Quote:
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.
MajP is online now   Reply With Quote
Old 01-04-2019, 04:49 AM   #11
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Combo Box Length Limit?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 01-04-2019, 05:53 AM   #12
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,352
Thanks: 87
Thanked 1,642 Times in 1,524 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Combo Box Length Limit?

Oh, yeah, I remember the type. Drama Queen through and through. Good luck with THAT user, Frothy.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 01-04-2019, 06:39 PM   #13
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 932
Thanks: 10
Thanked 189 Times in 179 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Combo Box Length Limit?

several reasons why memo fields get truncated
Note especially the last one listed in the web page table


http://allenbrowne.com/ser-63.html
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
The_Doc_Man (01-05-2019)
Old 01-05-2019, 04:43 AM   #14
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 12,127
Thanks: 81
Thanked 2,009 Times in 1,957 Posts
jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light jdraw is a glorious beacon of light
Re: Combo Box Length Limit?

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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
jdraw is offline   Reply With Quote
Old 01-05-2019, 04:44 AM   #15
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,211
Thanks: 83
Thanked 456 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Combo Box Length Limit?

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Reply

Tags
combo box , combo box fields display

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel VBA - Limit on string length using Mailto: and shell32.dll wdhewitt Modules & VBA 0 01-09-2013 06:30 PM
Image Size - Scrolling and Length Limit Geezer Forms 1 07-14-2008 06:34 PM
Limit Character length with combo tl mike Modules & VBA 9 03-14-2008 02:55 PM
Import Record 495 character Length Limit? Kevin Crowley Tables 2 05-04-2005 08:39 AM
get round limit in the path length D.Mair Modules & VBA 9 01-18-2002 03:23 AM




All times are GMT -8. The time now is 06:11 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World