Avatar of Frank Freese
Frank Freese
Flag for United States of America asked on

Creating records with multiple record sources

Folks
I am creating an Access 2016 database. The database has 4 tables:
tblTopics where you can have many Topics. Each Topic has Sub Topics where you can have many Sub Topics. For example, a Topic might be Circular Reference with various Sub Topics to Circular Reference. tblSubTopics is the second table.
Sub Topics can have many Issues. For example, a Sub Topic to Circular Reference might be Finding Circular Reference. tblIssues is the third table.
Finally, an Issue(s) has a Solution(s). tblSolution is the fourth table.

I would like to have the ability to where I can enter in the Topic, Sub Topic, Issue and Solution. I do not want to save any data (create a new record for each table) until I have finished.  As I understand a form can have only 1 defined Record Source. I need 4 Record Sources, one for each table. Therefore, what are my options? Can I have multiple subforms or do I need to use the Tab control? Or is there another way? Is there a solution(s)? I have attached the Relationship Diagram for this database that might help
Relationships.png
Microsoft Access

Avatar of undefined
Last Comment
Frank Freese

8/22/2022 - Mon
John Tsioumpris

Are you trying to create a Search Form ?
Just join the tables in a query and use that query as source of your Form.
Frank Freese

ASKER
Not a Search Form John but a single form. In researching this I keep discovering that a form can have only 1 Record Source and will have three additional Record Sources. Maybe a series of subforms? I just don't know the best way to resolve this.
PatHartman

Your request deviates from relational database standard logic.  You are saying that you have a 1-1-1-1 relationship and ALL records must exist before ANY record can be added.  That simply isn't possible.  Even in a 1-1 relationship, one of the tables is the "parent" and THAT record MUST be created before any related record can be created in the second table.  Technically 1-1 relationships are 1- 0 or 1 to allow the parent to be created first.  That parent record must exist as 1-0 for some period of time until the related record is added.  Only a SINGLE related record is allowed for 1-1 relationships.  There is NO way to enforce a circular constraint since one record must be added before another. It just isn't possible to insert four records simultaneously that all relate to one another.

Stand back and think about the actual relationships.  You will almost certainly come up with  3 1-m relationships tbl1 --> tbl2 --> tbl3 --> tbl4.

A record gets added to tbl1.  then on the first subform a record gets added to tbl2, on the third level subform a record gets added to tbl3 and on the fourth level subform, one or more records get added to tbl4.  Then you step up to sfrm3 and add the second record there and move down to sfrm4 and add 1 or more records there.  Move back up to sub3 and if there are no more records to be added at that level, move up to sub2 and add the second record there.  move down through the hierarchy and bounce up and down as needed to create the child records on that branch.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Frank Freese

ASKER
I'm thinking seriously about your comments and truly giving them a lot of thought. I can see and understand what you are saying. I just haven't used subforms very much which sounds like the solution. Would you support that conclusion?  BTW, did you look at the Relationship diagram I enclosed?
ASKER CERTIFIED SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Frank Freese

ASKER
Allow me to accept this as a solution. However, I want to make sure that I completely understand how this is to work and the changes to my tables. So I will have a follow-up comment in response to your explanation, if that is OK with you?
John Tsioumpris

Well....i still haven't grasped your idea of how you want to implement this....
If you want to have loose data...then you could create an entry form that everything would be unbound...so no restrictions on this but in the end it would require that specific data are saved in a specific order.....would that is what you had in mind ?
Maybe a simple drawing of how you are thinking it i....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Frank Freese

ASKER
John, I think you are right on to what I need to be about doing at this time. I'll put together a "better" statement of work and in fairness post a new question differently. I am waffling now which tells me I have yet to clearly "visualize" my objective.  In the beginning I began to question the implementation of this database so I looked to ExpertsExchange for help. I am glad I did. I still believe in this database.
Thanks again - I'll be back soon