Switchboard Error (Access 2007 / 2000)

Topic

New Reply

I have an Access 2000 database with a switchboard menu, which all works fine.
However, if I open the database in Access 2007, I get the following error “There was an error executing the command”.
The problem seems to lies in the handle buttob code.
Can anyone offer an explanation as to why this no longer works?

Private Function HandleButtonClick(intBtn As Integer)
‘ This function is called when a button is clicked.
‘ intBtn indicates which button was clicked.

‘ Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
Const conCmdOpenPage = 9

‘ An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim con As Object
Dim rs As Object
Dim stSql As String

On Error GoTo HandleButtonClick_Err

‘ Find the item in the Switchboard Items table
‘ that corresponds to the button that was clicked.
Set con = Application.CurrentProject.Connection
Set rs = CreateObject(“ADODB.Recordset”)
stSql = “SELECT * FROM [Switchboard Items] ”
stSql = stSql & “WHERE [SwitchboardID]=” & Me![SwitchboardID] & ” AND [ItemNumber]=” & intBtn
rs.Open stSql, con, 1 ‘ 1 = adOpenKeyset

‘ If no item matches, report the error and exit the function.
If (rs.EOF) Then
MsgBox “There was an error reading the Switchboard Items table.”
rs.Close
Set rs = Nothing
Set con = Nothing
Exit Function
End If

Select Case rs![Command]

‘ Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = “[ItemNumber] = 0 AND [SwitchboardID]=” & rs![Argument]

‘ Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rs![Argument], , , , acAdd

‘ Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rs![Argument]

‘ Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rs![Argument], acPreview

‘ Customize the Switchboard.
Case conCmdCustomizeSwitchboard
‘ Handle the case where the Switchboard Manager
‘ is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run “ACWZMAIN.sbm_Entry”
If (Err 0) Then MsgBox “Command not available.”
On Error GoTo 0
‘ Update the form.
Me.Filter = “[ItemNumber] = 0 AND [Argument] = ‘Default’ ”
Me.Caption = Nz(Me![ItemText], “”)
FillOptions

‘ Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase

‘ Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rs![Argument]

‘ Run code.
Case conCmdRunCode
Application.Run rs![Argument]

‘ Open a Data Access Page
Case conCmdOpenPage
DoCmd.OpenDataAccessPage rs![Argument]

‘ Any other command is unrecognized.
Case Else
MsgBox “Unknown option.”

End Select

‘ Close the recordset and the database.
rs.Close

HandleButtonClick_Exit:
On Error Resume Next
Set rs = Nothing
Set con = Nothing
Exit Function

HandleButtonClick_Err:
‘ If the action was cancelled by the user for
‘ some reason, don’t display an error message.
‘ Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox “There was an error executing the command.”, vbCritical
Resume HandleButtonClick_Exit
End If

End Function

Many thanks

Robert

Reply | Quote

Replies

Temporarily comment out the line

On Error GoTo HandleButtonClick_Err

by inserting an apostrophe ‘ at the beginning of that line. Now try again.
When the error occurs, what does the error message say?
Click the Debug button in the error message dialog. Which line is highlighted?

Reply | Quote

Hello Hans

The error message says :-

“The expression On Click you entered as the event propertysetting produced the following error:
Syntax error in PARAMETER clause.
*The expression may not result in the name of a macro, the name of a user defined function, or [Event Procedure]
* There may have been an error evaluating the function, event or macro”

There is no debug button. Only the OK button and a “Show help” button which gives the following message:-.

“This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired”.

Regards

Robert

Reply | Quote

That type of error is difficult to troubleshoot. Since I don’t have Access 2007, I’m afraid I can’t help. I hope that someone else will reply.

Reply | Quote

Thanks for your help, Hans

I did look in Tools > References to check if everything was the same, there.
Access 2007 references the Access Object 12.0 Library and Access 2000 references the Object 9.0 Library. Other than that, the references are the same. I couldn’t find the Object 9.0 library in the Access 2007 list of available references. I was hoping that referencing that as well might solve the problem.

Regards

Robert

Reply | Quote

Have you tried compiling the code? It should fail when it finds something it doesn’t like. Also, there are a whole bunch of built in functions that 2007 does not like. For example you can no longer run compact on close through VBA in 2007. I had to alter the code for a database built in 2000 when a client upgraded to 2007. It seems to me there was a link posted in the last week or so to a complete list of functions that 2007 doesn’t allow anymore.

Reply | Quote

You probably mean Functions and properties in Access 2007 blocked by sandbox mode, mentioned in post 667,892.

Added later: please note that Sandbox Mode only applies to the use of functions and properties in expressions. You can use all functions and properties in VBA code.

Reply | Quote

One thing you can try is Tools | References in the Visual Basic Editor. Does any of the references whose check box is ticked (those at the top of the list) begin with MISSING?

Reply | Quote