none
How to Disable Message Box when Calling a Procedure in Another Sub RRS feed

  • Question

  • Hello,

    I have a click event that runs an sql command based on a value selected in a combo box to create individual product reports:

    Private Sub RepInd_Click()
    
    ''Functionality to create individual product reports when selecting the product from a drop down menu
    
    DoCmd.SetWarnings False
    
    ''Creates ORE Report
    
    If Me.RepIndividual.Value = "ORE" Then
    
    Dim sqlorerep As String
    
    sqlorerep = "SELECT DISTINCT tbl_ore.[Enrtry ID] AS Article_ID, tbl_ore.Product AS Online_Product, tbl_ore.[Handover date] AS Date_of_Handover, tbl_ore.[Live On] AS Online_Publication_Date, [tbl_ore].[Live on]-[tbl_ore].[Handover date] AS Actual_TAT, tbl_tat.Agreed_TAT, IIf(Nz([tbl_ore].[Handover date],0)=0,'missing handover date info',[tbl_tat].[Agreed_TAT]-([tbl_ore].[Live on]-[tbl_ore].[Handover date])) AS Actual_vs_Agreed, tbl_ore.Type INTO tbl_ore_tat_report " & _
    "FROM tbl_ore INNER JOIN tbl_tat ON tbl_ore.Product = tbl_tat.Product " & _
    "WHERE (((tbl_ore.Type)='Article'));"
    
    DoCmd.RunSQL sqlorerep
    
    MsgBox "ORE Report Created Successfully"
    
    Else

    I then have another click event that allows the user to create a report of all products. Rather than copying every sql command, I just called each one into the new sub as follows:

    Private Sub OverallRep_Click()
    
    DoCmd.SetWarnings False
    
    ''Deletes previous overall report
    
    Dim Msg, Style, Title, Response, MyString
    Msg = "Are you sure you want to continue? Peforming this action will delete previous report"
    Style = vbYesNo + vbCritical + vbDefaultButton2
    Title = "Create New Overall Products Report"
    Response = MsgBox(Msg, Style, Title)
    If Response = vbYes Then    ' User chose Yes.
        MyString = "Yes"    ' Perform some action.
        
        Dim sqldel As String
        
        sqldel = "DELETE tbl_overall_report.* " & _
                    "FROM tbl_overall_report;"
    
    DoCmd.RunSQL sqldel
    
    Else    ' User chose No.
        MyString = "No"    ' Perform some action.
        
        MsgBox "No changes were made to existing report"
        
    End If
    
    DoCmd.SetWarnings True
    
    ''----------Builds overall products report----------
    
    ''--VSI--
    
    ''Creates individual VSI Report
    
    Me.RepIndividual.Value = "ORE"
    Call RepInd_Click
    

    What I can't do is stop the message box appearing for each product in the second sub.

    Is there a way for me to call this first sub but not have to have all of the message boxes pop up?


    Wednesday, October 24, 2018 10:57 AM

Answers

  • Hi,

    One workaround is to set a module level variable that you can check whether to show or not the message box. For example:

    Dim bool As Boolean
    
    Private Sub RepInd_Click()
    ...
    DoCmd.RunSQL sqlorerep
    
    If bool=False Then
        MsgBox "ORE Report Created Succcessfully"
    End If
    ...
    End Sub
    
    Private Sub OverallRep_Click()
    ...
    bool = True
    Call RepInd_Click
    ...
    End Sub

    Hope it helps...

    • Marked as answer by LD2015 Thursday, October 25, 2018 9:23 AM
    Wednesday, October 24, 2018 3:50 PM

All replies

  • Hi,

    One workaround is to set a module level variable that you can check whether to show or not the message box. For example:

    Dim bool As Boolean
    
    Private Sub RepInd_Click()
    ...
    DoCmd.RunSQL sqlorerep
    
    If bool=False Then
        MsgBox "ORE Report Created Succcessfully"
    End If
    ...
    End Sub
    
    Private Sub OverallRep_Click()
    ...
    bool = True
    Call RepInd_Click
    ...
    End Sub

    Hope it helps...

    • Marked as answer by LD2015 Thursday, October 25, 2018 9:23 AM
    Wednesday, October 24, 2018 3:50 PM
  • This works perfectly! Thanks .theDBguy!
    Thursday, October 25, 2018 9:23 AM
  • Hi,

    You're welcome. Glad we could assist. Good luck with your project.

    Thursday, October 25, 2018 3:52 PM