Access Report and VBA

[shane01]

Hi Guys,

I have a report that is already created in Microsoft Access. What I need to do is create some VBA code that will using some calculations, produce an SQL statement and then get me a set of data. I then want to use that set of data in the report.

I need to be able to use VBA because the information needs to be calculated to make the query.

Once I have my data set using SQL (i can do this) how do I go about using it in the report.

Shane

[dannymking]

Is this from inside Access??

You can set the record source of the report by opening it is design view, save it and then re-open the report to show or print..

'open the report in design mode but hidden
  DoCmd.OpenReport "ReportName", acViewDesign,,,acHidden
  Reports("ReportName").RecordSource = "Name Of Query or SQL String"
  DoCmd.Close acReport, "ReportName", acSaveYes
  'now open the report in print preview
  DoCmd.OpenReport "ReportName", acViewPreview