By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,685 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,685 IT Pros & Developers. It's quick & easy.

Autofill of a field based on another

P: 2
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.
Jul 12 '10 #1
Share this Question
Share on Google+
4 Replies


Delerna
Expert 100+
P: 1,134
More detail

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

Create a query for my form
Expand|Select|Wrap|Line Numbers
  1. SELECT Table2.ID,[Weed ID],
  2.    [Scientific Name],
  3.    [Common Name],
  4.    [Collection place],
  5.    {Date]  ... etc
  6. FROM Table2
  7. JOIN Table1 on Table2.[Weed ID]=Table1.ID
  8.  
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
Expand|Select|Wrap|Line Numbers
  1.   SELECT ID,[Scientific Name] & " (" & [Common Name] & ")" FROM Table1
  2.  
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
Expand|Select|Wrap|Line Numbers
  1.    me.refresh
  2.  
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.
Jul 12 '10 #2

Delerna
Expert 100+
P: 1,134
You could alternatively use 2 comboboxes
with
Expand|Select|Wrap|Line Numbers
  1.  SELECT ID,[Scientific Name] FROM Table1
  2.  
for the data source for the scientific name combo

and

Expand|Select|Wrap|Line Numbers
  1.  SELECT ID,[Common Name] FROM Table1
  2.  
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
Jul 12 '10 #3

P: 2
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
Jul 12 '10 #4

Delerna
Expert 100+
P: 1,134
well.... you can, but it would be better for you if you had a stab at it first.
Jul 12 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.