Run code from switchboard?

[Gasman]

Hi all,

I had a function that backed up my backend DB.
I now wish to backup the front end periodically as I make changes.

I created a test sub to call the function. Last change was to make it a sub. Either works from the test sub.

CreateBackup ("FE")

Code is

Sub CreateBackup(strDBType As String)
    Dim strDBpath As String, ext As String, tmp As String
    Dim strPath As String, strBackupPath As String, strDB As String
    
    'tmp = CurrentDb.Name    'or maybe this should be the name of your BE

    strDBpath = GetAccessBE_PathFilename("tblUser")
    strPath = Left(strDBpath, InStrRev(strDBpath, "\"))
    strBackupPath = strPath & "Backup\"
    
    'Will now backup front and back end database
    If strDBType = "FE" Then
        strDBpath = CurrentDb.Name
    End If
    strDB = Right(strDBpath, Len(strDBpath) - InStrRev(strDBpath, "\"))
    
    With CreateObject("Scripting.FileSystemObject")
        'ext = "." & .GetExtensionName(tmp)
        tmp = strBackupPath & Format(Now(), "yyyymmdd_hhnnss") & "_" & strDB
        .CopyFile strDBpath, tmp
    End With
    MsgBox strDBType & " Database saved as " & tmp
End Sub

Sub test()
CreateBackup ("FE")
End Sub

In the switchboard I have the same call CreateBackup ("FE") and CreateBackup ("BE")

As a function it produced the error Backup.png

So I changed it to a sub and get the second error Backup2.png

Now I know I can create separate subs to call either the function or sub (I'm not fussed which it is) and use these in the switchboard, but is there a way to keep the code to a minimum as I am trying. ?

The switchboard is also shown

TIA

 

[pbaldy]

I don't use switchboards, but to call code like that it would need to be a public function in a standard module. Where is it?

 

[Gasman]

Hi Paul,

The code being called is in a standard module.
It was working fine when I just used CreateBackup in the menu entry.

 

[pbaldy]

Oh, so adding the parameter is what makes it fail? Is that a space between the function and the parentheses? If so, try without it. I'll poke around and make sure it can be run with parameters.

 

[Gasman]

Oh, so adding the parameter is what makes it fail? Is that a space between the function and the parentheses? If so, try without it. I'll poke around and make sure it can be run with parameters.

Hi Paul,

Yes there is a space. I just used what worked in the test sub.
Will try tomorrow when back in work.

 

[pbaldy]

Okay. This would imply you can have arguments, unless it's different when run from a switchboard:

https://msdn.microsoft.com/en-us/library/office/ff834373.aspx

 

[Cronk]

Your switchboard is calling a function as per your last screen shot.

However your code is in a Subroutine. Try changing it to a function by replacing
Sub .../End Sub to Function .../End Function

 

[Gasman]

Your switchboard is calling a function as per your last screen shot.

However your code is in a Subroutine. Try changing it to a function by replacing
Sub .../End Sub to Function .../End Function

Cronk,

It started life as a Function and was working without parameter.
I tried changing it to sub when it did not work with the first error message given.

 

[Galaxiom]

It needs to be a function and the call should not have a space between the function name and the parenthesis.

 

[Gasman]

It needs to be a function and the call should not have a space between the function name and the parenthesis.

Hi Galaxiom,

I've changed the routine back to a Function and removed the space between the function name and parameter, but get the dot or shriek error still.?

This was first thing this morning, before I came back to the forum and saw your message.

 

[JHB]

Could you show from one which work?

 

[Gasman]

Looking at the Switchboard Macro as this is 2007 Access.

The Switchboard item is a Command of 8 and Argument of CreateBackup("FE")

The Switchboard macro shows =[Argument] & "()" as Arguments
Pic attached.

 

[Gasman]

Could you show from one which work?

JHB,

If I remove the parameter it will work, and has worked in the past.

Of course now I have to amend the function for the Optional keyword or remove the parameter altogether, but it is the parameter causing the problem.

Function CreateBackup(Optional strDBType As String)

and added strDBType = "FE" for the test and it works.

I appreciate not a lot of people use macros here, and if need be (which I think is looking likely) I will create two separate subs and call the function with the parameter from those.

 

[Gasman]

OK,

I *think* from the macro code that this cannot be done?
The macro appears to want the argument 'CreateBackup' and appends '()' itself ?

So I will just have to create two functions. A sub produces the automation error (presumably due to the '()' being appended?

Edit:
Have just done this and all now working as it should. learnt something new again.

Thank you for all the posts.