Combo Box saving a number instead of text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box bound to column 0 of a table - the ControlSource is a
field named WorKPerformed in my Work table and the RowSource is

SELECT WorkPerformed.[Job Performed] FROM WorkPerformed;

NOTE: This second WorkPerformed is referring to a table with Job Performed
being the field in this second table.

My problem is that on the form I see the job description fine in the combo
box (even if I move forward and backward through the records). However, when
I look at the data in the table (outside of the form) there is a number
instead of the job description I selected. The numbers appear to run from
0-9 and refer to the record number beginning with 0 instead of 1. Can you
give me an idea of why and what I can do to get the correct text saved in the
table?

Thanks in advance.
 
I have a combo box bound to column 0 of a table - the ControlSource is a
field named WorKPerformed in my Work table and the RowSource is

SELECT WorkPerformed.[Job Performed] FROM WorkPerformed;

NOTE: This second WorkPerformed is referring to a table with Job Performed
being the field in this second table.

My problem is that on the form I see the job description fine in the combo
box (even if I move forward and backward through the records). However, when
I look at the data in the table (outside of the form) there is a number
instead of the job description I selected. The numbers appear to run from
0-9 and refer to the record number beginning with 0 instead of 1. Can you
give me an idea of why and what I can do to get the correct text saved in the
table?

Thanks in advance.
Click to expand...

Let me guess... you used the Lookup Wizard to define a Lookup Field
for Job Performed in your table, right?

If so, the number *IS CORRECT*. That's how combos usually are used -
the human sees a meaningful text value, the table stores a compact
unique number, and both go home happier for the choice.

Table datasheets are designed for data STORAGE, not for viewing. I
simply disagree that this would be "the correct text" - I think it's
working just fine as is!

John W. Vinson[MVP]
 
Thank you John. Also, I didn't use the wizard - I configured it myself. Can
I make it put the text in somehow? My users browse the table and use it for
other things and I would like them to see the text. It was putting text
earlier if I'm not mistaken. My other combobox is showing the text. I'm
also appending a new job description if the user doesn't find it and it works
fine.

Thanks.

John Vinson said:
I have a combo box bound to column 0 of a table - the ControlSource is a
field named WorKPerformed in my Work table and the RowSource is

SELECT WorkPerformed.[Job Performed] FROM WorkPerformed;

NOTE: This second WorkPerformed is referring to a table with Job Performed
being the field in this second table.

My problem is that on the form I see the job description fine in the combo
box (even if I move forward and backward through the records). However, when
I look at the data in the table (outside of the form) there is a number
instead of the job description I selected. The numbers appear to run from
0-9 and refer to the record number beginning with 0 instead of 1. Can you
give me an idea of why and what I can do to get the correct text saved in the
table?

Thanks in advance.
Click to expand...

Let me guess... you used the Lookup Wizard to define a Lookup Field
for Job Performed in your table, right?

If so, the number *IS CORRECT*. That's how combos usually are used -
the human sees a meaningful text value, the table stores a compact
unique number, and both go home happier for the choice.

Table datasheets are designed for data STORAGE, not for viewing. I
simply disagree that this would be "the correct text" - I think it's
working just fine as is!

John W. Vinson[MVP]
Click to expand...
 
Bonnie said:
Thank you John. Also, I didn't use the wizard - I configured it
myself. Can I make it put the text in somehow? My users browse the
table and use it for other things and I would like them to see the
text. It was putting text earlier if I'm not mistaken. My other
combobox is showing the text. I'm also appending a new job
description if the user doesn't find it and it works fine.
Click to expand...

When using a ComboBox to provide input choices from a lookup table it makes
sense to save the text value if that is the primary key of the lookup table.
For example if I was using a lookup table to provide choices for entry in a
state field that lookup table would consist of one field containing AZ, AR, CO,
etc..

In that case I am displaying the state code and I am storing the state code.
However; if my lookup table was a Customers table I would likely want to display
the Customer Name while storing a Customer Account Number (since names might not
even be unique in that case).

It sounds like you have a lookup table with a numeric field as the primary key,
yet you want to store the text field in the other record. Not knowing the type
of data I can't say that storing the text is a bad idea, but if storing the text
is okay then I can argue that the lookup table should not have a numeric primary
key field. If it *needs* a numeric PK, then that is what you should be saving
in your related tables. If it doesn't then store the text but eliminate the
numeric field from the lookup table and make the text field the PK. The PK is
always the field that should be stored in related tables.
 
Hi Rick, thanks for responding. The table that I am looking up the job
description from only has one field and it is text. It is just a unique job
description and I am using it so we have consistency of reporting and don't
show up with all kinds of fluky job descriptions. I'm selecting the text
"job descrition" and ending up with a number in the table. Driving me crazy.
 
Bonnie said:
Hi Rick, thanks for responding. The table that I am looking up the
job description from only has one field and it is text. It is just a
unique job description and I am using it so we have consistency of
reporting and don't show up with all kinds of fluky job descriptions.
I'm selecting the text "job descrition" and ending up with a number
in the table. Driving me crazy.
Click to expand...

Don't see how that is possible if the lookup table doesn't contain a numeric
field. As suggested earlier are you sure the field in the lookup table is
not defined as a lookup field? That causes you to see one thing in the
table when it actually contains something else.

When you look at the lookup table directly do you see a normal field or a
field with a drop-down arrow? You have to put your cursor in the field to
see the difference.
 
Hi Rick,

No I'm sure it's not. I went into the table and selected the field and it
did not drop down. Also, I went into the table structure and it is just a
plain text field. Same thing in the record I am adding the data to from the
combo box to. It is plain text. I'll recap again:

The form has table - "TimeWorked" as it's control source. In the combobox I
am have SELECT WorkPerformed.[Job Performed] FROM WorkPerformed; from the
second table that I am looking up the job description from. When I select an
appropriate job description it looks right in the form but saves a number to
the table "TimeWorked." Neither of these tables has a drop down arrow when I
look at them directly. However, there is a drop down arrow in the form on
the combobox of course. I need the text saved to the file "TimeWorked" and
not a number. It works fine on my other combobox on the form. If I can get
it to change - maybe a work around?

Any help is appreciated.
 
In your work performed table, is there a unique record key in one of the
fields (probably the first column)?

Look into the combo box properties, and check the Column Count ... is it 2?
Now check the Column Widths --- does it start off with a semicolon or with a
0"; ??? If so, then you actually have a two-column table with only the
second column showing. The first column represents the row's key value.
This is really the best way to build normalized relational databases and
avoid replicated data (unless you want to live with replicated data that may
get out of whack).

I saw your original question and the response (and the reponse to the
response) and figured I'd jump in at this point....


Bob (@Martureo.Org)

Bonnie said:
Hi Rick,

No I'm sure it's not. I went into the table and selected the field and it
did not drop down. Also, I went into the table structure and it is just a
plain text field. Same thing in the record I am adding the data to from the
combo box to. It is plain text. I'll recap again:

The form has table - "TimeWorked" as it's control source. In the combobox I
am have SELECT WorkPerformed.[Job Performed] FROM WorkPerformed; from the
second table that I am looking up the job description from. When I select an
appropriate job description it looks right in the form but saves a number to
the table "TimeWorked." Neither of these tables has a drop down arrow when I
look at them directly. However, there is a drop down arrow in the form on
the combobox of course. I need the text saved to the file "TimeWorked" and
not a number. It works fine on my other combobox on the form. If I can get
it to change - maybe a work around?

Any help is appreciated.


Rick Brandt said:
Don't see how that is possible if the lookup table doesn't contain a numeric
field. As suggested earlier are you sure the field in the lookup table is
not defined as a lookup field? That causes you to see one thing in the
table when it actually contains something else.

When you look at the lookup table directly do you see a normal field or a
field with a drop-down arrow? You have to put your cursor in the field to
see the difference.
Click to expand...
Click to expand...
 
You da man! That was it. I change my bound column to number 1 and now the
actual text is being saved. Can't for the life of me find the first column
in the table structure. Must be one of the behind the scenes things Access
does to us. I've worked with dBase for years but I'm trying to get with the
program.

Thanks very much ya'll

Bob Howard said:
In your work performed table, is there a unique record key in one of the
fields (probably the first column)?

Look into the combo box properties, and check the Column Count ... is it 2?
Now check the Column Widths --- does it start off with a semicolon or with a
0"; ??? If so, then you actually have a two-column table with only the
second column showing. The first column represents the row's key value.
This is really the best way to build normalized relational databases and
avoid replicated data (unless you want to live with replicated data that may
get out of whack).

I saw your original question and the response (and the reponse to the
response) and figured I'd jump in at this point....


Bob (@Martureo.Org)

Bonnie said:
Hi Rick,

No I'm sure it's not. I went into the table and selected the field and it
did not drop down. Also, I went into the table structure and it is just a
plain text field. Same thing in the record I am adding the data to from the
combo box to. It is plain text. I'll recap again:

The form has table - "TimeWorked" as it's control source. In the combobox I
am have SELECT WorkPerformed.[Job Performed] FROM WorkPerformed; from the
second table that I am looking up the job description from. When I select an
appropriate job description it looks right in the form but saves a number to
the table "TimeWorked." Neither of these tables has a drop down arrow when I
look at them directly. However, there is a drop down arrow in the form on
the combobox of course. I need the text saved to the file "TimeWorked" and
not a number. It works fine on my other combobox on the form. If I can get
it to change - maybe a work around?

Any help is appreciated.


Rick Brandt said:
Bonnie wrote:
Hi Rick, thanks for responding. The table that I am looking up the
job description from only has one field and it is text. It is just a
unique job description and I am using it so we have consistency of
reporting and don't show up with all kinds of fluky job descriptions.
I'm selecting the text "job descrition" and ending up with a number
in the table. Driving me crazy.

Don't see how that is possible if the lookup table doesn't contain a numeric
field. As suggested earlier are you sure the field in the lookup table is
not defined as a lookup field? That causes you to see one thing in the
table when it actually contains something else.

When you look at the lookup table directly do you see a normal field or a
field with a drop-down arrow? You have to put your cursor in the field to
see the difference.
Click to expand...
Click to expand...
Click to expand...
 
I have a combo box bound to column 0 of a table - the ControlSource is a
field named WorKPerformed in my Work table and the RowSource is
Click to expand...

Ummm... what's "Column 0"??
SELECT WorkPerformed.[Job Performed] FROM WorkPerformed;

NOTE: This second WorkPerformed is referring to a table with Job Performed
being the field in this second table.
Click to expand...

So there is a Table names WorkPerformed which contains a Text field
[Job Performed}? Try copying and pasting this exact SQL statement into
the SQL window of a new query: when you open the query, does it show
text, or a number?
My problem is that on the form I see the job description fine in the combo
box (even if I move forward and backward through the records). However, when
I look at the data in the table (outside of the form) there is a number
instead of the job description I selected. The numbers appear to run from
0-9 and refer to the record number beginning with 0 instead of 1. Can you
give me an idea of why and what I can do to get the correct text saved in the
table?
Click to expand...

Since tables don't have record numbers, I can only assume that your
WorkPerformed table actually has TWO fields, an autonumber ID and the
text - but that disagrees with the RowSource you posted. Something's
not adding up here!

Please doublecheck the table and the RowSource of the combo. I think
you've somehow gotten the wrong field as the Bound Column of the
combo.

John W. Vinson[MVP]
 
I got it running by changing the column count which was 2. Did you read what
Bob Howard said about it. It's a bit funny to me cause I looked at every
thing I could for both of the fields and nothing showed two columns.

Thanks for all your help.

John Vinson said:
I have a combo box bound to column 0 of a table - the ControlSource is a
field named WorKPerformed in my Work table and the RowSource is
Click to expand...

Ummm... what's "Column 0"??
SELECT WorkPerformed.[Job Performed] FROM WorkPerformed;

NOTE: This second WorkPerformed is referring to a table with Job Performed
being the field in this second table.
Click to expand...

So there is a Table names WorkPerformed which contains a Text field
[Job Performed}? Try copying and pasting this exact SQL statement into
the SQL window of a new query: when you open the query, does it show
text, or a number?
My problem is that on the form I see the job description fine in the combo
box (even if I move forward and backward through the records). However, when
I look at the data in the table (outside of the form) there is a number
instead of the job description I selected. The numbers appear to run from
0-9 and refer to the record number beginning with 0 instead of 1. Can you
give me an idea of why and what I can do to get the correct text saved in the
table?
Click to expand...

Since tables don't have record numbers, I can only assume that your
WorkPerformed table actually has TWO fields, an autonumber ID and the
text - but that disagrees with the RowSource you posted. Something's
not adding up here!

Please doublecheck the table and the RowSource of the combo. I think
you've somehow gotten the wrong field as the Bound Column of the
combo.

John W. Vinson[MVP]
Click to expand...
 
I got it running by changing the column count which was 2. Did you read what
Bob Howard said about it. It's a bit funny to me cause I looked at every
thing I could for both of the fields and nothing showed two columns.
Click to expand...

My guess is that at some point you had the table open in datasheet
view and shrank the width of the numeric ID column down to zero (or so
small that you can't see it).

John W. Vinson[MVP]
 
Nope, I would have seen that in the design view of the table structure. Was
just not there.

Thanks.
 
Back
Top