Why does creating lookup wizards delete my data??

[rocketD]

Hi All,

I'm working on a data table in which I'm trying to create lookup
wizards for two fields: Genus, and Species. I've got the lookup
tables complete, each with 2 fields (an ID and the latin name). I want
to use the lookup tables for the Genus & Species fields in my parent
table, so that when I create the form for future data entry, the fields
will have a drop-down box. However, there is already a ton of data in
those fields in the parent table, and every time I try to add the
lookup tables, it says it will delete all of the values in those two
fields. It would be a horrendous amount of work to re-add it, there's
more than 7,500 records. I've tried appending the data into a
structure only copy which has the lookup tables related already, but
that doesn't work either. Can anyone provide some advice, please??

Thanks,
Dara

 

[John Vinson]

Hi All,

I'm working on a data table in which I'm trying to create lookup
wizards for two fields: Genus, and Species.

Well... don't.

See http://www.mvps.org/access/lookupfields.htm for a critique.

I've got the lookup
tables complete, each with 2 fields (an ID and the latin name). I want
to use the lookup tables for the Genus & Species fields in my parent
table, so that when I create the form for future data entry, the fields
will have a drop-down box.

It is NOT necessary to use the lookup wizard to do this. Instead, use
a Form for your data entry, and use the Toolbox combo wizard to create
combo boxes on the form.

This would have the advantage that (unlike a table lookup) you can
make the Species combo box depend on the selected genus, so that only
those species belonging to that genus are available.

However, there is already a ton of data in
those fields in the parent table, and every time I try to add the
lookup tables, it says it will delete all of the values in those two
fields. It would be a horrendous amount of work to re-add it, there's
more than 7,500 records. I've tried appending the data into a
structure only copy which has the lookup tables related already, but
that doesn't work either. Can anyone provide some advice, please??

The Lookup Wizard *replaces* the contents of your field with Long
Integer numeric values linked to the lookup table (and then conceals
those numeric values behind the "lookup" combo box). A genus/species
table really would NOT benefit from having all the genera and species
replaced by numbers, in my opinion!!

John W. Vinson[MVP]

 

[John Vinson]

Hi All,

I'm working on a data table in which I'm trying to create lookup
wizards for two fields: Genus, and Species.

Well... don't.

See http://www.mvps.org/access/lookupfields.htm for a critique.

I've got the lookup
tables complete, each with 2 fields (an ID and the latin name). I want
to use the lookup tables for the Genus & Species fields in my parent
table, so that when I create the form for future data entry, the fields
will have a drop-down box.

It is NOT necessary to use the lookup wizard to do this. Instead, use
a Form for your data entry, and use the Toolbox combo wizard to create
combo boxes on the form.

This would have the advantage that (unlike a table lookup) you can
make the Species combo box depend on the selected genus, so that only
those species belonging to that genus are available.

However, there is already a ton of data in
those fields in the parent table, and every time I try to add the
lookup tables, it says it will delete all of the values in those two
fields. It would be a horrendous amount of work to re-add it, there's
more than 7,500 records. I've tried appending the data into a
structure only copy which has the lookup tables related already, but
that doesn't work either. Can anyone provide some advice, please??

The Lookup Wizard *replaces* the contents of your field with Long
Integer numeric values linked to the lookup table (and then conceals
those numeric values behind the "lookup" combo box). A genus/species
table really would NOT benefit from having all the genera and species
replaced by numbers, in my opinion!!

John W. Vinson[MVP]

 

[rocketD]

Hi John,

Thanks! That sounds like exactly what I wanted in the first
place...I'm a novice with no guidance, so I appreciate yours. I'll
give it a shot.

Dara

 

[rocketD]

Hi John,

Thanks! That sounds like exactly what I wanted in the first
place...I'm a novice with no guidance, so I appreciate yours. I'll
give it a shot.

Dara