Form showing ID value, not text for field

[Will]

Hello all,

In a database I'm working on, there is table called "Contract Type".
The details are as follows:

tbl_ContractType
------------------------
ContactTypeID (key)
Contract Type (text) <-- a unique type of contact, i.e. "2 weeks full
color", "6 months email"
Print or Internet (lookup from tbl_PrintInternet)

The Print or Internet table referrened there looks like this:

tbl_PrintInternet
-------------------------
PrintInternetID (key)
Print or Internet (text)

So each contact type in tbl_ContractType has to either be a Print
contract or an Internet contract (these are advertising contracts).

When creating a form based on the tbl_ContractType table, the Print or
Internet field displays the numerical value (PrintInternetID) and not
the text value (Print or Internet).

This is just a single very simple example of where I'm having this
issue, I have other tables that have many more referrences to other
tables, they all behave the same in their associated forms.

What is the best way to have the forms, which will be used for data
review and data entry, show the text values instead of the associated
key values?

Thanks in advance.

 

[BruceM]

Are the problem fields lookup fields? That would explain it, I think. For
more information see here:
http://www.mvps.org/access/lookupfields.htm
To check whether they are lookup fields, go to table design view and look at
the Lookup tab for the field (bottom left of the window). If it is a combo
box there will be other information about Row Source, Column Count, etc.
Also, in datasheet view you will see a dropdown arrow when you click into
the field.

Back up the database!

Once more, just to be safe.

Now you can covert the Lookup (table design view) to a text box.

I don't use lookup fields, but as I recall there is a wizard. I would guess
that is how it got set up. If there are just a few records you can just
change them manually. If there are many records you can use an update
query, substituting the text for the number. Be sure it is a text field.

Once that is fixed, if there are just two options (Print or Internet) and
you do not anticipate more choices you could use a value list for the combo
box row source. If the list could grow (or if you would rather use a lookup
table) tblPrintInternet can be used as a lookup *table* (very different
from a lookup field). The table, or a query based on the table, is the row
source for the combo box on the ContractType form bound to ContractType in
the underlying table.

Remember, BACK UP before you touch anything!!

 

[Will]

OK, I think I know what needs to be done. As a follow-up question.
Say the data entry is to take place in the table directly
occasionally. Since the contract type is now a text field in the
contacts table, how could I enforce the correct data entry in
datasheet view? It doesn't seem there is a way.

Thanks.

 

[John W. Vinson]

Say the data entry is to take place in the table directly
occasionally.

Well, DON'T say that.

Data entry should be done through Forms. That's the only way you can ensure
that you have control over it.

 

[BruceM]

You could use table-level validation, available in table design view, to
restrict the entries and provide a validataion message, but you should not
be entering data directly into tables except during development. You can
use the datasheet view of a form to make it resemble a table.

OK, I think I know what needs to be done. As a follow-up question.
Say the data entry is to take place in the table directly
occasionally. Since the contract type is now a text field in the
contacts table, how could I enforce the correct data entry in
datasheet view? It doesn't seem there is a way.

Thanks.

 

[Will]

Bruce and John, thanks for your assistance on this. I'll have to
think about how we can best approach the data entry issue. This
database will be getting massive inputs (hundreds to thousands of
rows) of data one a month that are most easily entered by copying/
pasting rows from Excel. I'll examine the datasheet view on the forms
and see what I come up with.

Thanks again!

 

[John W. Vinson]

Bruce and John, thanks for your assistance on this. I'll have to
think about how we can best approach the data entry issue. This
database will be getting massive inputs (hundreds to thousands of
rows) of data one a month that are most easily entered by copying/
pasting rows from Excel. I'll examine the datasheet view on the forms
and see what I come up with.

Even better than a copy and paste is File... Get External Data... Link to the
spreadsheet, and an Append query. This can even be automated so that it can be
done at the click of a mouse.