Problem with Microsoft Sample Code

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I am using Access 2003 and have built a database Switchboard based upon a
Micosoft Access 2000 Sample database. The sample database works ok in Access
2003 but when I use the Switchboard with my application I get an error
message which says "Compile error Method or data member not found" and
Me![SwitchboardID] is highlighted in this line

rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]="
& intBtn

SwitchboardID is a field in the table Switchboard Items

Here is all the code for the relevant part of the function, sorry it's so
long. Can anyone help me here?
Thanks
Tony

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 "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "The Switchboard Manager is not
installed. To install this feature, on the Tools menu, point to Database
Utilities, and then click Switchboard Manager."
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
 
Try changing

Dim dbs As Database
Dim rst As Recordset

to

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

There are two difference models for accessing data: DAO and ADO. While both
of them have a Recordset object in them, the two objects are not exactly the
same. Since you're trying to use a method of the DAO database, it's critical
that Access realize you're using DAO, hence the disambiguation.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tony Williams said:
I am using Access 2003 and have built a database Switchboard based upon a
Micosoft Access 2000 Sample database. The sample database works ok in
Access 2003 but when I use the Switchboard with my application I get an
error message which says "Compile error Method or data member not found"
and Me![SwitchboardID] is highlighted in this line

rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn

SwitchboardID is a field in the table Switchboard Items

Here is all the code for the relevant part of the function, sorry it's so
long. Can anyone help me here?
Thanks
Tony

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 "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "The Switchboard Manager is not
installed. To install this feature, on the Tools menu, point to Database
Utilities, and then click Switchboard Manager."
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
 
Thanks Douglas worked like a dream!
Tony
Douglas J. Steele said:
Try changing

Dim dbs As Database
Dim rst As Recordset

to

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

There are two difference models for accessing data: DAO and ADO. While
both of them have a Recordset object in them, the two objects are not
exactly the same. Since you're trying to use a method of the DAO database,
it's critical that Access realize you're using DAO, hence the
disambiguation.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tony Williams said:
I am using Access 2003 and have built a database Switchboard based upon a
Micosoft Access 2000 Sample database. The sample database works ok in
Access 2003 but when I use the Switchboard with my application I get an
error message which says "Compile error Method or data member not found"
and Me![SwitchboardID] is highlighted in this line

rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND
[ItemNumber]=" & intBtn

SwitchboardID is a field in the table Switchboard Items

Here is all the code for the relevant part of the function, sorry it's so
long. Can anyone help me here?
Thanks
Tony

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 "ACWZMAIN.sbm_Entry"
If (Err <> 0) Then MsgBox "The Switchboard Manager is not
installed. To install this feature, on the Tools menu, point to Database
Utilities, and then click Switchboard Manager."
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
 
Back
Top