Dynamic Report Record Source

[enjiel]

Reading through the various forums, this seems to be a pretty common question. In some form or another, people are wondering how to make their subreport's recordsource dynamic. I've lose count of the different ways that I've tried and the issue, I think, is that my main report is not a parent of the subreports that are called, in fact, they are independent of the main report.



The report is for planning city projects. The main report prints everything from a table (Project) and at the bottom of the report I have 3 subreports that query from other tables (Equipment, Labor, Materials).

Obviously everything displayed in the reports depends on the name of the project that was selected before the report is generated, which means that the recordsource needs to be dynamic.

Private Sub Report_Load()
Dim WorkOrder As String
WorkOrder = Forms!Project.WorkOrderCombo.Column(0)  //get the name of the project (or workorder) from the main form
SQL = "SELECT * FROM EquipJoin WHERE [ID] LIKE '" & WorkOrder & "*';"  //build a query to find all equipment that is used in this workorder
Me.RecordSource = SQL
End Sub

To note: The architecture of the database is as so:
1 table to list projects
1 table to list equipment
1 table to join projects and equipment
a table for materials (and a table to join mat/projects)
same for labor

The IDs for the Join tables is built as so: WorkOrderName:EquipmentID#
ex. project1:00012
with a semicolon to separate the values. This way I can search all equipment, for instance, with LIKE *

In any case, I can get the report to run but the subreports won't populate until I actually click on the subreports themselves.
The best answer that I've found, so far, (I suppose) is that I'm going about it the wrong way. Usually, when I'm stuck like this that is the case, but I can't think of another way to accomplish this.
I'd like the data for the subreports to populate without need for the user to click on each subreport.

[ranman256]

The subform should populate automatically IF you have the sub form bound by the query and the linking values to the master form,
Is this true?

if so, you could try the ON CURRENT event in the master form to refresh the sub form.

[enjiel]

The subform should populate automatically IF you have the sub form bound by the query and the linking values to the master form,
Is this true?

if so, you could try the ON CURRENT event in the master form to refresh the sub form.

That's the problem. I don't know how to bind it to a query that has to be generated through vba (because an element of the query is a variable obtained from another form).
Also, there is nothing to link the subforms and the form that they load in. The primary form loads all overarching project data from the project table. The subforms load all data concerning that project using conjunction tables. The project ID that is the PK in project table is used to generate the PK for the junction tables but there are no direct relations otherwise.

The record sources on all reports are generated by VBA on load event.

For the main report:

Private Sub Report_Load()
Dim WorkOrder As String
WorkOrder = Forms!Project.WorkOrderCombo
Me.RecordSource = "SELECT * FROM Project Where [WorkOrder] = '" & WorkOrder & "';"
Me.WorkOrder = WorkOrder
Me.TCostEst = Forms!Project.TCostEst
Me.StopDate = Forms!Project.StopDate
Report.Requery
End Sub

For the sub reports:

Private Sub Report_Load()
Dim WorkOrder As String
WorkOrder = Forms!Project.WorkOrderCombo.Column(0)
SQL = "SELECT * FROM MatJoin WHERE [ID] LIKE '" & WorkOrder & "*';"
Me.RecordSource = SQL
End Sub

[enjiel]

I see what you mean.
This fixed the problem:

Private Sub Report_Current()
Me.[EquipJoin SubReport].Requery
Me.[LabJoin SubReport].Requery
Me.[MatJoin SubReport].Requery
End Sub