problem with switchboard and run code

[alant37]

Im new to VBA and Im trying to run the following module called quit from the standard switchboard:
Public Function quit()
On Error GoTo quit_Err
DoCmd.quit acSave
quit_Exit:
Exit Function
quit_Err:
MsgBox Error$
Resume quit_Exit
End Function

In the standard switchboard I am using the Command: Run Code and a Function name: quit. However, I get the error message 'There was an error executing the command'. Is there anyway of using the standard switchboard to achieve this?

 

[Uncle Gizmo]

You're problem is probably caused by the use of "quit" as your function name. It is not a good idea to use "real words" for naming anything you use in programming because they can clash with reserved words.

for example if you simply change the word the name of your function from "Quit" to fQuit" I believe that will solve your problem.

 

[Uncle Gizmo]

BTW, Calling functions from the switchboard is a very powerful way of using this built in feature. I use the switchboard all the time. I have seen many derogatory comments about the switchboard, however with the use of functions it is very powerful. The only limitation is you cannot pass variables through your functions, so in effect it only works with "Subs" (subroutines) which incidently is what your function is.

Functions invariably pass variables, and can return variables whereas subroutines just carry out a routine.

I have one module called "basSwitchboard" where I keep all the code I call from the switchboard. This is an example of how I use it to open a particular form.

'Public Sub fMainQuoSeeAll()

'DoCmd.OpenForm "frmMainQuote", acNormal, , , acFormReadOnly

'With Form_frmMainQuote
    '.Visible = False
    '.cboPitCode.RowSource = "" & _
    "SELECT tlkpPitList.PitListID, tlkpPitList.PitListDesc, tblSupplier.SupplierDesc " & _
    "FROM tblSupplier INNER JOIN tlkpPitList " & _
    "ON tblSupplier.SupplierID = tlkpPitList.PitListSupplier " & _
    "ORDER BY tlkpPitList.PitListDesc"
    '.cmdGetTown.Visible = False
    '.AllowFilters = True
    '.DataEntry = False
    '.NavigationButtons = True
    '.OrderBy = "QuoteID DESC"
    '.OrderByOn = True
    '.Visible = True
'End With
'End Sub

TIP ::::::::::::::::::::::::::::::::::::::::::::::::
When entering information into the switchboard wizard, it is sometimes difficult to remember the particular function you want to call, what I do is enter some dummy text for example "XXXXX" where the switchboard wizard is expecting the function name, then I opened the switchboard table and cut and paste the function (sorry! Sub) name into where the "XXXXX" marker is.

 

[Rich]

Why the acSave?

 

[ghudson]

My guess he is confusing the acSave option with the notion it is saving the current record when in fact the acSave option saves any unsaved "object" changes, not "record" changes.

Public Sub CloseDatabase()
On Error GoTo CloseDatabase_Err
    
    Application.Quit acQuitSaveNone
    
CloseDatabase_Exit:
    Exit Sub
    
CloseDatabase_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume CloseDatabase_Exit
    
End Sub

 

[modest]

Why are you making a Quit function when Access already has built in calls?

 

[Bat17]

The only limitation is you cannot pass variables through your functions

With a minor tweak to the switchboad code it can be persuaded to take variables

if you change :-
' Run code.
Case conCmdRunCode
Application.Run rst![Argument]

To :-

' Run code.
Case conCmdRunCode
Eval (rst![Argument])

Then it should run the arguments OK, bear in mind that you will now have to call all code using brackets!
MyFunc() not MyFunc

HTH

Peter

 

[Uncle Gizmo]

Thanks Peter, I will give that a try at the first opportunity!

 

[alant37]

Thanks everyone, its working now as the following subroutine:
Public Sub fQuit()
On Error GoTo fQuit_Err

Application.quit acQuitSaveNone

fQuit_Exit:
Exit Sub

fQuit_Err:
MsgBox Error$
Resume fQuit_Exit

End Sub

However, I was still getting the error message until I changed the module name from fquit to converted-fquit. The code that I first produced was from converting a macro, but after reading your posts I have changed it to the code above.

 

[Bat17]

Modules should never have the same name as a function/sub

 

[ghudson]

Thanks everyone, its working now as the following subroutine:
Public Sub fQuit()
On Error GoTo fQuit_Err

Application.quit acQuitSaveNone

fQuit_Exit:
Exit Sub

fQuit_Err:
MsgBox Error$
Resume fQuit_Exit

End Sub

However, I was still getting the error message until I changed the module name from fquit to converted-fquit. The code that I first produced was from converting a macro, but after reading your posts I have changed it to the code above.

You should not have a module for every function. Don't put all your eggs [public functions/subs] in one basket but do not create a module for every public function/sub. But Bat17 is correct that you can not name a module and a function/sub with the same name. Why did you name the sub fQuit? What does the f stand for? Also, your error message is lacking the runtime error number for which is very helpful to debugging and trapping for specific errors. In my biased opinion this is a better way to code your shutdown sub...

Public Sub CloseDatabase()
On Error GoTo CloseDatabase_Err
    
    Application.Quit acQuitSaveNone
    
CloseDatabase_Exit:
    Exit Sub
    
CloseDatabase_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume CloseDatabase_Exit
    
End Sub

 

[Uncle Gizmo]

Why did you name the sub fQuit?

I suggested this, as I assumed that there was a reserverd word clash.. As it happened, it was a module function name clash.

In any event it is a good idea to follow a naming convention, and I allways prefix functions (and Subs) with an f.

This is a typical example of where this pays off, as my sub would have named fQuit and my module basQuit, or more likely basSwitchboard....

 

[Uncle Gizmo]

Eval (rst![Argument]) didn't work?

Hi Peter,
I just got Around to trying this and I could not get it to work.

' Run code.
Case conCmdRunCode
Application.Run rst![Argument]

To :-

' Run code.
Case conCmdRunCode
Eval (rst![Argument])

I tried:

' Run code.
Case conCmdRunCode
Eval (rs![Argument])
'.......... I'm using A2002 in A2000 mode so it has "rs" instead of "rst"

I also tried:

' Run code.
Case conCmdRunCode
Application.Run Eval rs![Argument]

I've got a Suspicion that there is something different about "Eval" in later versions of MS Access?

cheers Tony.

 

[Uncle Gizmo]

Change Sub from a Sub to a Function!

It does work!

I forgot to change my sub from a sub to a function! It don't work with a sub!

Cheers Tony...

 

[Uncle Gizmo]

Change your Subs into Functions.

For clarity I thought I should explain the problem I had in detail.

The switchboard will run code, in the form of a function or a subroutine (sub) however you cannot pass arguments through your functions.

However Peter describes a method of allowing you to pass an argument through your function using the Eval Function.

I changed my switchboard code as suggested by Peter, however it would not work I kept getting an error message. Some of my other code called by the switchboard stopped working as well.

I discovered it was because I was calling subroutines (subs) the subs work fine when called from the switchboard, however once you carry out the changes as described by Peter, you have to change your subroutines into functions. This is quite simple process just rename them "Function" instead of "Sub"

cheers Tony.