How to requery a subreport from a command button located at a form

Question

Hello everybody
I have a main report includes a subreport, the subreport is based on a query with two criteria. Once a command button is clicked, the main report  should be opened in preview mode and the subreport requeried, so that the shown data fits my criteria. I tried the following code but no works : 

Private Sub Command24_Click()

DoCmd.OpenReport "SummaryFirstCourse", acViewPreview, "", , acNormal
Reports![SummaryFirstCourse]![SummaryFirstCourseSubReport].Report. Requery

End Sub

Any help please ?

Answer 1

Hi. This may be a brute force approach but try to close the report first and then open it again. Just my 2 cents...

Answer 2

Some explanation of the rationale behind your question would be helpful.  The parent report's and subreport's recordsets are loaded when you open the parent report, so why would you need to reload the latter by calling the Requery method?

---

Ken Sheridan, Stafford, England

Answer 3

Private Sub Command24_Click()

DoCmd.OpenReport "SummaryFirstCourse", acViewPreview, "", , acNormal
Reports![SummaryFirstCourse]![SummaryFirstCourseSubReport].Report. Requery

End Sub

Hi Alef,

Is the space before  Requery  the problem?

Imb.

Answer 4

Some explanation of the rationale behind your question would be helpful.  The parent report's and subreport's recordsets are loaded when you open the parent report, so why would you need to reload the latter by calling the Requery method?

---

Ken Sheridan, Stafford, England

  Because the requery criteria based on two combo boxes in the form, so when the 2 combo boxes get values then the subreport should be required according to those 2 values 

Answer 5

Private Sub Command24_Click()

DoCmd.OpenReport "SummaryFirstCourse", acViewPreview, "", , acNormal
Reports![SummaryFirstCourse]![SummaryFirstCourseSubReport].Report. Requery

End Sub

Hi Alef,

Is the space before  Requery  the problem?

Imb.

 Actually not, the problem is here

Reports![SummaryFirstCourse]![SummaryFirstCourseSubReport].Report. Requery

I don't know how to call a subreport from a form

Answer 6

Because the requery criteria based on two combo boxes in the form, so when the 2 combo boxes get values then the subreport should be required according to those 2 values 

Requerying neither the parent report's nor the subreport's RecordSource query is necessary.  Provided the form containing the controls is open when the OpenReport method is called the parameters are capable of evaluation by the query.  The parent report can either be opened by code in the form's module, or the form can be opened from code in the report's module, in which case the report is opened directly, without the need to open the form first.

For examples of the former see MultiSelect.zip.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo includes illustrations of opening a form from a dialogue from in which controls are referenced as parameters by the report's query.

For examples of the second approach see ReportDialogue.zip in the same OneDrive folder.  This file illustrates two methods of opening a dialogue automatically when a report is opened, using code in the report's Open event procedure.

In either method, whether the parameters are referenced by the RecordSource of a parent form or a subform is immaterial.

---

Ken Sheridan, Stafford, England

Answer 7

Because the requery criteria based on two combo boxes in the form, so when the 2 combo boxes get values then the subreport should be required according to those 2 values 

Requerying neither the parent report's nor the subreport's RecordSource query is necessary.  Provided the form containing the controls is open when the OpenReport method is called the parameters are capable of evaluation by the query.  The parent report can either be opened by code in the form's module, or the form can be opened from code in the report's module, in which case the report is opened directly, without the need to open the form first.

For examples of the former see MultiSelect.zip.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo includes illustrations of opening a form from a dialogue from in which controls are referenced as parameters by the report's query.

For examples of the second approach see ReportDialogue.zip in the same OneDrive folder.  This file illustrates two methods of opening a dialogue automatically when a report is opened, using code in the report's Open event procedure.

In either method, whether the parameters are referenced by the RecordSource of a parent form or a subform is immaterial.

---

Ken Sheridan, Stafford, England

 The form is already opened, it has 2 combo boxes, so when the values of the 2 combo boxes selected, I want to show the desired data via report which consists of the main report ans subreport. The subreport is based on a query with 2 criteria. the 2 cruteria get the data from the two combo boxes so .....

Answer 8

Hi. I take it my suggestion did not work? Sorry...

Answer 9

 The form is already opened, it has 2 combo boxes, so when the values of the 2 combo boxes selected, I want to show the desired data via report which consists of the main report ans subreport. The subreport is based on a query with 2 criteria. the 2 cruteria get the data from the two combo boxes so .....

Hi Alef,

Just a Requery of the RecordSource of the Subform, does not change a thing. You have to modify the values for the two fields, that come from the combo boxes.

Opening the Report again, will probably only activate the Report, and bring it to foreground, without any further action.

You could try to close the Report first, and then open it again, for an update of the data.

Another way would be to construct a new SQL-string for the new selection, and assign that directly to the RecordSource of the Subreport.

Imb.

Answer 10

The form is already opened, it has 2 combo boxes, so when the values of the 2 combo boxes selected, I want to show the desired data via report which consists of the main report ans subreport. The subreport is based on a query with 2 criteria. the 2 cruteria get the data from the two combo boxes so .....

There seems to have been an assumption in this thread that the report is already open, but I can see nothing in any of your posts to suggest that.  On the contrary, the requirement seems to be merely to open a report which includes a subreport whose query references controls in a form as parameters.  All that is necessary for that is to open the parent report in print preview by calling the Openreport method from the form.  No Requerying is required.

---

Ken Sheridan, Stafford, England

Answer 11

...

You could try to close the Report first, and then open it again, for an update of the data.

...

Imb.

I'm glad I'm not the only one who thinks that. Cheers!

Answer 12

There seems to have been an assumption in this thread that the report is already open, but I can see nothing in any of your posts to suggest that.

...

Ken Sheridan, Stafford, England

Hi Ken. I may have started that confusion. Sorry...

Answer 13

 The form is already opened, it has 2 combo boxes, so when the values of the 2 combo boxes selected, I want to show the desired data via report which consists of the main report ans subreport. The subreport is based on a query with 2 criteria. the 2 cruteria get the data from the two combo boxes so .....

Hi Ken,

Sharp observation.

I jumped in the flow, and assumed once the report is opened, and you change the setting of the combo boxes ...

Imb.