show the unbound column from a drop-down list in the form

[jacchops]

I have an "options" table that I use to populate a drop-down box in another
tables field. In my options table... there are two fields: Option_Number and
Option_Text. I want my table to store the option_number associated with the
option_text, so that when queries are run, I can search for the number
instead of typing out the whole text.
When my users enter data on a form, the drop-down box shows them the
option_number and the option_text. But once they select the option, the
number is the one that appears on the form. Can I get the form to show the
text and not the number, but only store the number in the table? On the form,
I tried changing the field's properties bound column from 1 to 2 (since
column 1 is the number and 2 is the text), but then nothing shows up in the
field on the form.

 

[storrboy]

In the control properties (on the form never the table), the first tab
contains a few Column(*) properties. Set the Column widths in inches
using a semi-colon to separate them. Any column not specified will use
the remaining space.

IE: 1.5;2;.25;.5;;6

The bound column is the value the combobox holds. This must be the
value you wish to use or write to a table. The Column Count property
should equal the number of columns in the query to properly use the
widths setting.

 

[George Nicholson]

The Bound Column property specifies the value to be stored in the source
table. In this case you want it to be 1 (the numeric value). It has no
effect on what displays.

The Column Widths property controls what displays. You can specify a zero
value for columns that you wish to hide from the user during selection
(i.e., while dropped-down). The first non-zero column is what will appear in
the combo box after selection.
Sounds like you want to set this = 0, 1.5 (or whatever width is appropriate
to display the 2nd column). This way the user will never see the numeric
value, only the text.

If you want the user to see both the number and text during selection, but
only the text afterwards, then you need to switch their positions so that
text is col 1, number is col 2. Then set Bound Column = 2 and Column Widths
= 1.5, 1.

In any case make sure you set Column Count to 2.

HTH,

 

[Cheese_whiz]

Hi jacchops,

If you want to display the text but save the number, and your select query
that populates the combo box uses number then text (name), then you need the
following Property settings:

Column Count: 2
Bound Column: 1
Column Widths: 0", 1" *This is adjustable to need

That will save the number and display ONLY the text/name. If you want to
display both then use something like 1",1" for the column widths (though it
doesn't sound necessary/useful in your situation).

I'm not following what's happening in terms of this quote from your post:

"But once they select the option, the number is the one that appears on the
form."

Where does it appear on the form? Does it appear somewhere else other than
the combo box where they make the option selection? I don't get it.

CW

 

[jacchops]

Is there any way to leave the columns in the order that they currently are (1
for number, 2 for text) so that I can see the number first then text in the
drop down box, but only the text is stored? I ask this because the text is
often very long, so I'd rather show the number first. Otherwise my drop-down
box is going to take up the entire width of the screen.

Thank you for your previous response. Very articulate!!

 

[George Nicholson]

I can't think of a way to display ID & Text as drop-down but only Text when
at rest unless you reverse the order of ID & Text (which you say you don't
want).

However, there is a way to display ID & Text in *both* drop-down & at-rest
states. Create a query that you will use for the ComboBox.Rowsource. In
this query, include your ID, Text and a 3rd field that combines the ID &
Text. It is the 3rd, "Combined", field that will display to the user int
both states, while only the ID gets stored.

Below is an example of the SQL for such a query (which I name qcboRecTypes):

SELECT RecTypeID, RecType, [RecTypeID] & " - " & [RecType] AS Combined
FROM datRectypes;

Row Source: qcboRectypes
Column Count = 3
Bound Column = 1
Column Widths: 0,0,2

HTH,

 

[jacchops]

PERFECT!!! I always forget about populating my drop-down boxes with queries.
That was really helpful. Thank you so much for your quick response!!

I can't think of a way to display ID & Text as drop-down but only Text when
at rest unless you reverse the order of ID & Text (which you say you don't
want).

However, there is a way to display ID & Text in *both* drop-down & at-rest
states. Create a query that you will use for the ComboBox.Rowsource. In
this query, include your ID, Text and a 3rd field that combines the ID &
Text. It is the 3rd, "Combined", field that will display to the user int
both states, while only the ID gets stored.

Below is an example of the SQL for such a query (which I name qcboRecTypes):

SELECT RecTypeID, RecType, [RecTypeID] & " - " & [RecType] AS Combined
FROM datRectypes;

Row Source: qcboRectypes
Column Count = 3
Bound Column = 1
Column Widths: 0,0,2

HTH,

Is there any way to leave the columns in the order that they currently are
(1
for number, 2 for text) so that I can see the number first then text in
the
drop down box, but only the text is stored? I ask this because the text is
often very long, so I'd rather show the number first. Otherwise my
drop-down
box is going to take up the entire width of the screen.

Thank you for your previous response. Very articulate!!