I have an MS Access report in which I go thru a recordset to change the Record Source of the report. I wish to export the report as SNP files as it goes thru the recordset. I have tried doing using a button on a form.

At the moment it comes up with an error saying it doesn't recognise the name of the report. How do I export the report once this is solved?

Any help would be appreciated.

Private Sub cmdExportReports_Click()

Dim strSQL As String
Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblVarianceRanges", dbOpenDynaset)

rst.MoveFirst

Do While Not rst.EOF
With rst
strSQL = "SELECT qAzizVarianceComensura3.[Candidate ID Number], "
strSQL = strSQL & "qAzizVarianceComensura3.Name, "
strSQL = strSQL & "qAzizVarianceComensura3.[Week Ending], "
strSQL = strSQL & "qAzizVarianceComensura3.[Tempest Gross], "
strSQL = strSQL & "qAzizVarianceComensura3.[Client Gross], "
strSQL = strSQL & "qAzizVarianceComensura3.Variance, "
strSQL = strSQL & "qAzizVarianceComensura3.AbsValue "
strSQL = strSQL & "FROM qAzizVarianceComensura3 "
strSQL = strSQL & "WHERE (((qAzizVarianceComensura3.Variance) " & !VarianceRange & ")) "
strSQL = strSQL & "ORDER BY qAzizVarianceComensura3.Variance;"
Reports!rptVarianceComensura.RecordSource = strSQL 'Error 2451 here.
DoCmd.OpenReport "rptVarianceComensura", acViewPreview
.MoveNext
End With
Loop

End Sub

Here's code that should work. Might not if your db is secured since it opens the report in design view to change the recordsource. Looks like you'll also need to modify the comparison in the where clause, since to return a range of values I'd expect to see something like ...
"WHERE vnc > " & lowerBound & " AND vnc <= " & upperBound & " "
Private Sub cmdExportReports_Click()
Const conSQL As String = _
"SELECT [Candidate ID Number], [Name], [Week Ending], " & _
"[Tempest Gross], [Client Gross], Variance, AbsValue " & _
"FROM qAzizVarianceComensura3 " & _
"WHERE Variance = **VR** " & _
"ORDER BY Variance;"
Const conRpt As String = "rptVarianceComensura"
Dim rst As DAO.Recordset
Dim oldSQL As String
Dim filespec As String

Set rst = CurrentDb.OpenRecordset("tblVarianceRanges", dbOpenDynaset)
With rst
Do While Not .EOF
'open the report in design view, hidden
DoCmd.OpenReport conRpt, acViewDesign, , , acHidden
'if this is the first item in the loop, save the old record source
'so we can replace it at the end
If .AbsolutePosition = 0 Then oldSQL = Reports(conRpt).RecordSource
'change its record source
Reports(conRpt).RecordSource = Replace(conSQL, "**VR**", !VarianceRange)
'save the modified report
DoCmd.Close acReport, conRpt, acSaveYes
'build the filespec for the snapshot
filespec = CurrentProject.Path & "\Variance " & .AbsolutePosition & " (" & !VarianceRange & ")" & Format(Date, "yymmdd") & ".snp"
'and output it as a snapshot
DoCmd.OutputTo acOutputReport, conRpt, acFormatSNP, filespec
'and get the next !VarianceRange
.MoveNext
Loop
End With

'finally, replace the old r-source of the report
DoCmd.OpenReport conRpt, acViewDesign, , , acHidden
Reports(conRpt).RecordSource = oldSQL
DoCmd.Close acReport, conRpt, acSaveYes

End Sub

Problems with what you posted are: You attempt to assign a string to the record source of a report that is not yet open. You have no comparison operators in the where clause of your SQL.

I didn't realise I could open the report in DESIGN view. Many thanks.

You're welcome