Subform linked to another subform only showing data after form in design mode (1 Viewer)

I work for a General Contractor and am working on a client database. For each project there may be multiple architect firms assigned to the project and multiple architects within those firms assigned.

I went back and forth trying to decide which made the most sense from a design perspective:
- Project_ArchitectFirm_x table and a Project_ArchitectFirm_x_Architect_x table
- Project_ArchitectFirm_x table and a Project_Architect_x table
I ended up going with the first, but it is not set in stone yet so I am open to input.

The issue I am having is on the client form where I have an Architects Firms datasheet subform linked to an Architect datasheet subform. When I open the form directly the Architect Firms show up and the Architect datasheet has a record, but the data is not being displayed. If I switch to design view and then back to form view, the data in the Architect record is displayed. It also works if I put a refresh in the main form OnLoad event, so it has something to do with the order the controls are being loaded. It makes sense since the subforms get loaded after the main form that the [Architect Firm ID] control on the main form would not be populated yet.

There is a filter on the Architect combo box: [screen].[activeForm]![ArchitectFirmID_from_Sub]. When I remove this filter the architect data is displayed, but it is all architects in the intersection table not just the ones specific to this project.

Putting a refresh on the OnLoad event seems very kludgy and has the potential for slowing things down.

I tried delaying the loading of the form by leaving out the architect subform source and link fields, then populating them in the OnLoad event of the main form. I would have thought that would have the same effect as the refresh, but it ends up with the same results as the original form.

What do I need to do to get this working?

Research Normalization

Here is a link to several articles and formats related to Database Planning and Design.
I recommend you work through the tutorials from RogersAccessLibrary mentioned in the link.
Good luck with your project.

Instead of [screen].[activeForm] use [forms]![Project - Add/Edit Bid].

OK. So it works if I explicitly reference the form, but why isn't working when I use the screen.activeForm rather than the explicit reference to the form? These subforms are being used on two main forms, which is why I went with screen.activeForm. Not a big deal in this instance to create a copy of the subform, but I would like to understand why it does not work in this instance for future reference.

From FMS https://www.fmsinc.com/microsoftaccess/forms/controls-vba/index.html

You'll recall that the Screen system object allows you to refer to the form or control that is currently active. We can use this with the ActiveForm and ActiveControl properties to refer to the currently active form and its controls. We use the dot operator because we are referring to properties:

Screen.ActiveForm![Zip Code]

refers to the Zip Code control on the currently active form

Screen.ActiveControl

refers to the control that currently has focus on the currently active form.

You should generally avoid using this method in your macros unless you are sure the desired form will be active when the reference is made. It can be easy to lose track of which form is active! Also, when you are stepping through your module code with the debugger, the module window will be active, and the Screen system object will not work. This can make code that uses the Screen system object difficult to test.

I looked at the database and frankly it's a mess. Do some studying on Normalization, (another post has a couple of links. If you can find an old Litwin & Getz book on Access development I still think it was the best around). Normalization does take a bit of work to get your head around. In short, you need a Projects table, a Firms table, a tlkpFirmTypes table with a corresponding field in Firms. Then a ProjectsFirms table that links the Projects to specific firms. I don't know what all those "future" fields are, but get rid of them. Add them when you need them and don't clutter the design. Forget you comment about yes/no fields. Just set the default value and/or make it required. Works fine. You can use the FirmType to filter the various forms for the different Firm types, and the linking becomes real simple. (ProjectFirms will need a FirmID and a ProjectID, Projects will have ProjectID, and Firms FirmID. Linking will be on Projects.ProjectID to ProjectFirms.ProjectID. Duck soup.

JDRAW – I am familiar with the article you quoted, but in my application [screen].[activeForm] is not being used in a macro, so it does not apply in this instance. I use [screen].[activeform] to filter the Row Source query on the Architect combo box which is on the Architect subform. It points to an unbound control on the main form that holds the ID for the selected Firm from the Firm subform. I would prefer not to use a static reference if it can be avoided.

Royce – In regards to your ProjectFirms suggestion, I already have an intersection table between the project and the architect table. It is Project_ArchitectFirm_x. (The reason that there is both an Architect table and an Interior Designer table is that the Interior Designers were an add to the original application which was already in production. If only development was always a straight line!)

My question was not about a simple intersection table; it was about the relationship between two intersection tables. I have an Architect Firms table and an Architects table. Both architect firms and the individual architects from those firms will be tied to a project. I just wasn't sure if it made more sense to link the ProjectArchitects intersection table to the ProjectArchitectFirm intersection table, since you can’t have an architect on the project without first having the firm, or does it make more sense from a design perspective to link the ProjectArchitect intersection table straight to the Project table and the architect would, of course, still be linkable to the Architect Firm through the ArchitectFirm ID on the Architect table. Obviously, the form design in either case would not allow you to add an architect before adding the firm. I scanned through the links about normalization and did not see this addressed.

I agree that the Future fields are extraneous. It is a design construct left over from the the last database I did where I would occasionally send out updates to people with whom I had no other contact. I preferred having extra fields to use for design changes instead of having to programmatically create new fields in the back-end tables when I sent out updates for the front-end. There were a lot of different variables with all the different end-users, and I wanted the upgrade to be as bullet proof as possible so that no intervention would be required from me. I have been planning to remove them.

Look at my example below on the man screen it links two subforms hope it helps


https://www.access-programmers.co.uk/forums/showthread.php?t=303130

If a large architectural firm has dozens of Architects, and different ones will be assigned to different projects from time to time, and you need to know "which architects are assigned to this specific project" then you need a ProjectArchitects table that with ProjectID and an ArchitectID. (From the List of specific Architects you can generate a list of firms.) However, if you only need to know which Firms are the architects on this project then you would need the Project_Firms table. (A database for an Architectual firm would be different than a database for a General Contractor. The Firm would probably need a ArchitectsProjects table which would should all the projects an Architect is assigned to.) "The best design" is driven by purpose. Sometimes you need one thing for the people entering the data, and another for the people using the data. "Normalization" rules help design an efficient database. They do not tell you anything about the joins that may be needed. That is purpose driven. e.g. "Jones & Jones Architects" changes it's name to "Jones, Smith and Jones Architects" a good design makes that a trivial change.


Similarly, the right Keys in your tables means that you can use a simple ProjectID - ProjectID link in your subforms and everything works. You don't have to worry about all the "Oh, I have to write code to requery this subform because..... ", and as you have already discovered, your present design is making you do that. To me it looks like you can change the design and subforms linkage becomes much simpler.



Also, it is counter-intutitive, but SubForms load BEFORE main forms.