Switchboard question

T

Tony Williams

This is part of the code for the standard Switchboard in Access 2002
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


However when I try to use it I get a message to say that

Compile error - User defined type not defined

and it points to this line

Dim dbs As Database

I've checked the choices after the "As" and "Database" doesn't appear.
Can anyone help?
Thanks
Tony
 
J

Jeff Conrad

You need to set a reference to the DAO object library. By default
Access 2000 and 2002 set a reference to the ADO library.

Here is some info I have posted in the past:To fix the References problem follow these steps:
- Open any module in Design view.
- On the Tools menu, click References.
- Scroll down to you get to Microsoft DAO 3.xx and check it.
- If you're using Access 97 that should be DAO 3.51 Object Library.
- If you're using Access 2000, 2002, or 2003 that should be DAO 3.6 Object Library.
- Close the References box again.
- Now re-compile again. Debug--Compile.
- Hopefully you should not see any more compile errors.

As good practice it may also be better to use this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

....rather than:

Dim dbs As Database
Dim rst as Recordset

This helps to avoid confusion for Access (but you still have to manually set a reference to DAO for
2000 and 2002).
Hope that helps,
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

in message:
This is part of the code for the standard Switchboard in Access 2002
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


However when I try to use it I get a message to say that

Compile error - User defined type not defined

and it points to this line

Dim dbs As Database

I've checked the choices after the "As" and "Database" doesn't appear.
Can anyone help?
Click to expand...
 
T

Tony Williams

Thanks Jeff worked fine
Tony
Jeff Conrad said:
You need to set a reference to the DAO object library. By default
Access 2000 and 2002 set a reference to the ADO library.

Here is some info I have posted in the past:To fix the References problem follow these steps:
- Open any module in Design view.
- On the Tools menu, click References.
- Scroll down to you get to Microsoft DAO 3.xx and check it.
- If you're using Access 97 that should be DAO 3.51 Object Library.
- If you're using Access 2000, 2002, or 2003 that should be DAO 3.6 Object
Library.
- Close the References box again.
- Now re-compile again. Debug--Compile.
- Hopefully you should not see any more compile errors.

As good practice it may also be better to use this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

...rather than:

Dim dbs As Database
Dim rst as Recordset

This helps to avoid confusion for Access (but you still have to manually
set a reference to DAO for
2000 and 2002).
Hope that helps,
--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

in message:
This is part of the code for the standard Switchboard in Access 2002
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


However when I try to use it I get a message to say that

Compile error - User defined type not defined

and it points to this line

Dim dbs As Database

I've checked the choices after the "As" and "Database" doesn't appear.
Can anyone help?
Click to expand...
Click to expand...
 

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

Similar Threads

Switchboard Wizard Number of Items on Page 7
Switchboard form no longer fills 1
Set rst = dbs.OpenRecordset(strSql) 5
SQL Server 9
Switchboard 7
Problem with Switchboard Form 0
Visual Basic Error 0
Form doesn't correctly show now with 2007 1

Top