Storing Data from LookUp Combo Box in Table as text a poor idea

[Leonidsg]

Quick design question. In my forms I have several combo boxes with lookup tables. When something is selected from the form it saves as the ID in the table which is a text field currently. Is is a bad idea to have it save as a the actual text instead of the ID. Also if I keep it as the ID and in the future make a change to the lookup table where a particular ID does not correlate the same text, will this change be reflected in the tables.



Thank you
Lenny

[Beetle]

When something is selected from the form it saves as the ID in the table which is a text field currently. Is is a bad idea to have it save as a the actual text instead of the ID.

This statement is a bit conflicting. Are you storing the numeric ID value (correct), or the text that is associated with the numeric ID value (incorrect)?

Also if I keep it as the ID and in the future make a change to the lookup table where a particular ID does not correlate the same text, will this change be reflected in the tables.

Yes, except that the last part of that statement should say the change will be reflected in the combo boxes, forms, reports or other objects that reference the value from the lookup table.

[Leonidsg]

This statement is a bit conflicting. Are you storing the numeric ID value (correct), or the text that is associated with the numeric ID value (incorrect)?

Currently it is storing the Number ID instead of the text, this seems like the more correct method?

Yes, except that the last part of that statement should say the change will be reflected in the combo boxes, forms, reports or other objects that reference the value from the lookup table.

Makes sense. Thank you

[June7]

Primary and foreign key fields must be the same datatype.

Whether you save the ID or text value depends on situation. If the table is simply:

ID	Condition
1	Poor
2	Fair
3	Good
4	Excellent

I suggest just saving the text Condition and eliminating the complication of having to join tables to retrieve the related value.

[Leonidsg]

That is mostly the case but some of the text can have up to 4 words. Like "Spica Cast wo stabilization". Does it become too much at that point. One big one I have is the Bones lookup table which is mainly two words: "L Femur" , "R Femur" etc.

Thank you

[June7]

Yes that does get long and if this was 30 years ago when memory came at a premium, would definitely use the ID value. Indexing on text can slow sorting but you will often want to sort on the text values instead of the numeric ID. Lookup alias in combobox can be aggravating in some situations as well as having to always include the lookup table in queries to retrieve the text alias. So decide which way works best for you.

[Leonidsg]

Hey June7. Thanks again for your help. Would you mind telling me how to change the text to be stored instead of the Id. Thank you.

[Leonidsg]

I think I figured it out, I changed the bound column. Does that sound correct? Thank you

[June7]

If you want to save the text, don't even need the ID field in the RowSource.

[Leonidsg]

If you want to save the text, don't even need the ID field in the RowSource.

Hey Juen7, so I finally got around to working on this part. My row source reads "SELECT [DEXALocationLkUp].[DEXALocation] FROM DEXALocationLkUp; " Everything looks good, when I click on my combo box in the form I can see my text choices but when I click on the choice it doesn't show it on the form or save it to the table.

Any suggestions.

Thank you
Lenny

Edit: figured it out, needed to change bound column to 1. Thank you

[June7]

Is the BoundColumn property set as 1? Is the ColumnCount set as 1? Is the ControlSource set to the correct field?

[Leonidsg]

Everything was correct except bound column. Once I changed that it worked great. Thanks again.