Creating survey: getting data from previous table record in sequential tables
I'm creating a survey database. In the simplest version I have a 1 to many relationship between some tables, where after filling in an initial table, you have a choice to select one of three options, each of which will take you to a different table. I'd like to grab data from the last visited record in the table higher in the heirarchy and put it in the lower table.
To help clarify (maybe), it's a marine biology database, so the initial table has you select the location, diver, and assigns a unique survey ID. You then choose whether you're going to survey plants, animals, or ground type. The selection takes you to an appropriate table for an "encounter instance", eg, a fish sighting. On each fish sighting I'd like to have data taken from the first table (location, diver, survey ID). Standard LookUp doesn't work because the data I'm looking for isn't static. The only way I've got it to work so for is to write a script to copy and paste the data when I go from first table to the next as I make the selection, but I'm hoping there's a more elegant solution?
I'm happy to upload a pic of the ERD if people think that might help, or clarify in any way that would be helpful.
Any help would be much appreciated,