My switchboards won't work with WinXP !

[Roger Ottaway]

I have Access2002sp3 on WinXP. I have four different databases, all with a
Switchboard which is nominated to open in the Startup. In my macros I have
an autoexec which maximises the Switchboard when opening. For the last year
or so these have all been working just fine. All of a sudden, none of the
switchboards work ... neither on startup when the database if opened or when
you click on it as a form ! I'm not aware that anything has changed lately
with my WinXP or Access setup. The database opens with the database window
and even though the startup is correct, the switchboard doesn't appear. I
have tried running the Access repair but this also hasn't fixed the problem
!



The only clue I have is with one of the database which shows a runtime error
Method 'Connection' of object'_CurrentProject' failed. This refers to a line
of code in the switchboard as follows



Set con = Application.CurrentProject.Connection



which is supposed to be incorrect.



Has anyone any ideas about what is going on, please ? How can I fix this ?



thanks … Roger

 

[Roger O]

I have had no help so I have cross posted this to setupconfig !!

.... Ro

 

[Jeff Conrad]

Hi Roger,

You might try checking the References for the database.
Any marked Missing?
Write down the references showing and then uncheck
the ADO Reference. Close the References box, and then
re-open the dialog box. Find the correct ADO Reference
and then re-check it. Then compile your database.
Any errors on compile?

Now close the database and then re-open.
Fixed or still the same problem?

 

[Roger O]

Dear Jeff

thanks for your reply. The database is in Access2000 file format, does that
make a difference. I opened the Switchboard in design view, then went to
code, tools, references. What I get is a list of references that are
available, of which 5 were checked. They are

visual basic for applications
microsoft access 10.0 object library
OLE automation
microsoft visual basic for applications extensibility 5.3
microsoft DAO 3.6 object library

There are many more available, but unchecked. Where can I see if any are
missing ?

I unchecked Microsoft DAO 3.6 object library, closed access, then reopened
the database, then the switchboard, and rechecked . I assume that this is
what you are referring to in "ADO Reference" ?? It made no difference.

I also tried adding in microsoft ADO ext 2.7 for DDL and security ... but it
also made no difference. This was not originally checked !

... Roger

 

[Jeff Conrad]

Hi Roger,

Comments below....

thanks for your reply. The database is in Access2000 file format, does that
make a difference.

It does for coding and references yes.

I opened the Switchboard in design view, then went to
code, tools, references. What I get is a list of references that are
available, of which 5 were checked. They are

visual basic for applications
microsoft access 10.0 object library
OLE automation
microsoft visual basic for applications extensibility 5.3
microsoft DAO 3.6 object library

Ahh haa, I think I see the problem!
You do not have a reference set to the ADO object library in this database.
You do not *have* to have a reference set to ADO in 2000, 2002, or 2003
unless of course you have ADO code in your database, which you do.
The Switchboard form that the wizard made for you uses ADO code
because by default 2000 and 2002 have a reference set to ADO.

Here is my guess at what happened (just a guess).
- You have this 2000 format database using 2002
- At some point you use the SBM to create a switchboard form
- Later on you manually set a reference to the DAO object library
(You would HAVE to have done this yourself as Access would
not automatically do this for you using 2000 or 2002)
- You removed the ADO reference
(Again you would have to have done this manually at some point)

Now the Switchboard form is failing because there is no ADO
reference and this code.....

Set con = Application.CurrentProject.Connection

.....is ADO code.

There are many more available, but unchecked. Where can I see if any are
missing ?

It would have been fairly obvious.
You would have seen the word MISSING right next to one (or more)
of the references.

I unchecked Microsoft DAO 3.6 object library, closed access, then reopened
the database, then the switchboard, and rechecked .

No, I did not say to close the database after unchecking DAO.
I said to do this:
- Close the References box, and then re-open the dialog box.
- Find the correct ADO Reference and then re-check it.
- Then compile your database.
- Any errors on compile?

I assume that this is what you are referring to in "ADO Reference" ??
It made no difference.

No difference is no surprise given the steps you did.

I also tried adding in microsoft ADO ext 2.7 for DDL and security ... but it
also made no difference. This was not originally checked !

At this point you have at least five options available to fix this issue.

1. Scrap the Switchboard form and table altogether and build your
own custom main menu form.

- Personally I do not think you need to do this, but I should at least
present the option to you.

2. Uncheck the DAO library reference, add a reference to ADO and then
go through ALL your code and make sure none of your code is using DAO.
Your switchboard form will work, but other parts of your database will fail.

- Could be a lot of work based on the size of your database. I do not
believe this is the best course of action here.

3. Leave the reference to the DAO library intact and add a reference to ADO.
In the References box you would position the DAO above ADO so Access
will use the DAO first by priority. You would then need to "disambiguate"
certain declarations, because objects with the same names exist in the two models.
For example, to ensure that you get a DAO recordset, you'll need to use
Dim dbs as DAO.Recordset. If you want an ADO recordset, you would have
to use Dim dbs As ADODB.Recordset.

These are the common objects between the two libraries:
Connection, Error, Errors, Field, Fields, Parameter, Parameters,
Property, Properties, and Recordset.

- This is a good alternative to solve your issue. Your References would then
look like this:

Visual Basic for Applications
Microsoft Access 10.0 Object Library
OLE Automation
Microsoft Visual Basic for Applications Extensibility 5.3
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.x Library

The last one is the one you would need to add. The "x" will
be a different number based on Access version. Make sure
it is below the DAO one by using the up/down arrows.

Make sure you compile your database after doing this!!
I assume you know how to compile the database correct?
Access will tell you if it finds any problems. Then carefully
test ALL parts of the database.

4. Leave the DAO reference checked and do not add a reference
to the ADO library. Then manually change any ADO code behind
the Switchboard form to DAO code so the form will work correctly.

- Pretty trivial if you know how to do this, otherwise it could be
difficult if you do not know how to change the code!

5. Leave the DAO reference checked and do not add a reference
to the ADO library. Instead of manually changing all of the ADO
code behind the Switchboard form, just copy/paste the DAO equivalent
code from an Access 97 database SBM-generated form. Poof, all
problems solved!

- If you have not made any changes to the code behind the Switchboard
form, than this would be a piece of cake. I could just post the 97 SBM
form code here and you could just paste it over your form code.

Assuming you have not made any drastic changes to the form code,
then I think number 5 would be the easiest and gets my vote. The
choice is still yours to make, but whatever you decide, make some
backups of your database first!!

 

[Roger O]

Dear Jeff

thanks for your help so far. I am proud of the Access databases that I have
created, and they do a good job. But, I am not an Access expert by any
measure. All of your solutions involve changing the code, and if needed I
can and will do that. However, the thing that gets me is that I have 4
databases, and all of the four stopped working at the same time. We have a
small business and I have the same databases on another computer which runs
WinNT with Access 2002 as well. The "code" for the databases is exactly the
same, and in the office it runs just fine with the Switchboards coming up
with the startup, as they should.

I have mananaged to figure out that on my WinXP computer I have Access2002
(10.6501.6714)sp3 and on the WinNT computer I have Access2002
(10.403.4219)sp2, so obviously something must have happened when I upgraded
my XP Access2002 setup from sp2 to sp3. Does any of that make sense ? Is
there anything in Access2002sp3 that I can uninstall to make my databases'
switchboards work again ?

thanks ... Roger

 

[Jeff Conrad]

Hi Roger,

Comments below...

thanks for your help so far. I am proud of the Access databases that I have
created, and they do a good job. But, I am not an Access expert by any
measure. All of your solutions involve changing the code, and if needed I
can and will do that. However, the thing that gets me is that I have 4
databases, and all of the four stopped working at the same time. We have a
small business and I have the same databases on another computer which runs
WinNT with Access 2002 as well. The "code" for the databases is exactly the
same, and in the office it runs just fine with the Switchboards coming up
with the startup, as they should.

Then I would check the References on all the databases. It is quite possible
that the other databases do have a Reference set to the ADO library.
The Switchboard code you posted is, without a doubt, ADO code. In
order for that code to run properly as is a Reference must be set to ADO.

I have mananaged to figure out that on my WinXP computer I have Access2002
(10.6501.6714)sp3 and on the WinNT computer I have Access2002
(10.403.4219)sp2, so obviously something must have happened when I upgraded
my XP Access2002 setup from sp2 to sp3. Does any of that make sense ? Is
there anything in Access2002sp3 that I can uninstall to make my databases'
switchboards work again ?

No need to uninstall anything, period!
Don't panic. Deep cleansing breaths Roger....in.....out....repeat...


I truly belive option 5 is the easiest method for you, but the choice
is yours to make. If you have made NO code modifications to
the Switchboard form itself than the solution would be as easy as this:
1. Backup your database
2. I will post all of the Switchboard code behind a 97 form here.
3. You copy all the code from here in one pass and paste it over all
the existing code behind your Switchboard form. Poof, done!
4. Compile the code to see if there are any problems, compact
the database and you are up and running. That's it.

 

[Roger O]

Dear Jeff

I have checked my db's on the WinNT machine (which are also Access2000 db's
running on Access2002, exactly the same as on the WinXP machine), and they
have the same references as my problem db's. On my WinXP machine I have
another database where the switchboard is opening okay, and that has MS
Forms 2.9 OL as an extra reference. So I put that in, and it made no
difference.

I guess I am back to plan A (below) ??? which means option 5 ???. I had
Access97 on my PCs when I developed the db's, and then upgraded to
Access2000 and then Acess2002. I can't understand why it should just happen
now, unless the upgrade to sp3 turned something on, or something off, which
has affected just these particular db's ??

over to you !!! ........... Roger

 

[Jeff Conrad]

Dear Jeff

I have checked my db's on the WinNT machine (which are also Access2000 db's
running on Access2002, exactly the same as on the WinXP machine), and they
have the same references as my problem db's. On my WinXP machine I have
another database where the switchboard is opening okay, and that has MS
Forms 2.9 OL as an extra reference. So I put that in, and it made no
difference.

I guess I am back to plan A (below) ??? which means option 5 ???. I had
Access97 on my PCs when I developed the db's, and then upgraded to
Access2000 and then Acess2002. I can't understand why it should just happen
now, unless the upgrade to sp3 turned something on, or something off, which
has affected just these particular db's ??

over to you !!! ........... Roger

Hi Roger,

Well I'm a little stumped why the exact same switchboard are working in the
other databases if there is no ADO reference checked. The Switchboard code
you gave is most definitely ADO.

Anyway, below is ALL the code behind a 97 Switchboard Wizard form.
Just copy/paste all this code over the existing code behind your Switchboard
form. I have tried to correct any possible line wrapping by the newsreader,
but there may still be some. You may have to carefully double-check everything
after pasting.

Please make 2 backups of your file before beginning!!

'**********Code Start*************
Private Sub Form_Open(Cancel As Integer)
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub

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

End Sub

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
'**********Code End*************

Compile the code, save and close the form.
Then test everything.
Problem solved??

 

[Roger O]

Dear Jeff

many thanks, yes problem (whatever it was) is solved !! I checked the
references again, but they are just the same. So, frankly, I don't know ...
just a total mystery. Anyhow, many thanks

best wishes ... Roger

Dear Jeff

I have checked my db's on the WinNT machine (which are also Access2000 db's
running on Access2002, exactly the same as on the WinXP machine), and they
have the same references as my problem db's. On my WinXP machine I have
another database where the switchboard is opening okay, and that has MS
Forms 2.9 OL as an extra reference. So I put that in, and it made no
difference.

I guess I am back to plan A (below) ??? which means option 5 ???. I had
Access97 on my PCs when I developed the db's, and then upgraded to
Access2000 and then Acess2002. I can't understand why it should just happen
now, unless the upgrade to sp3 turned something on, or something off, which
has affected just these particular db's ??

over to you !!! ........... Roger

Hi Roger,

Well I'm a little stumped why the exact same switchboard are working in the
other databases if there is no ADO reference checked. The Switchboard code
you gave is most definitely ADO.

Anyway, below is ALL the code behind a 97 Switchboard Wizard form.
Just copy/paste all this code over the existing code behind your Switchboard
form. I have tried to correct any possible line wrapping by the newsreader,
but there may still be some. You may have to carefully double-check everything
after pasting.

Please make 2 backups of your file before beginning!!

'**********Code Start*************
Private Sub Form_Open(Cancel As Integer)
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True
End Sub

Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions
End Sub

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

End Sub

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
'**********Code End*************

Compile the code, save and close the form.
Then test everything.
Problem solved??

 

[Jeff Conrad]

Dear Jeff

many thanks, yes problem (whatever it was) is solved !! I checked the
references again, but they are just the same. So, frankly, I don't know ...
just a total mystery. Anyhow, many thanks

best wishes ... Roger

Excellent, glad to hear everything is working fine now!
I'm still not sure on the references problem either, but
we'll just have to chalk it up to a mystery now.

You're very welcome, glad I could help.
Come back anytime, we're open everyday of the year.