Link to home
Create AccountLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Creating records with multiple record sources

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
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

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


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.
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.
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?
Avatar of PatHartman
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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?
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