Suggestions on fixing error when switching to Option Explicit (1 Viewer)

[gojets1721]

Probably a very stupid question, but I have an export command on a report and I changed it to Option Explicit.

When doing so it's saying the bolded 'currentreport' below is not defined. I'm not sure what to define it as. That line identifies the open report's name.

Any suggestions?

Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err

    Dim fd As Object
    Dim FileName As String
    Dim ReportName As String
    
    ReportName = Reports(currentreport).Name

    Set fd = Application.FileDialog(2)
    FileName = Complaint History" & " " & Format(Date, "mm.dd.yyyy") & ".pdf"
    
    With fd
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & FileName
        If .Show = -1 Then
            FileName = fd.selecteditems(1)
            If Right(FileName, 4) <> ".pdf" Then
                FileName = FileName & ".pdf"
            End If
            
        DoCmd.OutputTo acOutputReport, ReportName, acFormatPDF, FileName

        MsgBox "Report saved to " & FileName

        End If
    
    End With
    
btnExportReport_Click_Exit:
    Set fd = Nothing
    Exit Sub
    
btnExportReport_Click_Err:

      MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnExportReport_Click_Exit
    
End Sub

 

[Minty]

I'm pretty sure that's not a valid property. Try the following:

Dim rptCurrentReport As Report
Set rptCurrentReport = Screen.ActiveReport

From

Screen.ActiveReport property (Access)

Office VBA reference topic

learn.microsoft.com

 

[gojets1721]

Thank you! That worked. I inherited this code so I'm still getting used to it. I appreciate it!

 

[gojets1721]

I'm pretty sure that's not a valid property. Try the following:

Dim rptCurrentReport As Report
Set rptCurrentReport = Screen.ActiveReport

From

Screen.ActiveReport property (Access)

Office VBA reference topic

learn.microsoft.com

@Minty strike that...so I added in your revision (see below) and now I'm getting an 2498 error on DoCmd.OutputTo line. Any suggestions?

Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err

    Dim fd As Object
    Dim FileName As String
    Dim rptCurrentReport As Report
    
    Set rptCurrentReport = Screen.ActiveReport
    
    Set fd = Application.FileDialog(2)
    FileName = "Complaint History" & " " & Format(Date, "mm.dd.yyyy") & ".pdf"
    
    With fd
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & FileName
        If .Show = -1 Then
            FileName = fd.SelectedItems(1)
            If Right(FileName, 4) <> ".pdf" Then
                FileName = FileName & ".pdf"
            End If
            
        DoCmd.OutputTo acOutputReport, rptCurrentReport, acFormatPDF, FileName

        MsgBox "Report saved to " & FileName

        End If
    
    End With
    
btnExportReport_Click_Exit:
    Set fd = Nothing
    Exit Sub
    
btnExportReport_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnExportReport_Click_Exit
    
End Sub

 

[Minty]

Ah sorry, I missed the context of where it was being used.
Change it to

Dim rptCurrentReport As String

 Set rptCurrentReport = Screen.ActiveReport.Name

The .Name property returns the report name as a string.
Screen.ActiveReport will return an object.

 

[Pat Hartman]

If the code is running in the report, why not just use Me.Name?

DoCmd.OutputTo acOutputReport, Me.Name, acFormatPDF, FileName

 

[gemma-the-husky]

Just to confirm. Setting option explicit means you need to declare all variables before you use them. If you don't put option explicit, VBA will create variables automatically. However if a variable is created by VBA your database may not work as you expect, because the variable probably doesn't hold the correct value, and may be of the wrong type.