Switchboard code problem

[Neokey]

I wonder if anyone can help, I'm trying to convert a access 2000 db to 2003 and keep getting an activeX error on the following code:

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

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

Dim con As Object
Dim rs As Object
Dim stSql 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 con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" & Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

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

' Close the recordset and the database.
rs.Close
Set rs = Nothing
Set con = Nothing

End Sub

 

[boblarson]

Make sure that there is an ADO reference checked in the TOOLS > REFERENCES in the VBA window area.

And use the lowest common one - so if your users are on Windows XP use 2.6 and if on Vista - 2.7 or Windows 7 - 2.8. You can use 2.6 even if users are on later Windows versions, but ones on earlier Windows versions can't use the one later than their OS supports with its MDAC.

 

[boblarson]

Oh and you don't have to include the Application part in there. You can if you wish but it is not necessary as it is assumed by Access. So

Set con = CurrentProject.Connection

is fine.

 

[Neokey]

Many thanks, I'll try it and let you know how I get on.

Neo.

 

[Neokey]

Hi,
I've Selected the Microsoft data object library from tools references and I still have the same problem. The error I’m getting is:-

Run-time error “429”:

ActiveX component can’t create object.

I’ve even tried selecting all the versions from 2.6 up to 6.0 with no success.

Do you think its because I'm using Windows 7? I hope you can assist.

Neo.

 

[gemma-the-husky]

out of interest, which access version do you have with this code.

the switchboards I have (DAO access versions) don't have ADO connections

they just use a recordset based on the switchboard items table.

 

[boblarson]

I agree with Dave. But to go with the can't create object - I would not be trying to use ADO with late binding. You might be able to do it but I find it to be much more difficult and since you can just reference 2.6 for whatever Windows version you are on, it should work for everyone with Windows XP and greater.

So, you would use

Dim con As ADODB.Connection

Set con = CurrentProject.Connection

That should work for you. There may be a way using a generic object but I haven't gone that route myself.

 

[Neokey]

out of interest, which access version do you have with this code.

the switchboards I have (DAO access versions) don't have ADO connections

they just use a recordset based on the switchboard items table.

It’s an Access 2000 db which I’m converting to 2003, I’m running windows 7 on this laptop with both Access 2003 and 2007 installed.

I only use 2003 the run this db.

Thanks for the help Bob, I’ll try your way and let you know how I get on.

Many thanks,

Neo.

 

[Neokey]

Hi,

just tried it on the train on the way home, still same error?

Neo.

 

[boblarson]

Perhaps this is your problem:

http://support.microsoft.com/kb/2517589