Autofill of a field based on another

[MyWebdots]

Hi there - thank you in advance - up front - I am a newbie at Access.

I have built a basic database for a Field Researcher documenting weeds.

Problem: I want to auto fill fields based on the another:

Details:
Table 1:Names
ID: Primary Key
Weed Scientific Name
Weed Common Name

Table 2:Names
ID:Primary Key
Weed ID: Foreign Key
Collection place
Date
Field 5
Field 6
etc

One Form with combination of above.

The user needs to select the Weed Scientific Name on the form - and have the Weed Common Name autofill.

OR vice versa - select the Weed Common Name and have the Weed Scientific Name auto fill.

I have worked out that it probably requires Combobox and something to do with the foreign key and after update code. ...but that's where I end. I'm just not sure what to do!!

Please help me! Thank you so much in advance.

[Delerna]

More detail

I will hazard a guess based on what you have posted
I would probably

Create a query for my form

SELECT Table2.ID,[Weed ID],
   [Scientific Name],
   [Common Name],
   [Collection place],
   {Date]  ... etc
FROM Table2
JOIN Table1 on Table2.[Weed ID]=Table1.ID
 

Then I would let the query wizard create a columnar form bound to that query. Could also use a continuous form.

I would then design that form and add a combobox with the following query as its datasource

  SELECT ID,[Scientific Name] & " (" & [Common Name] & ")" FROM Table1
 

I would also ensure that the ID field in the combobox was bound to the [Weed ID] field on the form.

I would also probably put

   me.refresh
 

into the after update event of the combobox.

Anyway, something along those lines should work for you.
It's difficult to give an exact answer because
1) You don't have enough detail.
2) I don't have the DB in front of me.

[Delerna]

You could alternatively use 2 comboboxes
with

 SELECT ID,[Scientific Name] FROM Table1
 

for the data source for the scientific name combo

and

 SELECT ID,[Common Name] FROM Table1
 

for the data source for the common name combo

Both combo's would need their ID field's bound to the [Weed ID] field of the form

and both combo's would need the same after update event

[MyWebdots]

Hi Delerna,
Thank you very much for repling. Should I send you the database to have a look -would you be happy to do have a look

Cheers

[Delerna]

well.... you can, but it would be better for you if you had a stab at it first.