Access -- Make a macro and relational lookup

[Mac Hardy]

I have a database with 2 tables: Brds and Numbers.
Birds contains 250,000 records and several fields, including one named
SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
I want a macro to enter a new record in birds as follows. I type a 4-letter
abbreviation into the SPECIES field and the macro will find the record in
Numbers, ABBREV field, and return the SPECIES field of Numbers to the SPECIES
field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
field of Birds.
There is more but I will take any progress offered. I have made none so
far. Thanks. Mac Hardy

 

[tighe]

Mac,

there be a more elegant way but:
I would make it so where you type the ABBREV a combo box so you know you are
using one that exists, then on the after update event of the combo box, run
an append query that uses the Combo box data as filter to add the addition
fields information to the new record, or you could use the setvalue action in
a macro to Dlookup the other data based on the abbreviation.

hope this helps.

 

[Steve Schapel]

Mac,

There is a problem with the design of your tables. What you are proposing
will mean that the same information is stored in two different places within
your database. This is called un-normalised, and flouts one of the basic
rules of database design, so this leads me to believe you have misunderstood
one of the principles of relational data. Please let us know if you can
consider a schema change, and if so somebody will be happy to help.