Populating Combo Boxes From Query's

[jutlaux]

I have a combo box, on a from, that is getting populated by a Query. The
problem I have is when you look at the table for the form, the data from the
combo box is not the text from the combo box but a number. This number
corresponds to the location in the combo box that the text is in.

i.e. if my combo box has the following data:
"text1"
"text2"
"text3"
"text4"

and I select "text2" from the combo box, when I go to the table data
for the combo box it will have a value of 2 in it not the text "text2". Is
there a way to populate a combo box from a query and still have the table
store the text from the combo box, not the location?

 

[Klatuu]

This is not normal behaviour. A combo returns the value of the bound column
when you reference it as Me.MyCombo
Also, you post is a bit unclear. You say the combo's row source is a query.
Is it returning the text values "text1", "text2", etc.?

Is the combo a bound control?

Can you provide more detail?

 

[jutlaux]

Sorry for the confusion hope this helps.

Correct.

From the combo box properties:
Row Source Type = Table/Query
Row Source = SELECT Table_ProjectType.ID, Table_ProjectType.Project_Type
FROM Table_ProjectType ORDER BY [Project_Type];

When you place a combo box a wizard pops up asking where you want the combo
box to get it's data from. I selected "I want the combo box to lookup the
values in a table or query" at the end of the wizard it asks what you want
Access to do with the value. I select "Store that value in this field" and
lets call this field "Project_Type"

When you are on the Form View the combo box has the correct data in it from
the query i.e the "text1", "text2", "text3", "text4". Now if I select
"text2" from the combo box my thought would be that the data "text2" gets
stored into field "Project_Type". If you are on the Form the combo box will
show "text2". The problem arises when you open the Table for the Form that
has the combo box in it. If you go to field "Project_Type" in the table in
has a number 2 in it not the data "text2" that the From is showing.

 

[Klatuu]

The problem is you have a multi column combo. The bound column is probably
column 1 and the ID is the numeric value from that field. Project_Type is
probably the text field you are seeing. When you query a combo like
Me.MyCombo, it returns the Bound column.
In a combo, the Control Source (the field in the form's record source the
control is bound to) is always bound to the bound column.
The wizard is not that smart.
If you want it bound to the project_type field, you will need to make the
bound column column 2. Or better still, if you don't really need the ID in
the combo, get rid of it and let it be a one column combo based on just the
project type field.

Sorry for the confusion hope this helps.

Correct.

From the combo box properties:
Row Source Type = Table/Query
Row Source = SELECT Table_ProjectType.ID, Table_ProjectType.Project_Type
FROM Table_ProjectType ORDER BY [Project_Type];

When you place a combo box a wizard pops up asking where you want the combo
box to get it's data from. I selected "I want the combo box to lookup the
values in a table or query" at the end of the wizard it asks what you want
Access to do with the value. I select "Store that value in this field" and
lets call this field "Project_Type"

When you are on the Form View the combo box has the correct data in it from
the query i.e the "text1", "text2", "text3", "text4". Now if I select
"text2" from the combo box my thought would be that the data "text2" gets
stored into field "Project_Type". If you are on the Form the combo box will
show "text2". The problem arises when you open the Table for the Form that
has the combo box in it. If you go to field "Project_Type" in the table in
has a number 2 in it not the data "text2" that the From is showing.

This is not normal behaviour. A combo returns the value of the bound column
when you reference it as Me.MyCombo
Also, you post is a bit unclear. You say the combo's row source is a query.
Is it returning the text values "text1", "text2", etc.?

Is the combo a bound control?

Can you provide more detail?

 

[John Vinson]

When you are on the Form View the combo box has the correct data in it from
the query i.e the "text1", "text2", "text3", "text4". Now if I select
"text2" from the combo box my thought would be that the data "text2" gets
stored into field "Project_Type". If you are on the Form the combo box will
show "text2". The problem arises when you open the Table for the Form that
has the combo box in it. If you go to field "Project_Type" in the table in
has a number 2 in it not the data "text2" that the From is showing.

Just to add to Klatuu's good advice: normally, you WANT to store a
numeric ID rather than the text value. Access' wizards are set up on
that assumption (the assumption can be overridden); since one would
ordinarily not have the Table open for viewing or printing, this is
just fine. It lets you store an economical 4-byte Long in the table as
a link to an arbitrary length text string, and lets you (e.g.) change
the spelling of the Location in the Location table without having to
track down every record containing that value.

Typically a Combo Box has a bound column, a numeric foreign key, with
zero width so it's not displayed; and a visible text column. This lets
the computer see the unique, unambiguous, compact ID, while the user
sees the text value; both end up happier.

John W. Vinson[MVP]

 

[jutlaux]

This worked I changed the bound column to 2 and now my table is getting the
text not the ID #. Thanks for you help.

The problem is you have a multi column combo. The bound column is probably
column 1 and the ID is the numeric value from that field. Project_Type is
probably the text field you are seeing. When you query a combo like
Me.MyCombo, it returns the Bound column.
In a combo, the Control Source (the field in the form's record source the
control is bound to) is always bound to the bound column.
The wizard is not that smart.
If you want it bound to the project_type field, you will need to make the
bound column column 2. Or better still, if you don't really need the ID in
the combo, get rid of it and let it be a one column combo based on just the
project type field.

Sorry for the confusion hope this helps.

Correct.

From the combo box properties:
Row Source Type = Table/Query
Row Source = SELECT Table_ProjectType.ID, Table_ProjectType.Project_Type
FROM Table_ProjectType ORDER BY [Project_Type];

When you place a combo box a wizard pops up asking where you want the combo
box to get it's data from. I selected "I want the combo box to lookup the
values in a table or query" at the end of the wizard it asks what you want
Access to do with the value. I select "Store that value in this field" and
lets call this field "Project_Type"

When you are on the Form View the combo box has the correct data in it from
the query i.e the "text1", "text2", "text3", "text4". Now if I select
"text2" from the combo box my thought would be that the data "text2" gets
stored into field "Project_Type". If you are on the Form the combo box will
show "text2". The problem arises when you open the Table for the Form that
has the combo box in it. If you go to field "Project_Type" in the table in
has a number 2 in it not the data "text2" that the From is showing.

This is not normal behaviour. A combo returns the value of the bound column
when you reference it as Me.MyCombo
Also, you post is a bit unclear. You say the combo's row source is a query.
Is it returning the text values "text1", "text2", etc.?

Is the combo a bound control?

Can you provide more detail?

:

I have a combo box, on a from, that is getting populated by a Query. The
problem I have is when you look at the table for the form, the data from the
combo box is not the text from the combo box but a number. This number
corresponds to the location in the combo box that the text is in.

i.e. if my combo box has the following data:
"text1"
"text2"
"text3"
"text4"

and I select "text2" from the combo box, when I go to the table data
for the combo box it will have a value of 2 in it not the text "text2". Is
there a way to populate a combo box from a query and still have the table
store the text from the combo box, not the location?