Sometimes the report exists, sometimes it doesn't

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

I am using the following line of code:

Set Rpt = Reports!rptDailyIPTMtg

to set the Report variable to a report that definitely exists. But the code
doesn't consistently find the report. I get runtime error 2451: "The report
name 'rptDailyIPTMtg' you entered is misspelled or refers to a report that
isn't open or doesn't exist".

I am trying to provide a "query-by-form" kind of solution to run a report in
3 different modes based on a static complete date entered in a popup form.
IPT is a "responsible party" (no, that doesn't refer to a party where no
alcohol is served).
In Case 1 all IPTs are included on 1 report. In Case 2 only 1 IPT is included
on the report. In Case 3 individual reports are printed for each IPT.
I got a good result for Case 1 the first couple of times I tested it, but now
I get this error msg.

The complete code follows:

Private Sub cmdRunRpts_Click()
Dim strSQL As String
Dim strSQL1 As String
Dim strIPTs As String
Dim rsIPTs As DAO.Recordset
Dim db As DAO.Database
Dim Rpt As Report
Dim recDt As Date
Dim iRpt As Integer '1=All IPTs, 2=One IPT, 3=Each IPT
Dim i As Integer

If isRptOpen("rptDailyIPTMtg") Then DoCmd.Close acReport, "rptDailyIPTMtg"

Set Rpt = Reports!rptDailyIPTMtg

With Me
recDt = .txtDate
iRpt = .grpIPTRpt
End With

strSQL1 = "SELECT *" & vbCrLf
strSQL1 = strSQL1 & "FROM qryIPTRpt" & vbCrLf
strSQL1 = strSQL1 & "WHERE [Week-end Date]<=#" & recDt & "#"

Select Case iRpt
Case 1
strSQL = strSQL1 & vbCrLf & "ORDER BY Val([IPT_NO])"
Rpt.RecordSource = strSQL
DoCmd.OpenReport "rptDailyIPTMtg", acViewNormal
Case 2
strSQL = strSQL1 & " AND Val([IPT_NO]) Like " & Me.cmbIPT & vbCrLf
strSQL = strSQL & "ORDER BY Val([IPT_NO])"
Rpt.RecordSource = strSQL
DoCmd.OpenReport "rptDailyIPTMtg", acViewNormal
Case 3
strIPTs = "SELECT IPT FROM tblIPTs WHERE sectid>1 ORDER BY IPT"
Set db = CurrentDb
Set rsIPTs = db.OpenRecordset(strIPTs)
strIPTs = ""
With rsIPTs
For i = 1 To .RecordCount
strSQL = strSQL1 & " AND Val([IPT_NO]) Like " & .Fields("IPT")
& vbCrLf
strSQL = strSQL & "ORDER BY Val([IPT_NO])"
Rpt.RecordSource = strSQL
Rpt.Controls("txtIPT") = .Fields("IPT")
Rpt.Print
.MoveNext
Next i
.Close
End With
Set rsIPTs = Nothing
End Select

Set Rpt = Nothing

End Sub
 
I am using the following line of code:
Click to expand...

Same answer applies as to your later post of similar question. The Reports
Collection only contains Open Reports.

Larry Linson
Microsoft Access MVP
 
Larry,

Thanks for your response. I've been trying for days to solve this so I've
submitted multiple posts trying to recast the question.
In light of your response then, how would one go about establishing a report
object to manipulate in code? Should I first open the report, perhaps in
design view, and then set the variable to it? Or should I conclude that even
though a report object is available in code, it is impractical to use it? Any
insight as to why the report object is so limited?
 
Back
Top