Autopopulate data based on a relationship

[kwall2011]

Didn't know quite how to title this. I am really new at Access, and after the past 3 hours reading tutorials, I think I am further away from my goal haha. Thus ya'lls help.

I am trying to create a 2-table database where we can enter our survey data. I want them to be linked by "head"# so that the "Temp" "Date" "island" and "site" are autofilled into the "time dep"table from the data I entered in the first "perm"table.

I would also like if the lookups were filtered so that, for example, when I choose "Cat Island" my only two options are "fishbowl" and "Cuda" then from there depending on which of those I choose, The "head"# can be chosen from a dropdown as well. There are about 22 coral-heads per site, and two sites per island. I don't want to have a giant dropdown of 80+"head"#s, but at the same time, if we are typing them in, it increases chance of error. limiting the options of head number to be site specific would be ideal.

I feel like I may be going about this all wrong, and need a form? But I cannot figure out how to make the form data fill the datasheets that I have created.

I dont know what the best way is to do this, so any help is GREATLY appreciated!

Thanks


Metapop.accdbMetapop.accdb

[June7]

Sounds like you need a form/subform arrangement - review http://office.microsoft.com/en-us/ac...010098674.aspx

Also sounds like you need dependent (cascading) comboboxes - review http://datapigtechnologies.com/flash...combobox2.html

[kwall2011]

OK, I still cannot get it to work. I have redesigned everything using forms, and I just cannot figure out how to link them up to do what I want.

Maybe now that there is more to work with I can get a bit more specific help? I have been working at this for a long time, and have learned a lot, but I have kinda hit a wall and dont know where to go from here.

New goal!! ....

I set up 2 tables that contain constant data for the sites: tblsite and tblhead

My end goal would be to enter all the constants into those two tables so when I go into the main table to enter the data, it autofills the constants based on the date/site/head combo.

For example: in the frmdata Form, I want to manually enter the date, and the island. Then I would like the site to be filtered by a combo box (I still cant figure out how to achieve this). Then based on the combination of site and date (each site will have surveys from multiple dates and the temp and current will change date to date) it auto fills the current, depth, and temp.

Then i will enter the other 3 values for that site.

Once I get to "head' I would like head to be filtered by a combobox based on the site I chose. Once I select a head, it auto populates the width, length,height and complexity from my tblhead data. This should always be constant.

Then I can enter the last 4 values.

that way I dont have to keep re-typeing the width, height, current ect for each coralhead. This will cut way down on data entry errors and all the data can be in one convenient table.

Please help! Thank You!!!Metapop-3tables.zip

[June7]

Doesn't work because there is no primary/foreign key fields for linking tblsite with tbldata. Also need to set head field in tblhead as primary key. You are also duplicating data in tbldata. Only save primary key value from tblhead and tblsite into tbldata, not all the related information.

Fix the primary/foreign keys and then setup form/subform arrangement as shown in previously referenced tutorial. Remove the duplicating fields from tbldata.