If a field in Subform 1 needs to serve as a master link field for Subform 2, then one way to do it is to add a text box on the MAIN FORM and set its control source to be:

     =[Subform 1].[Form]![master link field name]

See the picture below as an example.  I add a text box that references a subform's control, to be used as a "link master field" for the second subform.  This text box needs to be invisible, of course, because you don't to show weird numbers.  Name this text box, and use this name as a "link master field" for your second subform.

Nested Subforms
Synchronize Subforms

Here are some of Richards Videos in case.

V/r
Juan

Hi, I tried nesting subforms, but the problem is that it appears that nesting forms built using different relationships don't work. For example, the main form uses a one-to-many relationship while the subform uses a many-to-many relationship. It seems to me that Access is not equipped to mix different relationships in this manner. In any case, at this point in my project it seems I have two options to move forward. Have the user enter (select) data twice in two different forms or use a combination of Combo boxes and Lookup wizards. I'm think the latter option will be more user friendly.

DOnt panic it can be done show us a screen shot of what you got in design view.

Thanks, I am not panicking, but my design is way too complicated. Going back to the drawing board and looking at using Sharepoint list or Access Lookup fields and calling it a day. In any case the Audit form (as shown above) uses three Subforms.

The Current Placements uses a subform that tracks the youth to the foster home they are placed in using a many-to-many relationship via a junction table. (and this is probably the problem -mixing a many-to-one relationship in a form with a many-to-many relationship Subform)

The Audit Month tracks each month the audit is completed. (Monthly audits are required)

The Auditee and the Peer Auditor are staff selected to audit on the particular month.

The problem is that I cannot select the Foster Family to be audited because a Combo box drop down select function does not work in another form environment. My workaround is to simply use Lookup Wizards, which should not be an issue for something as basic as:

1) Selecting the Foster family to be audited
2) Selecting two staff members to be the auditors (Auditee and Peer Auditor)
3) Recording and tracking each audit month (the when)
4) Noting that the audit was completed and by what staff persons.

In short, I don't see how the 4 process above can be manipulated in a single form using subforms. This app is initially for only one user and forcing that user to jump around to separate forms is not going to work. I am pretty sure this app requires some advanced VBA and macro coding that are beyond my current capabilities, hence my use of Lookup tables as a interim solution.

I dont see the master  and child fields.  Looks like tooooooooo much info on a screen have you looked at the ABCD?  Richard shows us how to manage several forms with tabs.  A good free video that show what you might try is the Vehicle Maintenance  If this is more to you taste then the ABCD database will rock you.  Vehicle database is simple and can manage several subforms at a time not all at one time.  look it over and let me know.

V/r
Juan

I think I'm good. I rebuilt my database using Lookup wizards in an hour. Quick and super easy.  Since my organization does not have an SQL server for Access, I will be using Dataverse and/or Sharepoint Lists as the data source for Access Forms. Exporting Access tables with Lookup Wizards to Dataverse automatically creates relationships in that environment.  PowerApp forms are then created from the Dataverse tables or Sharepoint lists for mobile apps. So, in this environment, the debate of using lookup wizard is moot. In other words, I am using Access more as a modeling tool and it seems to be working well for this purpose.

Good to know   this shows there is more than one way to get the job done.  Best of luck and good Job, you did all the leg work.

V/r
Juan

Thanks, Juan

I'll post the results here if this actually works as advertised.

Well, as it turns out, the idea of exporting Access tables to Dataverse tables and creating mobile Power apps from the Dataverse tables looked good on Microsoft paper, but not in reality. Power Apps is not even close to Access and I don't have the faintest idea on how to share  Dataverse forms with others. Power Apps and Dataverse simply isn't a viable option to Access.

My organization is paying for Power Apps licenses that none in my organization are using due to our collective lack of Power Fx coding knowledge required to build Power Apps. I am going to explore cutting this subscription cost from our budget and looking at an SQL solution. Can anyone recommend an easy to use, affordable SQL server solution?

Thanks.

I think there is a free ver. of MS SQL that you can set up and start playing with.  Get your feet wet before you dive into the deep sea...  for me SQL is the best.  SQL is a cookie that needs to be eaten small bites at a time.  

V/r
Juan

Thanks Juan,

I'll look into MS SQL to start. Dataverse seems to be handling my exported Access tables and the .accde front end file is working. I will test dataverse for awhile to see how it holds up. I also confused Dataverse with Power Apps as the former is only a data source and cannot generate forms, queries or reports. Power Apps can be created from SharePoint lists but not from Dataverse tables. DV has potential someday to replace Access if Microsoft ever figures out a way to allow users to create and share forms and queries in that environment.

If your users need to access the database anytime, anywhere, on any platform or device, then Power Apps would be good for you -- since Access is only for Windows desktop usage, and is iffy for remote access.  The cost for Power Apps could be steep; the cheapest plan is $5 per user per app per month.  If you have 10+ users it could be costly.  

Regarding coding, Power Apps uses only Power Fx code -- no VBA, no macros, not even SQL.  If you are already proficient in Access, it would be (much) easier to learn Power Apps.  I trialed Power Apps for a month with no knowledge of it beforehand, and was able to make this simple CRUD form:    https://www.youtube.com/watch?v=Y6eOT4YjFsc

SQL Server Express is free ( https://www.microsoft.com/en-us/sql-server/sql-server-downloads ).  But if you need to put data on the Internet, you also need to sign up for a online hosting service like Winhost.  The cheapest Winhost plan, at $60/year, allows 500MB of space for your database.  Depending on how much data you have, that may be (way) more space than you need, or barely enough space.  (Case in point: in my old job, I had 20+ years worth of company data, and 500MB was barely enough space.)  In addition, you need to create an Internet domain for the hosting.  That's another $10-20 a year, paid to an Internet "registrar", who owns the domain and charges you for it.

SQL Server is just a back end (a more "robust" BE than Access).  You still need a front end app like Access or Power Apps to use that data.  You can also create a web interface as a front end.  A web interface can be used anytime, anywhere, on any device that has a web browser, just like Power Apps.  A web interface requires a whole different way of coding (with ASP, ASP.Net, etc.), just like Power Apps.

In my old job, I started using Access in the 90s as both BE and FE.  But in the early 2000s, I switched to SQL Server as BE, and web interface as FE for my users, due to all the advantages mentioned above -- more robust BE, and a FE that could be used anytime, anywhere, on any device.

Hi Kevin, after testing I decided that Dataverse won't work for Access. The platform is extremely complicated, unintuitive and requires heavy IT support to dataverse apps. Unfortunately, my org has a part time IT person who is not well versed in the Power Platform environment. Like dataverse, I also determined that sharepoint lists are an inadequate data source for Access. Not sure why Microsoft built the connecters to both given their limitations.

I am going to look into the SQL Server Express your mentioned and see if I have the budget to set up and SQL server.

Thanks.