Really need programming assistance HERE!

[fhs]

I'm working with a fairly old financial application (converted from '97 to 2007). Unexpectedly, the application suddenly isn't loading and I'm getting a "Run-time error '-2147217838 (80040e52)': Data source object is already initialized." What is going on? This worked a week ago! Here is the module that pops open and the highlighted line of code that is triggered is: Set con = Application.CurrentProject.Connection

Sure hope someone can see what's happened and what needs to be repaired.


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

Thanks for assisting.

 

[VilaRestal]

Some of the code is a bit wierd:

Dim con As Connection
Dim rs As ADODB.Recordset
Set con = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset


and I think the lines

Me("Option" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Visible = True
Me("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]

should be

Me.Controls("Option" & rs![ItemNumber]).Visible = True
Me.Controls("OptionLabel" & rs![ItemNumber]).Visible = True
Me.Controls("OptionLabel" & rs![ItemNumber]).Caption = rs![ItemText]

 

[gemma-the-husky]

this looks like a strange version of the standard MS switchboard, using ADO instead of DAO - so problems may be to do with references.

 

[VilaRestal]

I'm sure CurrentProject.Connection should be CurrentProject.AccessConnection in 2007
(That will be the cause of the error)

But declaring the connection and recordset as objects can't be good either. It's better to tell Access VBA exactly what you want a variable to be imo.

And I wonder whether Me("controlname") works in 2007. (Shorthand for Me.Controls("controlname")) I've never tried it but will