Numbers appearing instead of text in Table

[Maria]

Please excuse my lack of experience – I am just starting to program in
Access. I am using Access 2003 – not sure if this is important to know.

I have a DB with 3 forms y 4 tables.

The form “fmProductos†contains Combo Boxes to display data for the user.
The Combo Boxes show up and the data within shows as well.

When the user selects a data value and saves the form, what actually appears
in the "tblProductos" is a Number and NOT the Text.

Form used: fmProducts
Combo Boxes – Marca, MaqTipo, Modelo, Combustible
Values of ComboBoxes taken from tblValorParaCampos
Table to store record: tblProductos

Data for the Combo Boxes uses the “tblValorParaCampos†table and each
Combo box is bound using their respectful “Row Source†properties.

Again, this method works – there is no issue with the Combo Boxes being
populated with data – it works properly.

However, as stated above - the value that is then stored in the
"tblProductos" is a number and NOT the actual text.

What am I missing? How do I get the actual text to be listed in the table
as opposed to the number that represents the selection of the user.

Thank you in advance
Maria

 

[Ken Snell [MVP]]

I'm guessing that your combobox is based on a field that is a "lookup" field
in the table. What this means is that the combo box displays the "text"
(description) of the data item, but is actually storing the "ID" value (a
number, perhaps from an autonumber field) of the data item. This is how a
relational database should work, so what you're seeing is the correct
behavior.

You use a query to display the "description" text based on the "number"
value. In fact, if you look at the combo box's RowSource property, you'll
see an SQL statement that lists the "id" (number) field first, followed by
the "description" (text) field. And the BoundColumn property of the combo
box is 1. And the ColumnWidths of the combo box are 0";1".

 

[Mike Painter]

Please excuse my lack of experience - I am just starting to program in
Access. I am using Access 2003 - not sure if this is important to
know.

I have a DB with 3 forms y 4 tables.

The form "fmProductos" contains Combo Boxes to display data for the
user. The Combo Boxes show up and the data within shows as well.

When the user selects a data value and saves the form, what actually
appears in the "tblProductos" is a Number and NOT the Text.

Form used: fmProducts
Combo Boxes - Marca, MaqTipo, Modelo, Combustible
Values of ComboBoxes taken from tblValorParaCampos
Table to store record: tblProductos

Data for the Combo Boxes uses the "tblValorParaCampos" table and each
Combo box is bound using their respectful "Row Source" properties.

Again, this method works - there is no issue with the Combo Boxes
being populated with data - it works properly.

However, as stated above - the value that is then stored in the
"tblProductos" is a number and NOT the actual text.

What am I missing? How do I get the actual text to be listed in the
table as opposed to the number that represents the selection of the
user.

Thank you in advance
Maria

It sounds like this is a properly designed set of tables.

The key to the table tblValorParaCampos is stored in tblProductos as it
should be and the actual text would be displayed by a query joining the two.

This is the way it should be.
In this case it might not make a difference but if you were dealing, say,
with names and addresses this is the only safe way to do it.

 

[Maria]

Thanks Ken

I understand what you indicated but to actually display the "description" is
where I am having issues -- I am still only displaying numbers.

Sorry - this is quite frustrating and the books that I have do not really
indicate how to display.

thx again for helping me
Maria

 

[Ken Snell [MVP]]

You want to store the number in the table, not the description, that you're
describing. The number is the important data that links the data from one
table to the data in another table. I think you're wanting the "lookup
field" option in that other table so that you see a description instead of
the number when you view the data directly in the table? You do not want to
do this.

However, if you're talking about how you display the result on a form, then
you can use a query as the form's Record Source where the query includes the
description field from another table, using the number to link the two
tables. Then you can bind a text box to this description field and show it
on the form.

But, to help you further, I need to know more details about the situation
where you want to display the description -- table? form? and tell the
fields in the tables (names), table names, how the tables are linked
(through which fields), etc.

 

[Maria]

Couple of statements/questions that I hope you can answer:
1. Ok so what you are saying is that the number values that are stored in
the table once the record is saved is the correct form of storing the lookup
values? I looked at the values that were stored and compared them to my
Value List and all the numbers are correctly saved - it would be nice to see
the description display in the table. I did a workaround by allowing a
lookup on the same field (in the stored table) and I can see the correct
description values - is this going to be a problem? Should I put the combos
in the table back to text fields and all that displays in the table are the
number values?

2. Yes it is the Form where the text fields are displaying the number value
(again the correct value) - but I would like it to display the actual
"description" text of the value. This actually relates to my question on
June 20 that you already answered on how to display text in 2 text fields
depending on data selected from a combo box. I used your instruction for the
Unbound Textbox and both text fields are displaying the correct number value
- but - again I would like the "description" to display in those fields.

Here is the description of the DB and how it functions:

3 Forms: (I changed the form names since 6/20)
- f_Productos – used to enter new products (machinery), contains 4 bound
combos that refer to t_ValorParaCampos (tx_MaqTipo, tx_Marca, tx_Modelo,
tx_Combustible), these work properly on the form but only stores the value in
the table. I know, I know I need to change the field name to reflect that
they are really combos. As it works now - I will do so later
- f_Empresas – used to enter Clients, contains 1 bound combo that refers to
t_ValorParaCampos (cbo_Provincia), this works properly but again only number
value stored in table
- f_Alquiler – used to create new requests for renting machinery), contains
1 bound combo that refers to t_Productos (cbo_RefMaq) and 1 bound combo that
refers to t_Empresas (cbo_NomEmpresa) and 1 bound combo that refers to
t_ValorParaCampos (cbo_Pagado), please see large paragraphs below for issues.

4 Tables: (I changed the form names since 6/20)
- t_Productos – listing of all products that are for rent
- t_Empresas – listing of clients
- t_Alquiler – listing of Requests for renting of machinery
- t_ValorParaCampos – table that lists all data for combo boxes

The form having the display of “description†text issues is the f_Alquiler.
Again the number value lists correctly in the text boxes (tx_Marca and
tx_Modelo) as well as what is stored in the t_Alquiler table.

cbo_RefMaq’s RowSource: SELECT t_Productos.RefMaq, t_Productos.tx_Marca,
t_Productos.tx_Modelo FROM t_Productos ORDER BY t_Productos.RefMaq,
t_Productos.tx_Marca, t_Productos.tx_Modelo;
No Columns – 2
Bound Column – 1
Column width – 2.54cm; 0 cm (do not worry if you indicate in inches - I
understand the conversion to cm)

tx_Marca Row Source: =cbo_RefMaq.Column(1)
tx_Modelo Row Source: =cbo_RefMaq.Column(2)


If there is anything you need from me – please indicate (I hope to
understand everything so that I too may help others having the same issue
with combo boxes)

Thx again
Maria

 

[Ken Snell [MVP]]

Comments inline...

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Couple of statements/questions that I hope you can answer:
1. Ok so what you are saying is that the number values that are stored in
the table once the record is saved is the correct form of storing the
lookup
values? I looked at the values that were stored and compared them to my
Value List and all the numbers are correctly saved - it would be nice to
see
the description display in the table. I did a workaround by allowing a
lookup on the same field (in the stored table) and I can see the correct
description values - is this going to be a problem? Should I put the
combos
in the table back to text fields and all that displays in the table are
the
number values?

Most MVPs recommend against using lookup fields in tables.
http://www.mvps.org/access/lookupfields.htm

After you read the above info, you'll recognize your situation as one of the
problems. However, if you understand how lookup fields work and know how to
avoid the problems that you've seen when working with them on a form, then
certainly you can use lookup fields in the tables.

2. Yes it is the Form where the text fields are displaying the number
value
(again the correct value) - but I would like it to display the actual
"description" text of the value. This actually relates to my question on
June 20 that you already answered on how to display text in 2 text fields
depending on data selected from a combo box. I used your instruction for
the
Unbound Textbox and both text fields are displaying the correct number
value
- but - again I would like the "description" to display in those fields.

On a form, you use a query (or a table) as the record source for displaying
data. If your form is showing data from a child table (e.g., order details
that have a part number in them), and you want to show the description of
the part number on the form, then you must use a query that joins the order
details table with the part number table, and then you include the
description field from the part number table in the query's output. This
allows you to bind a textbox on the form to the description field for
displaying its value. This means you can show the description of a part
number even when that description is not in the table of interest.

You do not want to store the description in the order details table because
that is storing redundant data. What if the part number description is
changed? Then you need to write code to go find all the instances of the
description in all the other tables and update the descriptions the same
way. Very quickly, your database will be a mess.

So I reiterate: you want to store the "ID" (number) of a data item in a
child table, not the "description". Use queries to show the related data for
that "ID" on a form or report.

 

[Maria]

Everything is working now. My issue was not the Combo box on the Alquiler
form - everthing was programmed properly. My issue was where the combo was
grabbing data to fill the 2 text fields. The data for the 2 text fields was
grabbing from a table where the data was not saved properly. Instead of
saving the text to the 2 fields, it was only saving a number that was
associated with the text because I did not have the correct Bound Column
number listed in those fields. Once I changed the Bound Column number --
everthing displayed and saved properly.

thx for all your help
Miara