Creating records with multiple record sources

Frank Freese
Frank Freese used Ask the Experts™
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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer
Are you trying to create a Search Form ?
Just join the tables in a query and use that query as source of your Form.
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.
Distinguished Expert 2017
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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?
Distinguished Expert 2017
There are situations where you need to control the data in the child tables but your situation is not one where I see that as a requirement.

For example, in a batch data entry system, the data entry clerk enters a total of the checks in the batch and a dollar amount.  Then enters the details of each check.  The application flags the batch as incomplete when the batch record is complete.  Then at some point the user finishes the detail data entry and pushes the validate button.  The code then reads all the details and counts the checks entered for the batch and sums the dollar amounts.  If the two numbers equal what the data entry clerk entered in the batch header, the batch is flagged as "balanced" and the clerk moves on to the next batch.  This process uses a combination of human and computer processing to ensure that all the data is entered and is entered correctly.  This would be a common process for something like a Visa or Master Card check processing operation.

In your situation, you don't have batches.  Each table has 0 to many child records.  Intermediate levels can't be skipped but records are not required in all tables in all situations. So, you can have a record in tbl1 and tbl2 but not in tbl3 and tbl4 but you cannot have a record in tbl1, slip tbl2 and have a related record in tbl3 and tbl4.  This is the way genealogy works.  You can't skip generations.   If you need to ensure that the top three tables have at least ONE child record each, you can do that but the easiest way is to add a completeFlg in each of the top three tables.  As a record is created, it defaults to No.  Then when a child record is added, you update the completeFlg on the parent record.  When a child record is deleted, you count the remaining records and see if there is still at least one child record.

For this type of relationship, you must enforce RI to ensure that records can't be deleted if they have child records.  Although, you might want to specify Cascade Delete if that makes sense for your situation.  So if you delete a tbl2 record, Cascade Delete will delete all related records in tbl3 and tbl4.  Cascade delete is of course dangerous so make sure that is what you really want.  I always specify cascade delete on the relationship between tblOrders and tblOrderDetails but NEVER between tblCustomer and tblOrders.  If I delete an order, I want all the details to go away but I don't want to be able to delete a Customer if he has ever made any order.
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 TsioumprisSoftware & Systems Engineer
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 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....
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial