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?
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?