Database works in 2003 but throws error "Set rst = dbs.OpenRecordset(strSQL)" in 2010

[jeepjenn]

Good Morning,



I have inherited a database that hasn't been fully functional in 12 Years (yay me!)
It was origionally created in the 90's. I have managed to bring it into 2003 and fix all of the switchboard issues. (2003 is the Access I have the most experience with, although still fairly limited).
Once I had everything functional, I was told we are being converted to 2010 for a short period, then to 2013 next year.

When I follow the process to bring it into 2010, I get the error below showing up (in bold):

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

(I included the surrounding text hoping that will help out)

Any easy solutions or thoughts?

[pbaldy]

What's the error? How is rst declared? Make sure DAO is specified.

[ssanfu]

Any easy solutions or thoughts?

Do you have the following two line at the top of every module (form and standard)?

Option Compare Database
Option Explicit

The "Option Explicit" line requires every variable to be declared. In the IDE, you can set an option to automatically add "Option Explicit"...
TOOLS/OPTIONS ...then in the Editor tab, check the 2nd option "Require Variable Declaration". This will only add the line to NEW modules. For existion modules, you will need to manually add the line.

In the declaration statements for "Database" and "Recordset", you should specify the library

Option Compare Database
Option Explicit

Sub SomeProcedure()
Dim d As DAO.Database
Dim r As DAO.Recordset

'Other code

End Sub

[jeepjenn]

the "option Compare Database etc" is at the top of each module...

I changed the following:

Dim d As DAO.Database
Dim r As DAO.Recordset

Now I get a compile error: variable not defined...(the bold parts are highlighted)



Private Sub FillOptions()
' Fill in the options for this switchboard page.
' The number of buttons on the form.
Const conNumButtons = 8

Dim d As dao.Database
Dim r As dao.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



Can you also recommend a book or website that will help me grasp this? Its like italian to me right now...

[John_G]

Hi -

Use Dim dbs As dao.Database. You declared "d" as Database, but then used "dbs " in the Set statement.

The same for rst - use rst in the Dim statement.

John

[ssanfu]

Can you also recommend a book or website that will help me grasp this? Its like italian to me right now...

It is mostly:

Do
   program
   fix errors
   read
   ask questions
Loop

I'm still stuck in the loop.....

[jeepjenn]

Sigh....I'm just going to delete the switchboard and recreate it. THAT I know how to do, and will probably be easier than trying to figure this out.

Thanks for the suggestions Guys!!