none
How to requery a subreport from a command button located at a form RRS feed

  • 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 ?
    Thursday, December 31, 2015 5:25 PM

Answers

  • Hi. This may be a brute force approach but try to close the report first and then open it again. Just my 2 cents...
    • Marked as answer by AlefAelol Saturday, January 2, 2016 9:21 PM
    Thursday, December 31, 2015 5:28 PM

All replies

  • Hi. This may be a brute force approach but try to close the report first and then open it again. Just my 2 cents...
    • Marked as answer by AlefAelol Saturday, January 2, 2016 9:21 PM
    Thursday, December 31, 2015 5:28 PM
  • 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

    Friday, January 1, 2016 5:33 PM

  • 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.

    Friday, January 1, 2016 6:01 PM
  • 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 
    Friday, January 1, 2016 7:57 PM

  • 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
    Friday, January 1, 2016 8:00 PM
  • 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


    • Edited by Ken Sheridan Friday, January 1, 2016 8:50 PM Typo corrected.
    Friday, January 1, 2016 8:49 PM
  • 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 .....
    Saturday, January 2, 2016 6:54 PM
  • Hi. I take it my suggestion did not work? Sorry...
    Saturday, January 2, 2016 7:27 PM
  •  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.

    Saturday, January 2, 2016 7:49 PM
  • 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

    Saturday, January 2, 2016 8:34 PM
  • ...

    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!
    Saturday, January 2, 2016 8:44 PM
  • 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...
    Saturday, January 2, 2016 8:48 PM
  •  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.

    Saturday, January 2, 2016 8:55 PM