Access 2000 to Access 97 conversion gives switchboard problem 5

[diosugna]

When I converted an Access 2000 db to 97 for my Access 97 users, when they accessed the database the switchboard code broke on the following error:

Compile error:
Method or data member not found!

When clicking away the dialogue box the following piece of code is highlighted on ".CurrentProject":

Set con = Application.CurrentProject.Connection

Any ideas. I'm not an VBA programmer whatsoever. If you have a simple solution I'd appreciate it.

-------------------------------------------------------

 

[PaulF]

Take a look at this article on Converting Switchboards from 97 to 2K

http://support.microsoft.com/support/kb/articles/Q208/1/95.ASP

It doesn't mention the error message you got, but it might help.

PaulF

 

[BMeek]

The difference is in the .dll files associated with the project. Once the file is converted to Access 2000, go to the Visual basic editor (Press "Alt + F11&quot then select the "Tools" menu. Select "References" and check to see what files are available for reference. Most likely your file is referring to Microsoft DAO from Access 97. Access 2000 uses Microsoft ADO, so make sure the latest version of "ADO" has a checkmark next to it. In most cases that will allow Access reference to the methods and properties the code is looking for.

Bryan Meek
bmeek@pacbell.net

http://www.handtech.com/meekent

A TC in your corner gives you the personal attention you need to find the right technology solutions for your business.

 

[diosugna]

Thanks for the suggestions. They will be useful, however, my problem is the conversion of MSA2K to MSA97 not the other way round. MSA97 doesn't like the switchboard that I created in MSA2K.

Appreciate your valued input, though.

 

[huzza]

Hi,
As I don't have access to the 2000 version of access I'm not sure where exactly in the switchboard code this line comes as on my access 97 switchboards no reference is made to connections.

However try changing that line to

 Set con = CurrentDb.Connection

as access 97 doesn't recognise the current project object, but it does recognise current database (CurrentDb).
Let me know if this works,
Richard

 

[diosugna]

Huzza, It doesn't work. I believe my problem stems from DAO vs ADO as is suggested by BMEEK. Thanks a million. I'll keep digging!

 

[PaulF]

This is code for a Switchboard made in 97... notice the variables being set for database and recordset ...... 2K uses connections.......also notice the reference to the Wizard...
Application.Run "WZMAIN80.sbm_Entry"

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 &quot;Command not available.&quot;
On Error GoTo 0
' Update the form.
Me.Filter = &quot;[ItemNumber] = 0 AND [Argument] = 'Default' &quot;
Me.Caption = Nz(Me![ItemText], &quot;&quot
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 &quot;Unknown option.&quot;

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 &quot;There was an error executing the command.&quot;, vbCritical
Resume HandleButtonClick_Exit
End If

End Function





HTH
PaulF

 

[zollo9999]

I also have this problem when converting from Access 2002 to Access 97. I agree it seems to be related to the differences in DAO and ADO. Did anyone come up with a fix?

It seems that in A2K2 doesn't properly convert back to A97.
The converted switchboard uses connections which only apply to ADO in A2K2.

Is there a A97 service pack that will fix it?

Could someone point me in the right direction.
Thanks

 

[hibbie0762]

I have converted a couple of my Access 97 dbases to Access 2002 fairly successfully by correcting the missing reference in a dummy module, then saving. Everything seems to work except my predefined reports, which contain concatenated text strings. The syntax (|[datone]| |[dattwo]|) to produce &quot;datone dattwo&quot; seems to have become ([datone] & &quot; &quot; & [dattwo]). The vertical bars have gone and the fields need to be linked by ampersands and spaces as shown.

Is there any routine to fix this automatically or do I have to do each report manually? All comments/advice welcome.

 

[AXSNut]

Hey folks, this is my first entry in this forum...I have the same problem in reverse. I created a database at work; take it home where I have Access 2002. Everything converts fine except the Switchboard which has an error in the following section of code..."Dim dbs As Database" is the line highlighted. Any ideas?...Please note, I am not experienced in code...I took one VB class.

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim dbs As Database (this line is highlighted)
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If

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

 

[AXSNut]

Hey folks, this is my first entry in this forum...I have the same problem in reverse. I create a database at work; take it home where I have Access 2002. Everything converts fine except the Switchboard which has an error in the following section of code..."Dim dbs As Database" is the line highlighted. Any ideas?...Please note, I am not experienced in code...I took one VB class.

Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.
Const conNumButtons = 8

Dim dbs As Database (this line is highlighted)
Dim rst As Recordset
Dim strSQL As String
Dim intOption As Integer

' Set the focus to the first button on the form,
' and then hide all of the buttons on the form
' but the first. You can't hide the field with the focus.
Me![Option1].SetFocus
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Next intOption

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set dbs = CurrentDb()
strSQL = "SELECT * FROM [Switchboard Items]"
strSQL = strSQL & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
strSQL = strSQL & " ORDER BY [ItemNumber];"
Set rst = dbs.OpenRecordset(strSQL)

' If there are no options for this Switchboard Page,
' display a message. Otherwise, fill the page with the items.
If (rst.EOF) Then
Me![OptionLabel1].Caption = "There are no items for this switchboard page"
Else
While (Not (rst.EOF))
Me("Option" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
rst.MoveNext
Wend
End If

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

 

[PHV]

You have to add the reference to Microsoft DAO 3.x library:
menu Tools -> REferences ...
And replace this:
Dim rst As Recordset
By this:
Dim rst As DAO.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[AXSNut]

Wow Thanks! I am now able to see the Switchboard...one problem however...when I click on any switchboard button, I get a compile error in the HandleButtonClick function:

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

**********************************************************
On the last line, the following is highlighted, "![SwitchboardID]"

Thanks in advance, your continued help is appreciated.

 

[PaulF]

replace

Dim dbs As Database

with

Dim dbs As DAO.Database

PaulF

 

[AXSNut]

Hi Sorry its taken so long to get back to this...
I did what you suggested, however, still the same problem...

In the last line of my previous message:

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


"![SwitchboardID]" is highlighted.

And the Error Dialogue box says,
"Compile error: Method or data member not found"

Thanks for your help.