VBA Code - 97 to 2003

[MrTBC]

Hi All,
I've inherited an Access 97 database that I need to convert to 2003, problem is that it keeps crashing when I try to use the switchboard. I think it's a problem with this code but I'm not sure where to start.
Any ideas please? Many thanks.


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

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

Dim dbs As Database
Dim rst As Recordset

On Error GoTo HandleButtonClick_Err

' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn

' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If

Select Case rst![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]

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

' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]

' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![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 "WZMAIN80.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 rst![Argument]

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

' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

End Select

' Close the recordset and the database.
rst.Close
dbs.Close

HandleButtonClick_Exit:
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

 

[RoyVidar]

For DAO objects:
1 - ensure there's a reference to Microsoft DAO... library (in VBE - Tools | References - though it should be there by default in 2003)
2 - do explicit declaration

[tt] Dim dbs As dao.Database
Dim rst As dao.Recordset[/tt]

But if it's crashing (not erroring, where one would have expected you to provide error number and error mesage), then that seems more like corruption, try /decompile Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times

Roy-Vidar

 

[yetanotherjo]

I've got the same problem I think. The DAO reference needs to be moved up the list so it can open the recordsets referenced in the module behind the switchboard.

 

[MrTBC]

I'm actually trying to convert the code manually but it doesn't seem to like:

Dim rst As New ADODB.Recordset

 

[RoyVidar]

No - in my humble opinion etc... don't rely on the order of the references, that will bomb some time or other, take the xtra time and type those three letters and a dot to disambiguate the references.

For ADO, ensure you have a reference to ADO, and I'll do my preferred explicit declaration/instantiation too

[tt]Dim rst As ADODB.Recordset
set rst = New ADODB.Recordset[/tt]

Roy-Vidar