Bound column number being saved to table

[KerryL]

I can't figure out why a few of my combo boxes are saving the bound column number to the table instead of the data (from the selection table).

For example, on the data entry form users can add up to 3 Layoff Reasons using separate combo boxes whose selections come from tblLayoffReason. A selection in the first combo box works fine; the selection is added to the table. But anything selected in the 2nd & 3rd combo boxes cause the bound column number to be saved to the table instead of the selection data.

These are the 3 combo boxes on frmAddData:
cboLayfReas1
cboLayfReas2
cboLayfReas3

Properties of each cbo:
Column Count = 2
Column Heads = No
Col Width = 0";2"
Bound Column = 1


tblLayoffReason contains the selections available for the combo boxes:

ID Reason
1 Not Reported
2 Company Sold
3 Bankruptcy
4 Financial Trouble
5 Operations Transferred


Iif the user selects "Company Sold" for cboLayfReas1, "Company Sold" ends up in the table. If the user then selects "Bankruptcy" for cboLayfReas2, what is placed in the table is the number "3." Consequently, when users go into edit that record later it shows "3", and all reports show a "3" for Layoff Reason 2 as well. Obviously, I need the actual reason (Bankruptcy) to be displayed.

If the combo box properties are identical, why is the bound column number being saved to the table instead of the text from column 2 of the selection table?

 

[Eupher]

Kerry,

You don't perchance have any lookup fields defined in your table, do you?

Ken S.

 

[KerryL]

PHV said:
Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Thanks, that helped me understand inner & outer joins. I'll have to do some SQLing to get comfortable with it.

 

[KerryL]

Tarnish said:
The "mini query" solves this problem:
Often, what is best to STORE in a table is NOT the best thing for the user to SEE as an option from which to choose.
<snip>
"Mini query" steps in and creates a "mini record" which includes what the table wants to store (ID#) and what the user wants to see (Name) and makes it all fit inside the combo box. Then, with a couple of setting adjustments, the user picks the "mini record" based on the "Name" that's displayed (and that's all he sees), but "mini query" passes on the related "ID" to the table so table is happy too!

Does that make any sense or did I get too carried away?

It makes perfect sense, thank you. Now I just have to get it to work in reverse so I can translate the ID# from the table into its matching description for reports, etc.

 

[KerryL]

Eupher said:
You don't perchance have any lookup fields defined in your table, do you?

In the main data table? No. I only have lookup data in a few lookup tables.

 

[KerryL]

Okay, so if the correct way of saving info from a lookup table cbo is to use Column-1 (the key/ID) as the bound column, then the table field datatype should be a number, right?

Then when I need to display the lookup table item description (for queries or reports) I create a query that links the ID# saved in the main table to the lookup table key field (also the ID#)...

Man, seems like saving the description in the table and filtering it would require less overhead.

 

[Remou]

You may make that decision for some list that are very unlikely to change, but it is remarkable how often a number, description list turns out to be more useful, and easier, in the end.

 

[TheAceMan1]

How are ya KerryL . . .

I went thru this thread as was spellbound!

Havwe a look here The Evils of Lookup Fields in Tables

See Ya! . . . . . .

 

[KerryL]

Aceman,
Spellbound... LOL, yeah right. ;-)

The reference you supplied regarding lookup tables is pretty critical of them. However, it doesn't offer a viable alternative.

If lookup tables are bad, what should be used instead to offer users a selection choice?

 

[Eupher]

KerryL,

Lookup table ? lookup field. There is nothing wrong with lookup tables! Lookup fields in a table can be a big problem! Precisely because it can get very confusing as to what is actually being stored in the table vs. what is displayed. Much better to store the primary key and do the lookups in the combo or listbox on the form. Not saying this is the problem you're having - but it sounds like the symptoms!

Ken S.

 

[Remou]

KerryL
I think there is a misunderstanding. The article is critical of look-up fields, a completely different animal to look-up tables, which I think you will find are considered useful. As you have already said in your reply to Eupher (15 Feb 07 9:29 ), you have not committed look-up fields.

 

[KerryL]

Oops, I misunderstood. I apologize for my confusion.

I am currently in the process of:
1. Consolidating my lookup tables into 1 table.
2. Adding a WHERE clause to the Control Source of each combo box that pulls data from the lookup table.

BTW, does it matter if my main table stores the Lookup Table ID (key field) in a text field, or should they be a numeric field?


Hey Eupher, are you a UM fan or is your handle just a coincidence?

 

[TheAceMan1]

KerryL . . .

Since your having this type of problem, my post was meant as a tickler for whenever your deciding to use lookup combo's in tables . . .

See Ya! . . . . . .

 

[Eupher]

KerryL,

No, my handle is not related to UM at all. A eupher is one who plays the euphonium.

Ken S.

 

[Remou]

KerryL
You would be better to store numbers in a numeric field, otherwise you will run into problems relating the fields.

 

[PHV]

does it matter if my main table stores the Lookup Table ID (key field) in a text field, or should they be a numeric field
The rule is: the ForeignKey should be the SAME data type as the referenced PrimaryKey.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886