Set rst = dbs.OpenRecordset(strSql)

R

Richard

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
 
D

Douglas J. Steele

See whether changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

makes any difference.
 
R

Richard

Thanks Doug I will try that.

Douglas J. Steele said:
See whether changing

Dim rst As Recordset

to

Dim rst As DAO.Recordset

makes any difference.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Richard said:
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
 
J

John Spencer

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
 
R

Richard

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

banem2

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top