Saving to table from Combobox selection

[ryan.fitzpatrick3]

Hi Everybody,

I have a combobox named (cboxItemMaster) that pulls from a table that
I have.I have several of these combobox's on a form. Combobox 2 pulls
filtered data from 1, then 3 from 2, then 4 from 3 respectively. This
works fine. Now once all the selections are made I have it saving to a
new table, an item setup table. This works fine. My question is lets
say for combobox1 it pulls table1's data, table1's data has 3 fields
of information, 1 is autokey, 2 is item number, 3 is classification of
item. in the combobox I have it where it only shows the item name with
the column widths being 0,1,0. This works fine, but when I save to the
new table, instead of the item name being stored it stores the number
of that item in the table. so lets say I have 6 items in table1 that
go into combobox1 named

1 apple
2 banana
3 grape
4 strawberry
5 orange
6 lemon

I want the name (apple) to go into the new table so I can query later
on by that name. Instead, the number goes into the table which in this
case would be 1 (apple). Does this make sense? I only would like the
name. Any suggestions. Thanks in advance.

Ryan

 

[Dale Fye]

Ryan,

Normally, to save disk space, developers will store the ID value in the
second table, they way you have it now. Then, if they want to actually view
the text, they would create a query that links the ID values from your new
table back to their source tables. Then they would display the textual data
in the query. So, if you had 2 tables tbl_Fruit (as in your example) and
tbl_Boxes(Box_ID, Box_Desc) that indicates the types of boxes you use for
shipping, then you might have a third table (tbl_Fruit_Boxes) which contains
a Fruit_ID and Box_ID fields. To see which fruit go with each box type you
might write a query that joins tbl_Fruit to tbl_Fruit_Boxes on the Fruit_ID
field, and joins tbl_Boxes to tbl_Fruit_Boxes on the Box_ID field. Then
bring the Fruit_Desc and Box_Desc fields into the query grid.

I'm no expert on JET, but my understanding is that this not only saves disk
space (integer values take up less space than text), but it also saves time
as it is quicker to index numeric fields than it is to index text fields.

HTH
Dale