Trying to suppress MsgBox from other subroutine - creates error

[Scaniafan]

Hello all,

I've got five subroutines which can be run separately, and include a message box when an import is completed.

I also have one button which calls the five subroutines, to import all data at once. However, when I run this, I get the message box from each subroutine, which holds the total import progress.

I've found below statement on http://stackoverflow.com/questions/13635599/suppress-msgbox-from-another-subroutine-in-vba, however it immediately gives an error on the red marked line.

If ShowMessages = True Then 'The = True part is important here - see below.
        [COLOR="Red"]ShowMessages is a Variant type[/COLOR]
    'The original statement that calls the msgBox
    MsgBox "CP201 Import completed", vbInformation, "Import completed"
    End If

The error is "Compile Error: Expected: Expression"

I'm lacking experience to solve this. Can someone please help me with this? Thanking in advance.

 

[arnelgp]

just comment this:

'ShowMessages is a Variant type

 

[Scaniafan]

When I do this, I don't get the compile error anymore, but when I try to run it (both seperately and in the "import all"), I get below error.

The import sub looks as below:

Public Sub Import_CP201_Click(Optional ShowMessages = True)
    DoCmd.SetWarnings False

    'Identify newest CP201 file
        Dim strFileFound As String
        Dim dateFileDate As Date
        Dim dateNewestDate As Date
        Dim strNewestFile As String
        Const strcNameRoot As String = "SAP"        ' part of the file name that doesn't change
        Const strcExtension As String = "csv"          ' extension for the file name
        strFileFound = Dir(CurrentProject.Path & "\Import\CP201\" & strcNameRoot & "*." & strcExtension)
        Do While strFileFound <> ""
          dateFileDate = GetLastUpdate(CurrentProject.Path & "\Import\CP201\" & strFileFound)
          If dateFileDate > dateNewestDate Then
            dateNewestDate = dateFileDate
            strNewestFile = strFileFound
          End If
          strFileFound = Dir
        Loop
        Debug.Print "Newest CP201 file is: " & strNewestFile
    
    'Import newest CP201 file
        DoCmd.TransferText acImportDelim, "CP201_Import_Spec", "TBL_CP201_Import", CurrentProject.Path & "\Import\CP201\" & strNewestFile, True

    'Append new CP201 to TBL_CP201_Master
        DoCmd.OpenQuery "QRY_CP201_Append_1"
        DoCmd.OpenQuery "QRY_CP201_Append_2"
        DoCmd.OpenQuery "QRY_CP201_Append_3"
   
    'Update existing shipments with data from import file
        DoCmd.OpenQuery "QRY_CP201_Update_1"
    
    'Delete table TBL_CP_203_Import
        DoCmd.DeleteObject acTable, "TBL_CP201_Import"
        DoCmd.DeleteObject acTable, "TBL_CP201_Delete"

    'Delete old import errors tables
        Dim tblDef As TableDef
    
        For Each tblDef In CurrentDb.TableDefs
            If InStr(1, tblDef.Name, "ImportErrors") > 0 Then
                DoCmd.SelectObject acTable, tblDef.Name, True
                DoCmd.DeleteObject acTable, tblDef.Name
            End If
        Next tblDef
                     
    DoCmd.SetWarnings True
    
    If ShowMessages = True Then 'The = True part is important here - see below.
'showMessages is a Variant type
    'The original statement that calls the msgBox
    MsgBox "CP201 Import completed", vbInformation, "Import completed"
End If

The Sub that calls the query looks like:

Public Sub Import_All_Click()

    Call Import_CP201_Click(False)
    Call Import_CP203_Click
    Call Import_CP203AR_Click
    Call Import_CP203IV_Click
    Call Import_CP203RD_Click
    Call Import_CP207_Click
   
    MsgBox "All imports completed", vbInformation, "Import completed"

End Sub

I've tried putting the statement in all subs to see if this would solve something, but no luck here

 

[arnelgp]

can you change this:

Public Sub Import_CP201_Click(Optional ShowMessages = True)

to:

Public Sub Import_CP201_Click(Optional ShowMessages As Boolean = True)

 

[Scaniafan]

Still provides the same error message unfortunately

 

[arnelgp]

put a breakpoint on Public Sub Import_CP201_Click. run your form. click the import button. press F8 to step through code. note where the error occurs.

 

[Scaniafan]

I've added the breakpoint at the line you mentioned, but it immediately gives the mentioned error, without even reaching the breakpoint.

I've added the breakpoint to the Call Import_CP201_Click(False) and the Public Sub Import_All_Click(), but in both instances, it gives the error immediately.


When I use "Debug" > "Compile" I get the following error:

Compile Error: Procedure declaration does not match description of event or procedure having the same name

It marks this part of the code:

Public Sub Import_CP201_Click(Optional ShowMessages As Boolean

leaving the

= True)

out of the selection.

 

[arnelgp]

ah, thats a nono, access is evaluating your public sub as a Click event. you should rename your sub to another name, ie, public sub subImport_CP201(Optional ShowMessages as Boolean).

if you have a button, on click event of that button:

Public Sub yourButton_Click()
Call subImport_CP201
End Sub

 

[Scaniafan]

Ah, ok. Learning every day here!

It works like a charm now, thank you very much for your support!