Multi-table Data entry form

[halpme]

Hello! I'm fairly new to access, and I've been attempting to figure this out with no luck. :/

I have a pretty extensive database with something like 4 master tables all "foreign-keying" into a single relational table. (And then there are more shoot-offs from a couple of those master tables... but I won't kill us all off with info overload. XD)

I'm just wondering if there is some way that I can populate information into all those tables with a single form? I want to be able to enter data into the form and then have it go to the 4 master tables AND that last table that holds the relationships together.

(If there isn't a single form that I can do this with, what would be the next best approach?)

So, i.e., I have my master tables:

tblWorkers
tblCars
tblCompanies


tblVisit

And the child table would relate all the information with the unique identifiers (in this case: the SSN of each worker, the VIN for each car, the tax ID number for each company, and then the visit code for each visit scheduled).

Is there any possible way I can do this?

I know about forms/subforms, and I've rolled that around in my head to see if I could do something with that... but it seems as though the form/subform only fits when it's a single master/child table set up.

Any help would be wonderfully appreciated! Please excuse my noobiness if I completely missed an obvious thing.

Thanks!

[taxidev]

You can have any number of sub forms related to a main table.

But your setup looks more like a bunch of lookup tables. You might want to not link the sub forms, just display the 4 tables, and have a dropdown in each of the 4 columns in the "child table", so you can select from the other tables.

[halpme]

Hmm, the only problem with this is that the database would become huuuge. And to use a dropdown wouldn't be user-friendly if there are thousands of visits/workers/etc. :/ Also, often times I'll be updating the DB to have new workers or new vehicles, etc., and I'll need to enter that data into the tables (and was hoping to do that via forms).

So, i.e., I was hoping I could just enter something like: WorkerA went on VisitB to CompanyC with VehicleD, which would populate the relationship/Child table.

So, if these are previous workers/visits/companies/vehicles, it would pull the information from previously entered information in its respective tables via the identifiers; and if this is a new worker/visit/company/vehicle, then I would have the option to enter in the details of each (which would then populate to its respective tables) -- if that makes sense?

I apologize if my post is confusing! I greatly appreciate all feedback.

[halpme]

Not sure if providing more info would help, but...

Ideally, I'd like to enter data in this format (preferably in a single page of form/forms):
**Note: Most of the time the records I'm entering will be new and not existing information in the master tables.

- Visit Number with visit details

- Worker involved (with his/her details if new, or brought up from tblWorkers via SSN) -- if there's more than 1 worker, then I could just click a "Next" button and add in the second worker

- Vehicle(s) worker(s) drove (with vehicle details, and format same as above if there's more than 1 vehicle)

- Company/ies involved (format same as above)

I would assume that because I'm also adding this all in one form, that would kind of take care of the "relationship" portion of the tables in correlating all this information together.

I have no idea if I can set up a form to do something like the above.

Would greatly appreciate the use of anyone's awesome Access-guru brain!