Hi

[Niroshana]

hi
I want to change the record source (SQL) of sub report by VBA code when open the main report. Please help me to build the code.

 

[arnelgp]

what do you mean?
you can have a Master/Child Link Fields in your subreport, and only show
related records from the record of Main report.

 

[pbaldy]

From the main report? This type of thing:

Me.SubreportControlName.Report.RecordSource = "SELECT..."

The reference would change depending on where the code is (substitute "Report" for "Form"):

Forms: Refer to Form and Subform properties and controls

 

[The_Doc_Man]

Note that if you change the .Recordsource of a report, if the field names in the source don't match the field names used in building the report, you just broke the report.

 

[Niroshana]

My code is as follows, but there is an error in the code, Please help me to resolve the issue.


Private Sub Command33_Click()


Dim Dbs As DAO.Database
Dim Rst As DAO.Recordset

Set Dbs = CurrentDb
Set Rst = Dbs.OpenRecordset("Select * from [ACTIVE SERVICE ORDERS EXP EMAIL] ")

Rst.MoveFirst

Do While Not Rst.EOF


Report_LETTEREMAIL.LETTERSUB.Report.RecordSource = "Select * ACTSEVEMAILQ WHERE SID=" & Rst!SID

DoCmd.OpenReport "LETTEREMAIL", acViewPreview, , , acWindowNormal

Rst.MoveNext

Loop


End Sub

 

[arnelgp]

you'll end up with Endless Loop there, you are Missing Rst.MoveNext.
it does qualify for Master/Child Link Fields on your subReport.
can you upload your db or share it on your dropbox/onedrive?

 

[Niroshana]

Thanks you for your reply

There is no Master/Child Link Fields. When move the record through the loop, updating only subReport data.

 

[arnelgp]

you add Master/Child Link Fields.
so you do not use Recordset at all.
see this demo and open LETTEREMAIL report in Print Preview.