Help with string across multiple private subs (1 Viewer)

[gojets1721]

Full disclosure...I'm very new to VBA so bear with me.

I have 3 commands on a report that quickly allows the user to export the report to pdf, email the report, or print it. It works great. Here's my exact code:

Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err

    Dim objFileDialog As Object
    Dim strFileName As String

    Set objFileDialog = Application.FileDialog(2)
    strFileName = "Complaint Report" & ".pdf"
   
    With objFileDialog
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & strFileName
        If .Show = -1 Then
            strFileName = objFileDialog.SelectedItems(1)
            If Right(strFileName, 4) <> ".pdf" Then
                strFileName = strFileName & ".pdf"
            End If
           
        DoCmd.OutputTo acOutputReport, Me.Name, acFormatPDF, strFileName

        MsgBox "Report saved to " & strFileName

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

    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbExclamation, "Error Message"

    Resume btnExportReport_Click_Exit
   
End Sub

Private Sub btnEmailReport_Click()
On Error GoTo btnEmailReport_Click_Err

    Dim strReportName As String
    Dim strCriteria As String
    Dim strFileName As String
    Dim strSubject As String
    Dim strMessage As String
   
    strReportName = Me.Name
    strCriteria = ""
    strFileName = "Complaint Report" & ".pdf"
   
    strSubject = "Complaint Report"
    strMessage = "Please see the attached complaint."
   
    Call EmailReport(strReportName, strCriteria, strFileName, strSubject, strMessage)

btnEmailReport_Click_Exit:
    Exit Sub

btnEmailReport_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "Error Message"

    Resume btnEmailReport_Click_Exit
   
End Sub

Private Sub btnPrintReport_Click()
On Error GoTo btnPrintReport_Click_Error
   
    DoCmd.RunCommand acCmdPrint

btnPrintReport_Click_Exit:
    Exit Sub

btnPrintReport_Click_Error:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "Error Message"

    Resume btnPrintReport_Click_Exit

End Sub

The strFileName and strSubject lines are what I need help with. With exporting and emailing, I have to specify a file name, subject line, etc in the VBA. These are always the same name across the board. I was hoping I could just declare this name once and then reference it throughout the VBA. That way, if i want to change it, I don't have to change it in three different places. I'm not sure how to do that though across three private subs

Any suggestions?

 

[NauticalGent]

Give this a read and then come on back if you are still unclear...

VBA - Variables

VBA - Variables - Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable.

www.tutorialspoint.com

Edit: what you are looking for is a Constant Variable in your EmailReport Sub.

 

[gojets1721]

Give this a read and then come on back if you are still unclear...

VBA - Variables

VBA - Variables - Variable is a named memory location used to hold a value that can be changed during the script execution. Following are the basic rules for naming a variable.

www.tutorialspoint.com

Edit: what you are looking for is a Constant Variable in your EmailReport Sub.

Thank you! I was able to get it working by using a private const

One last question...when using the private constant, is there a way to have it refer to a field in the report?

For instance, I'm getting an 'internal error' with this code:

Private Const cstrFileName As String = Me.txtReportName

 

[NauticalGent]

Thank you! I was able to get it working by using a private const

One last question...when using the private constant, is there a way to have it refer to a field in the report?

For instance, I'm getting an 'internal error' with this code:

Private Const cstrFileName As String = Me.txtReportName

Exactly. A const is just that; a constant value that does not change. To do what you are trying to do, you would simply Dim your variable in the proper module and the change it at runtime.

 

[theDBguy]

If the value needs to dynamically change, then you might also try looking into TempVars.

 

[NauticalGent]

then you might also try looking into TempVars.

I was going to mention that as well but decided to cross that bridge if we got there...glad it was you!

 

[Pat Hartman]

A constant is not what you want for this purpose. Constants cannot be changed while code is running. That is why they are called constants. I generally keep a table for values like this. I make a form that the user can open if he needs to make changes. It lets the user be in control and not require a code change if the location changes. Then each procedure would refer to the table that holds the values.

 

[The_Doc_Man]

You have gotten good advice from these others. I'll just toss in my two cents' worth. Anything declared CONST is not something you can change. Which means it absolutely cannot appear to the left of an equals-sign in any executable code. The declaration of a constant is managed by the VBA compiler at compile time and after compilation, that constant is essentially in a read-only segment of memory. The value of a form's control is managed at run time and is in a read/write segment of memory.

Directly reference your control if you wish (rather than referencing a constant) in order to get your desired value. Understand that if you try to use the declared constant as anything BUT a constant, you will be doomed to failure.

 

[gojets1721]

If the value needs to dynamically change, then you might also try looking into TempVars.

Thanks. If I were to use it for 'strFileName' in the two private subs below, how would you suggest that be coded?

Private Sub btnExportReport_Click()
On Error GoTo btnExportReport_Click_Err

    Dim objFileDialog As Object
    Dim strFileName As String

    Set objFileDialog = Application.FileDialog(2)
    strFileName = "Complaint Report" & ".pdf"
 
    With objFileDialog
        .Title = "Save to PDF"
        .InitialFileName = "\Documents\" & strFileName
        If .Show = -1 Then
            strFileName = objFileDialog.SelectedItems(1)
            If Right(strFileName, 4) <> ".pdf" Then
                strFileName = strFileName & ".pdf"
            End If
         
        DoCmd.OutputTo acOutputReport, Me.Name, acFormatPDF, strFileName

        MsgBox "Report saved to " & strFileName

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

    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbExclamation, "Error Message"

    Resume btnExportReport_Click_Exit
 
End Sub

Private Sub btnEmailReport_Click()
On Error GoTo btnEmailReport_Click_Err

    Dim strReportName As String
    Dim strCriteria As String
    Dim strFileName As String
    Dim strSubject As String
    Dim strMessage As String
 
    strReportName = Me.Name
    strCriteria = ""
    strFileName = "Complaint Report" & ".pdf"
 
    strSubject = "Complaint Report"
    strMessage = "Please see the attached complaint."
 
    Call EmailReport(strReportName, strCriteria, strFileName, strSubject, strMessage)

btnEmailReport_Click_Exit:
    Exit Sub

btnEmailReport_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, vbCritical, "Error Message"

    Resume btnEmailReport_Click_Exit
 
End Sub

 

[Pat Hartman]

Another option as apparently someone mentioned is Tempvars. HOWEVER, somehow a value has to be placed in the tempvar using code in the first form that opens in the application. Now you are back to hardcoding the values which will always require a program change and the subsequent distribution of a new FE to all users. Hard coding values like this is just plain wrong unless of course you want to make yourself indispensable.