Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Switchboard Error (Access 2007 / 2000)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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?

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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?

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Switchboard Error (Access 2007 / 2000)

    You probably mean Functions and properties in Access 2007 blocked by sandbox mode, mentioned in <post#=667,892>post 667,892</post#>.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •