How many characters can be displayed in a combo box?

[John Oswald]

Access 2003. What is the maximum number of alpha-numeric characters that can
be displayed in a combo box?

I have a combo box on a form. The ControlSource of the combo box is tied to
LongInteger field in a table. The RowSource of the combo box is an SQL
statement that selects two columns from another small (<100 rows) table. In
form view, the combo box is set to hide the first column, and display the
second. The first column, an autonumber type, is the BoundColumn for the
combo box. The second column, pulls data from a memo field. In the Query
Builder, displaying the RowSource SQL statement in datasheet view, the second
column shows the full text of each row pulled from the Memo field, i.e., the
complete text string whether it is less than or greater than 255 characters.
However, displaying the combo box in form view, the rows of the second column
are always clipped to a maximum of 255 characters.

Are Access combo boxes fundamentally restricted to displaying a maximum of
255 characters? or is there a property or work around that I'm overlooking
that can be used to display longer strings in a combo box?

 

[Jeff Boyce]

John

So you are saying that you want your users to read (potentially) way more
than 255 characters before deciding which item to select from the combobox?
That doesn't seem particularly user-friendly. Is there a way to include a
brief (one/two word) short-title for the user to pick? This short-title
could be added to the table that holds the memo field to make selection
easier.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

On Thu, 2 Aug 2007 08:36:07 -0700, John Oswald <John

Are Access combo boxes fundamentally restricted to displaying a maximum of
255 characters?

Yes. Having a combo box display a full memo field would be impractical - a
memo field can contain *two billion* characters, and a combo box can display
65536 rows... try multiplying those and fitting the result on a screen !

or is there a property or work around that I'm overlooking
that can be used to display longer strings in a combo box?

Put a textbox on the form using DLookUp to look up the memo value: set its
control source to

=DLookUp("[memofieldname]", "[tablename]", "[ID] = " & [comboboxname])

assuming that the bound column of the combo box is the primary key of the
table containing the memo.

John W. Vinson [MVP]

 

[John Oswald]

Jeff,
Thanks. The form in question is only used by myself in organizing
information relating to certain aspects of my research (I'm a
university-based entomologist) -- so the "other users" problem is moot. I
agree completely with your general comments, but in this particular form it
would be useful to me to be able to view the complete long strings in the
combo box space as the result of a combo box pick. John Vinson's response
indicates that this is not possible.
Cheers,
John

 

[John Oswald]

John,
See below...

On Thu, 2 Aug 2007 08:36:07 -0700, John Oswald <John


Yes.

-----Well, that's the short answer I needed to hear! But see farther below...

Having a combo box display a full memo field would be impractical - a
memo field can contain *two billion* characters, and a combo box can display
65536 rows... try multiplying those and fitting the result on a screen !

or is there a property or work around that I'm overlooking
that can be used to display longer strings in a combo box?

Put a textbox on the form using DLookUp to look up the memo value: set its
control source to

=DLookUp("[memofieldname]", "[tablename]", "[ID] = " & [comboboxname])

assuming that the bound column of the combo box is the primary key of the
table containing the memo.

-----I had used this technique successfully before on forms that were
displayed in Form View, but had never knowingly gotten it to work
successfully on a form displayed in Datasheet View (and, because of this, I
had previously assumed [quite wrongly!] that this wouldn't work in Datasheet
view). With your suggestion, however, I played with this a bit further. The
control source expression format that you suggested did not work directly.
With that expression format the new text field on the form displayed "#Error"
in both Form and Datasheet Views. But, by accounting for the possibility of
Null values in [comboboxname] with the expression format:

=IIf(IsNull([comboboxname]),Null,DLookUp("[memofieldname]", "[tablename]",
"[ID] = " & [comboboxname]))

the control source expression works like a charm. This will be very useful
in other parts of my database... Thanks for the idea.