How to change RecordSource of Sub Report at runtime

[SmokeEater]

I have a report with one subreport. The subreport is based off of a query. I have code that will change the query but can't seem to get the subreport to refresh with differernt data. The subreport is in a header and I am using the onformat event of the header to fire the code to change the query the subreport is based on.

 

[dhookom]

I would change the SQL property of the subreport's record source query prior to opening the main report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SmokeEater]

OK, that will handle the first time the header fires. What about any of the following times. The subreport does not update with new info.

 

[dhookom]

It is near impossible to set the Row Source property of a chart or the Record Source property of a subreport from within the main report. I believe these properties must remain consistant for all instances. The only thing that can vary is based on the Link Master/Child properties.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[SmokeEater]

OK, If we assume that the record source property can't be changed, and it's a query, can the criteria for the query change and the sub report be refreshed/requeried to reflect the change in the query's criteria?

 

[dhookom]

Where/how/why would you change the criteria of the subreport?

I have never had a need to do this before so I haven't tried it. I can't imagine the need for this or how to set up a situation such as yours to even test.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jjd100]

I have this problem for the following reason. My subform's recordset SQL includes a field in the main form's recordset that I perform arithmetic on, and when I close the application it closes the main form first, then the subform complains that the main form's field is undefined (box asking to "enter parameter value"), then it closes the subform after I hit Enter. I need to get rid of this irritating message, running "sendkeys {enter}" on the main form close event works but is a bit cheesy.

 

[dhookom]

You would have better luck with this question if you:
1) asked in the Access Forms forum
2) started a new thread

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]