Combo Box Lookup Value

I have created a form with combo boxes to lookup data from other tables. The
data updates to another table that I am trying to use in a query. When I run
the query, it returns the numerical value instead of the text value. The Row
Source on the combo box is a select query for the text value and when I use
the drop down on the form, it displays the text value. How can I change the
format in the query to show the text value?

On Sun, 11 Oct 2009 17:38:01 -0700, ADonMS
wrote:

I have created a form with combo boxes to lookup data from other tables. The
data updates to another table that I am trying to use in a query. When I run
the query, it returns the numerical value instead of the text value. The Row
Source on the combo box is a select query for the text value and when I use
the drop down on the form, it displays the text value. How can I change the
format in the query to show the text value?

It's showing a number because that's what is in the table. This is not a bug
or an error; it's perhaps a warning that you should not be using table or
query datasheets for viewing or editing data!

You can use a Query joining your current table to the combo box's row source
table (this would be typical if you're creating a Report, just include all the
needed lookup tables in the Report's recordsource query). To display the data
on a Form, use a query as with the report, or another combo box, or (rarely,
because of the performance penalty) a DLookUp function call.
--

John W. Vinson [MVP]

Oh. I see, now...I think. As long as I don't use the table with the combo
selections for my queries and reports its OK. What if my form was already
created with a combo box from a Lookup table, is this ok? Or would I need to
change my forms?

"John W. Vinson" wrote:

On Sun, 11 Oct 2009 17:38:01 -0700, ADonMS
wrote:

I have created a form with combo boxes to lookup data from other tables. The
data updates to another table that I am trying to use in a query. When I run
the query, it returns the numerical value instead of the text value. The Row
Source on the combo box is a select query for the text value and when I use
the drop down on the form, it displays the text value. How can I change the
format in the query to show the text value?

It's showing a number because that's what is in the table. This is not a bug
or an error; it's perhaps a warning that you should not be using table or
query datasheets for viewing or editing data!

You can use a Query joining your current table to the combo box's row source
table (this would be typical if you're creating a Report, just include all the
needed lookup tables in the Report's recordsource query). To display the data
on a Form, use a query as with the report, or another combo box, or (rarely,
because of the performance penalty) a DLookUp function call.
--

John W. Vinson [MVP]

On Sun, 11 Oct 2009 19:06:01 -0700, ADonMS
wrote:

Oh. I see, now...I think. As long as I don't use the table with the combo
selections for my queries and reports its OK. What if my form was already
created with a combo box from a Lookup table, is this ok? Or would I need to
change my forms?

It's perfectly appropriate, routine and ordinary to use Combo Boxes on a form,
to store a number value into a table, for display on a report using a query.

Don't confuse data STORAGE with data DISPLAY. They are different functions.
Your tables will very commonly contain several fields of numbers, where the
numbers are actually foreign keys to lookup tables elsewhere in the database.

Where Microsoft went wrong, in many of our eyes, was putting Lookup Fields in
Tables: see
http://www.mvps.org/access/lookupfields.htm
for a critique.
--

John W. Vinson [MVP]

Once again, you're awesome!! Thanks, much for the explanation and backup info.

"John W. Vinson" wrote:

On Sun, 11 Oct 2009 19:06:01 -0700, ADonMS
wrote:

Oh. I see, now...I think. As long as I don't use the table with the combo
selections for my queries and reports its OK. What if my form was already
created with a combo box from a Lookup table, is this ok? Or would I need to
change my forms?

It's perfectly appropriate, routine and ordinary to use Combo Boxes on a form,
to store a number value into a table, for display on a report using a query.

Don't confuse data STORAGE with data DISPLAY. They are different functions.
Your tables will very commonly contain several fields of numbers, where the
numbers are actually foreign keys to lookup tables elsewhere in the database.

Where Microsoft went wrong, in many of our eyes, was putting Lookup Fields in
Tables: see
http://www.mvps.org/access/lookupfields.htm
for a critique.
--

John W. Vinson [MVP]