Setting Reports recordsource Crashing db

[John.Woody]

I have a problem with the code below. The sql statements work fine, the line giving me the problem is

Reports!R_RevenueByOp.RecordSource = strSQL

When I open the db and run the code it works perfectly. When I run the code for a 2nd time it crashes the db. It exits in less than 1/2 a second and does not close the .ldb lock file. Any ideas why?

Private Sub Preview_Click()
Dim strSQL As String

If Me.ReportChoice = 1 Then

If IsNull(Me.OperatorID) Then
MsgBox "You must select an operator before you continue.", vbOKOnly + vbExclamation, "Required Entry"
Exit Sub
End If

If IsNull(Me.ShowTop) Or Me.ShowTop = "" Then

strSQL = ...

Else

strSQL = ...

End If

DoCmd.Echo False
DoCmd.OpenReport "R_RevenueByOp", acViewDesign
Reports!R_RevenueByOp.RecordSource = strSQL
DoCmd.Close acReport, "R_RevenueByOp", acSaveYes
DoCmd.Echo True

 

[John.Woody]

Fixed it. I've taken the SQL and used it to create a query, the query being the recordsource for the report. It seems to work fine with the limited data I've got, have to try it when several thousand records are in there.

I'd still be interested to know why you can't change the recordsource to the report more than once though, if anyone knows.

John