Changing the Selection Fields in a Combo Box

[Terry]

Hi,
I've created a Combo Box using the Controls Wizard. In creating the
control, I specified that my unique value to be stored in the table is an ID.
When the user views the selection options in the Combo Box, I'd like to have
the ID field hidden and only see the text values, while still storing the ID
in the appropriate column.
This is probably a second question.
Also, when the user views a form that has been populated, I'd like them to
see the actual textual values on the form, as just the stored ID value. Is
that possible to do with the one Combo Box or is that two controls? If it's
two, how do you not see the empty second control when initially populating
the form?

 

[Arvin Meyer [MVP]]

The bound column (ID) should be the first one and all you need to do is to
set the column width to 0". If you have more than 2 columns, and want to see
them when the combobox is not dropped down, you'll need to use unbound
textboxes and set the controlsource to the column index, like:

= MyCombo.Column(2)

for the third column. Any columns that you do not want to see, set the
column width to zero, so in the above example of 3 columns with 1 visible:

0";1.5";0"

 

[John W. Vinson]

Hi,
I've created a Combo Box using the Controls Wizard. In creating the
control, I specified that my unique value to be stored in the table is an ID.
When the user views the selection options in the Combo Box, I'd like to have
the ID field hidden and only see the text values, while still storing the ID
in the appropriate column.

A combo has several interacting properties.

Its RowSource is usually a Query which can have up to 255 fields, 65536 rows.
Normally both would be much smaller.

Its Bound Column specifies which of those fields will be stored.

It has a Control Source - the name of the field into which the selected value
is stored.

The Column Count specifies how many fields will be included when the combo is
dropped down.

The ColumnWidths is a string of widths (in centimeters or inches) for the
fields in the ColumnCount - a width of 0 means that the corresponding field is
available (say as the Bound Column) but will not be displayed.

The first nonzero width column is displayed when the combo is *not* dropped.

So if you have the ID in column 1, and two additional text values, you could
use a column count of 3; a bound column of 1 (the ID); and a columnwidths
property like

0";1.5";0.5"

to display both text fields when the combo is dropped, and only the first when
it's not.

 

[Terry]

Thank you for an excellent answer and background. Is there a website or book
that you can point me to that has the same kinds of information on control
properties? Access Help sometimes only has one-liners that really don't
provide enough detail.
Thanks again.
Terryomsn

 

[John W. Vinson]

Thank you for an excellent answer and background. Is there a website or book
that you can point me to that has the same kinds of information on control
properties? Access Help sometimes only has one-liners that really don't
provide enough detail.

You might want to look at two very thourough books: _Access <version> Inside
Out_ by my friend and mentor John Viescas; and the huge, deep and dense two
volume _Microsoft Access 2002 Developer's Handbook_ by Getz, Litwin and
Gilbert. It has not been updated to 2007 but the vast bulk of what it contains
is still very much valid and useful. Lots more resources at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials