Autofill of a field based on another

[MyWebdots]

Hi there,


I am a newbie and have written a database (access 2007) for plant management. I would most appreciate help to what I think is a very basic problem! I am really stuck on autfilling a field.

Topline: When I pick the Plant Scientific Name on my form, I want it to auto populate the Common Name.

Plant names are in TABLE one...(this also has a primary key)
Plant Scientific Name
Plant Common Name

TABLE two also has a bunch of other data, (this table includes two fields for the plants above as a lookup to that other table)
Date recorded
Place found
Co-ordinates.

I have created a form with all fields. Including the Plant ones as a lookup.

What do I need to do so when I pick my Plant Scientific Name - it auto fills my Plant Common Name?

Thanks so much in advance.

[RuralGuy]

Base your form on a query that joins the two tables.

[MyWebdots]

thanks ruralguy - - not sure what you are saying - a query to do what exactly...sorry for my noobness?

Currently My 1st database has:

Weed number (primary key)
Plant name scientific (lookup from table 2)
Plant Name common (lookup from table 2)
Field 4
Field 5
Field 6

2nd database has

number (primary key)
Plant name scientific (look up from table 2)
Plant Name common

My form has all the fields in Table 1.
Every time I pick either the 'plant name scientific' - I want the matching 'plant name common' to come up (these always match and never change). Vice Verse if I pick the 'plant name common' I want 'plant name scientific' to come up.

(the reason for this is when the scientists are in the field they sometime's know the plant name, and sometimes know the scientific name).

I have a feeling it may have something to do with the primary key -
Can I do this through a wizard or do I need to write some VBA code? If so - could someone help me? Many thanks!

[RuralGuy]

To start, you do not want to use LookUp Fields. All you need in the first table (not database) is the primary key from the other table (not database) as a ForeignKey. Use a ComboBox Control on your form for each look up you need: one for the scientific name and one for the common name. You can use code in the after update event of each ComboBox to set the ForeighKey field.

[MyWebdots]

Ruralguy - thankyou. That is starting to make sense. I now created a FK in my table. (and removed the lookups).

(see my newbieness calling tables 'databases!).

Can you please provide me the code in the after update for one of the combo boxes - and I will try the other?

Most apprecicated.

[RuralGuy]

Use the ComboBox wizard to place one of the ComboBoxes on your form. Select the option to look up a value in another table.

[MyWebdots]

Hi there - thanks for responding.

I have done all that - still just don't know the 'afterupdate' code!

Thanks

[RuralGuy]

[ForeignKeyField] = Me.ComboBoxName.Column(0)
...using your field and control names of course.