×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access 2000 to Access 97 conversion gives switchboard problem
5

Access 2000 to Access 97 conversion gives switchboard problem

Access 2000 to Access 97 conversion gives switchboard problem

(OP)
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.

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

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") 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.

RE: Access 2000 to Access 97 conversion gives switchboard problem

(OP)
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.

RE: Access 2000 to Access 97 conversion gives switchboard problem

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

(OP)
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!

RE: Access 2000 to Access 97 conversion gives switchboard problem

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 "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





HTH  
PaulF

RE: Access 2000 to Access 97 conversion gives switchboard problem

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

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 "datone dattwo" seems to have become ([datone] & " " & [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.  

RE: Access 2000 to Access 97 conversion gives switchboard problem

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

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

RE: Access 2000 to Access 97 conversion gives switchboard problem

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.

RE: Access 2000 to Access 97 conversion gives switchboard problem

replace

 Dim dbs As Database

with

 Dim dbs As DAO.Database

PaulF

RE: Access 2000 to Access 97 conversion gives switchboard problem

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close