Combo Box displays numbers in table instead of value chosen

[Guest]

I have a combo box in a form that shows the supplier name. After selecting
the supplier name, I saved the form. When I created a report, it shows on the
supplier name numbers, instead of the name of the supplier for each record. I
checked back the table, and it shows numbers instead of names under the
supplier column as well.

Please help!

 

[Wayne Morgan]

This is normal behavior. The combo box has more than one column. The Bound
Column (the one stored in the table) is usually hidden and the other column
(the one the user reads) is shown. These values usually come from a
"look-up" table that has an ID number for each record and additional columns
with text descriptions for the user. The columns in this table are what make
up the Row Source of the combo box, with the ID field being associated with
the Bound Column and the description columns being visible to the user.

To get the report to show what you want, use a query as the report's Control
Source. Include this look-up table in the query and link it to the main
table on the look-up table's ID field and the field in the main table that
the combo box is placing the numbers in. You can then add the text
description column from the look-up table to the output of the query for use
in the report.

 

[Guest]

Please bear with me as I am a newbie.

I am confused about the lookup table and which Id number you mentioned. This
is my current database situation:

- I have a few tables, Supplier, Banker, Purchaser, etc. Each table stores
their respective info such as Name, Address and Phone No.
- For each table, I created a form for user to enter the particulars, thus,
Supplier form, Banker form, etc.
- I created another table, Transaction_Records, which records the in and
outs of payments in accounts department. In this table, user can choose
whether payment is to Supplier, Purchaser, etc. I use Combo Box in the Form
to let user select the name of receipient.
- I created a report using the Transaction_Records table, and it shows
numeric values instead of the receipient name.

When I create a query, what should I put in it? Should I create any
relationships between the tables to link anything?

Thank you so much.

 

[Wayne Morgan]

Yes, relationships would help. For just the report, this can be done in the
query. Instead of basing the report on the Transaction_Records table, create
a query and include the Transactions_Records table and the Purchaser table.
It appears that the combo box is supplying the ID number of the record from
the Purchaser table to the Transaction_Records table. In the query, drag and
drop this ID field from one table to the same field in the other table. This
will create a link between them. Next, add the fields from the
Transaction_Record table that you are using in the report, skipping the
linked field. Instead of the linked field, add the Purchaser's Name field
from the Purchaser table to the query's output. This will give you the name
instead of the ID number. Reset the textbox in the report to use this name
field instead of the field it was using. Since the rest of the fields are
the same, once you set the Record Source of the report to the query, the
rest of the controls ought to be ok.

Just as a warning, don't use "reserved words" as field names. The most
common use of a reserved word as a field name is naming a field "Name".
Instead call the field PurchaserName, CustomerName, CompanyName, FirstName,
LastName, etc. While you can get by with using reserved words as field
names, if you aren't careful, it can cause you problems. Reserved words are
any word that Access recognizes as a built-in function, property, method,
collection, or constant. Another common example of a reserved word that gets
used as a field name is "date".

 

[Guest]

Hi.

I used relationships to link up the table and now the combo boxes displays
the correct name instead of ID. However, I still need some help:

In table Transactions, I have a combo box (call it cbx2) that displays names
based on the selection in another combo box (cbx1). Eg. Supplier in cbx1
shows Supplier name in cbx2, and Purchaser in cbx1 shows Purchaser Name in
cbx2. My question is:

1. How do I link up the Relationships for Tables Transaction, Supplier and
Purchaser?
2. How to use the code Select DISTINCTROW table.columnname... etc.. (which
is what I did for other combo box that selects from only one table)? I tried
to select all tables related to cbx1, but it shows number value in the table
instead of the name.

Pls advise again, thanks a lot.

 

[Wayne Morgan]

In table Transactions, I have a combo box (call it cbx2) that displays

names
based on the selection in another combo box (cbx1). Eg. Supplier in cbx1
shows Supplier name in cbx2, and Purchaser in cbx1 shows Purchaser Name in
cbx2.

I don't follow here. What is the difference between Supplier/Supplier Name
and Purchaser/Purchaser Name?

I'm guessing that you have 3 tables involved here. The 1) Transactions, 2)
Suppliers, and 3) Purchasers. Is this correct? If so, what fields in the
Transaction table relate to fields in the other two tables?

 

[Guest]

cbx1 contains Value List. Eg. when Supplier is selected, then a code will
run, which will insert the correct Row Source in cbx2, so that only Supplier
names are shown in cbx2. When Purchaser is selected in cbx1, then only
Purchaser names are shown in cbx2.

Yes, there is 3 tables. Purchaser and Supplier table contains their contact
details. Transaction table records payments and income from both of them. In
Transaction table, there is Purchaser ID and Supplier ID. I didn't include
them in the form as I am just using the ID to create a relationship between
all 3 tables. The selected name in cbx2 will be stored in the CompanyName
field in the Transaction table.

 

[Wayne Morgan]

You should be able to used the links on the tables to get the name, you
shouldn't need to store it in the Transactions table. In the query that
feeds the transactions form, you would include the table that has the
Company Name/Purchaser ID fields. Since the Purchaser ID is being stored in
the Transactions table, the link between this field and the Purchaser ID in
the other table would allow you to select the Company Name (Purchaser) from
their and not have to store the name as well.

Going back to #2 in your previous message, as you can see, this will let you
continue to have the number value in the table instead of the name. You can
then use this to retrieve the name when you need it.