Creating a new record in only one table of underlying query

[1redhotraider]

Background: My database design has the following tables: GROWER, ENTITY,
FARM. GROWER to ENTITY relationship is one-to-many. ENTITY to FARM
relationship is one-to-many. (ENTITY record contains a foreign key to
GROWER. FARM record contains a foreign key to ENTITY. So a GROWER record
must exist in order to create an ENTITY record. An ENTITY record must exist
to create a FARM record.)
Challenge: I have created a form based on a query that relates all of these
tables. I have created a tabbed control on the form with 3 pages to display
grower, entity, and farm. I have created a button that moves me to a new
record and lets me enter data for all of these - successfully creating 3
brand new related records in each of the 3 tables. I would like to create an
additional button that allows me to create a new ENTITY record using the
currently displayed GROWER record. I would like to display the grower info on
the grower tab page and make the other tab pages allow for new input. (Then
eventually also to create a new FARM record using the currently displayed
GROWER and ENTITY records.)

Your help is greatly appreciated!

 

[Jeff Boyce]

It isn't quite clear from your description, but I suspect that you are NOT
using subforms on your tab control pages.

If the relationships are one-to-many and (ENTITY=to=FARM) one-to-many,
mainform/subform is a standard approach.

If you've used a query that joins all the tables together, you may run into
difficulties (Access has problems with figuring out which table gets updated
in multi-table queries).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[1redhotraider]

Thanks for your quick response, Jeff. Please forgive my long explanations -
first time posting (and it's been a few years away)!

I started with subforms on my tab control pages, but had difficulty using
comboboxes to navigate through different records. Currently I have
comboboxes on the main form that allow me to navigate to different records
based on different tables. Can you suggest a better way to handle this?

Thanks so much!

 

[1redhotraider]

Basically I have numerous one-to-many cascading tables. (I'm not sure that's
the correct terminology.) Each table references the one above it. I'd like
to find the best way to display all of the information, as well as, be able
to add new records to any table. Hope that clarifies!

 

[Jeff Boyce]

The commonly accepted approach is to use subforms on the tab control pages.

If you run into difficulties with that, post back here to the forms or
formscoding newsgroups for ideas...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.