# Solved: Access database Form Switchboard error



## mckinneysteel (Sep 24, 2010)

My database Switchboard is not working. When I run Visual Basic Debug - Compile BIDS1 
An error comes up
complie error - User-defined type not defined.


----------



## HiTechCoach (Jul 15, 2010)

Has it been working?

What changed just before the error started?

Please post the VBA code module that is causing the issue.


----------



## mckinneysteel (Sep 24, 2010)

Thank you for responding Hi Tech Coach.
I designed this database a few years ago to track the bids we submit. This was done before Office 2010.
The database works. The table view is there, but no switchboard that conducts certain searches.
I am copying and pasting the VBA code below. If you want the whole database, I can email it to you. I thank you again for your help. Juliana / McKinney Steel Inc.

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
' Minimize the database window and initialize the form.
' Move to the switchboard page that is marked as the default.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.FilterOn = True

End Sub
Private Sub Form_Current()
' Update the caption and fill in the list of options.
Me.Caption = Nz(Me![ItemText], "")
FillOptions

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

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].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
Private Function HandleButtonClick(intBtn As Integer)
' This function is called when a button is clicked.
' intBtn indicates which button was clicked.
' Constants for the commands that can be executed.
Const conCmdGotoSwitchboard = 1
Const conCmdOpenFormAdd = 2
Const conCmdOpenFormBrowse = 3
Const conCmdOpenReport = 4
Const conCmdCustomizeSwitchboard = 5
Const conCmdExitApplication = 6
Const conCmdRunMacro = 7
Const conCmdRunCode = 8
' An error that is special cased.
Const conErrDoCmdCancelled = 2501

Dim dbs As Database
Dim rst As Recordset
On Error GoTo HandleButtonClick_Err
' Find the item in the Switchboard Items table
' that corresponds to the button that was clicked.
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("Switchboard Items", dbOpenDynaset)
rst.FindFirst "[SwitchboardID]=" & Me![SwitchboardID] & " AND [ItemNumber]=" & intBtn

' If no item matches, report the error and exit the function.
If (rst.NoMatch) Then
MsgBox "There was an error reading the Switchboard Items table."
rst.Close
dbs.Close
Exit Function
End If

Select Case rst![Command]

' Go to another switchboard.
Case conCmdGotoSwitchboard
Me.Filter = "[ItemNumber] = 0 AND [SwitchboardID]=" & rst![Argument]

' Open a form in Add mode.
Case conCmdOpenFormAdd
DoCmd.OpenForm rst![Argument], , , , acAdd
' Open a form.
Case conCmdOpenFormBrowse
DoCmd.OpenForm rst![Argument]
' Open a report.
Case conCmdOpenReport
DoCmd.OpenReport rst![Argument], acPreview
' Customize the Switchboard.
Case conCmdCustomizeSwitchboard
' Handle the case where the Switchboard Manager
' is not installed (e.g. Minimal Install).
On Error Resume Next
Application.Run "WZMAIN80.sbm_Entry"
If (Err <> 0) Then MsgBox "Command not available."
On Error GoTo 0
' Update the form.
Me.Filter = "[ItemNumber] = 0 AND [Argument] = 'Default' "
Me.Caption = Nz(Me![ItemText], "")
FillOptions
' Exit the application.
Case conCmdExitApplication
CloseCurrentDatabase
' Run a macro.
Case conCmdRunMacro
DoCmd.RunMacro rst![Argument]
' Run code.
Case conCmdRunCode
Application.Run rst![Argument]
' Any other command is unrecognized.
Case Else
MsgBox "Unknown option."

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

HandleButtonClick_Exit:
Exit Function
HandleButtonClick_Err:
' If the action was cancelled by the user for
' some reason, don't display an error message.
' Instead, resume on the next line.
If (Err = conErrDoCmdCancelled) Then
Resume Next
Else
MsgBox "There was an error executing the command.", vbCritical
Resume HandleButtonClick_Exit
End If

End Function


----------



## HiTechCoach (Jul 15, 2010)

What lines of code are generating the error?


----------



## mckinneysteel (Sep 24, 2010)

The error occurs at this line

Dim dbs As Database


----------



## mckinneysteel (Sep 24, 2010)

The error reads
Complie error:
User-defined type not defined
ok Help


----------



## HiTechCoach (Jul 15, 2010)

I find it best to fully qualify the Dim statements liek this:


```
Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
```
Also what version of Access are you using?

If 2007/2010, have you created a Trusted Location for the folder with the database?


----------



## mckinneysteel (Sep 24, 2010)

Thank you again for responding Hi Tech Coach.
I changed the to the above code and the same error appears.
We are a small office, and we use Office 2010.
Can I send you this database?


----------



## HiTechCoach (Jul 15, 2010)

I sent you a PM with my email address


----------



## chaostheory (May 18, 2007)

Make sure you have the reference in the vba properties setup or i believe that will cause the Dim of the DB to crash.

Alt+F11 to get VB editor up
Tools --->
References---->
"Microsoft DAO 3.6 object library" i believe is the one you need a check next too in order to work with DB stuff...could be mistaken but anytime im in excel and work with databases from there if i dont have that, none of it works.


----------



## HiTechCoach (Jul 15, 2010)

chaostheory said:


> Make sure you have the reference in the vba properties setup or i believe that will cause the Dim of the DB to crash.
> 
> Alt+F11 to get VB editor up
> Tools --->
> ...


In Access 2007 and 2010 with the ACCDB (ACE) format it uses the new lreference for "Microsoft Office xx.x Access database engine" for DAO. It does use the "Microsoft DAO 3.6 object library" for the old MDB (JET) format. If it does have DAO 3.6 checked in a ACCDB then this can cause issues.


----------



## HiTechCoach (Jul 15, 2010)

chaostheory was correct. :up:

Since the database is still in the JET (.mdb) format you need to add the DAO library.


----------



## mckinneysteel (Sep 24, 2010)

I added the checkmark to the Microsoft ADO 3.6 object library. Thank you for your help chaostheory.
Also, Problem fixed with the help from HiTechhCoach too !!! Thank you so much HiTechCoach.
You are prompt and an excellent advisor.


----------



## HiTechCoach (Jul 15, 2010)

Glad chaostheory and I could assist.

*Boyd Trimmell* aka Hi Tech Coach  ( HiTechCoach.com free Access stuff)
*Microsoft Access MVP 2010 *
Business Process Management - Accounting/CRM Software Developer 
_Programming: Nine different ways to do it right, a thousand ways to do it wrong._
_Binary--it's as easy as 1-10-11_


----------

