Set rst = dbs.OpenRecordset(strSql)

I have used this switchboad for about a year now, but when I linked my table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

I know I could build my own switchboard but I really like like this
Microsoft version. Any suggestions?
************************************************** **********
Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.

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].Visible = True
Me![Command1].Enabled = True
Me![Command1].SetFocus
With Me![OptionLabel1]
.Visible = True
.FontWeight = conFontWeightBold
End With
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Me("Command" & intOption).Enabled = 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("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
Me("Command" & rst![ItemNumber]).Enabled = True
rst.MoveNext
Wend
End If

' Close the recordset and the database.
rst.Close
dbs.Close

End Sub

See whether changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

makes any difference.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have used this switchboad for about a year now, but when I linked my
table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

I know I could build my own switchboard but I really like like this
Microsoft version. Any suggestions?
************************************************** **********
Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.

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].Visible = True
Me![Command1].Enabled = True
Me![Command1].SetFocus
With Me![OptionLabel1]
.Visible = True
.FontWeight = conFontWeightBold
End With
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Me("Command" & intOption).Enabled = 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("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
Me("Command" & rst![ItemNumber]).Enabled = True
rst.MoveNext
Wend
End If

' Close the recordset and the database.
rst.Close
dbs.Close

End Sub

Thanks Doug I will try that.

"Douglas J. Steele" wrote:

See whether changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

makes any difference.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Richard" wrote in message
...
I have used this switchboad for about a year now, but when I linked my
table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

I know I could build my own switchboard but I really like like this
Microsoft version. Any suggestions?
************************************************** **********
Private Sub FillOptions()
' Fill in the options for this switchboard page.

' The number of buttons on the form.

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].Visible = True
Me![Command1].Enabled = True
Me![Command1].SetFocus
With Me![OptionLabel1]
.Visible = True
.FontWeight = conFontWeightBold
End With
For intOption = 2 To conNumButtons
Me("Option" & intOption).Visible = False
Me("OptionLabel" & intOption).Visible = False
Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
Me("Command" & intOption).Enabled = 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("OptionLabel" & rst![ItemNumber]).Visible = True
Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
Me("Command" & rst![ItemNumber]).Enabled = True
rst.MoveNext
Wend
End If

' Close the recordset and the database.
rst.Close
dbs.Close

End Sub

Try changing the line to the following and see if that helps

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Richard wrote:
I have used this switchboad for about a year now, but when I linked my table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

Hi Guys,

Tried both of your solutions with no luck. I will keep at it and see if I
can't rework this code a bit.

Thank you Doug and John.


"John Spencer" wrote:

Try changing the line to the following and see if that helps

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Richard wrote:
I have used this switchboad for about a year now, but when I linked my table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

On Dec 11, 12:44*am, Richard
wrote:
I have used this switchboad for about a year now, but when I linked my table
to the SQL server with OBDC, the code highlighted on this spot.

Set rst = dbs.OpenRecordset(strSql)

* *I know I could build my own switchboard but I really like like this
Microsoft version. Any suggestions?
************************************************** **********
Private Sub FillOptions()
' Fill in the options for this switchboard page.

* * ' The number of buttons on the form.

* * 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].Visible = True
* * Me![Command1].Enabled = True
* * Me![Command1].SetFocus
* * With Me![OptionLabel1]
* * * * .Visible = True
* * * * .FontWeight = conFontWeightBold
* * End With
* * For intOption = 2 To conNumButtons
* * * * Me("Option" & intOption).Visible = False
* * * * Me("OptionLabel" & intOption).Visible = False
* * * * Me("OptionLabel" & intOption).FontWeight = conFontWeightNormal
* * * * Me("Command" & intOption).Enabled = 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("OptionLabel" & rst![ItemNumber]).Visible = True
* * * * * * Me("OptionLabel" & rst![ItemNumber]).Caption = rst![ItemText]
* * * * * * Me("Command" & rst![ItemNumber]).Enabled = True
* * * * * * rst.MoveNext
* * * * Wend
* * End If

* * ' Close the recordset and the database.
* * rst.Close
* * dbs.Close

End Sub

Do you got any error message?

Regards,
Branislav Mihaljev
Microsoft Access MVP