2007 to 2010 Conversion Compile Error: User-defined type not defined

Question

I recently converted (exported/imported) a 2007 Access file to 2010. Everything works fine except one form. I keep getting the error "Compile error: User-defined type not defined"

I tried adding "Microsoft ActiveX Data Objects 2.8" to my References, but the problem still exists. Any idea on what the problem could be? I'm an amature and I did not write this code, but here it is. The text in bold is what Access highlighted as the issue. Thanks so much in advance!

Option Compare Database Option Explicit

' Clears all nodes on a treeview control Sub ClearTreeView(tvwTree As TreeView) On Error GoTo EH tvwTree.Nodes.Clear Exit Sub EH: MsgBox "Error " & Err.Number & ": " & Err.Description End Sub

' Calls functions to clear and populate a treeview control ' Parameters: ' strForm Name of the form ' strTV TreeView control name ' strSourceName Name of the table or query containing the data used to populate the treeview ' strChildField ID field for the child records ' strParentField Parent ID Field ' strTextField Field containing text that will be used as node labels ' Sub FillTreeView(tvwTree As Object, strSourceName As String, strChildField As String, strParentField As String, strTextField As String) Dim strSQL As String Dim rs As DAO.Recordset

On Error GoTo EH

' Open the recordset using table and fields specified in Sub parameters
strSQL = "SELECT " & strChildField & ", " & strParentField & ", " & strTextField & " FROM " & strSourceName
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

' Clear any existing data out of the treeview
ClearTreeView tvwTree

' Call recursive function to fill in treeview
AddTreeData tvwTree, rs, strChildField, strParentField, strTextField

' Close the recordset
rs.Close
Set rs = Nothing
Exit Sub

EH: MsgBox "Error " & Err.Number & ": " & Err.Description End Sub

' Recursive function to populate a treeview control ' Parameters: ' strFormName Name of the form ' strTreeViewName TreeView control name ' rs Recordset containing the data used to populate the treeview ' strChildField ID field for the child records ' strParentField Parent ID Field ' strTextField Field containing text that will be used as node labels ' varParentID Optional parameter that only gets passed for recursive calls to this function. Specifies the ID of the current record to be used as a ' ParentID when searching the recordset for "grand-children", etc. Sub AddTreeData(objTV As TreeView, rs As DAO.Recordset, strChildField As String, strParentField As String, strTextField As String, Optional varParentID As Variant) Dim nodChild As Node Dim nodParent As Node Dim strLabel As String Dim strNodeID As String Dim strCriteria As String Dim strBookmark As String

On Error GoTo EH

' Test for a circular reference
If rs(strChildField) = rs(strParentField) Then GoTo EH_CircularReference

' If the optional parameter is missing, then this is the first(non-recursive) call to this function.
' Set the critieria to look for a parent id of 0.
If IsMissing(varParentID) Then
    strCriteria = strParentField & " = 0 "
Else
    ' Otherwise, extract the childID portion of the node ID, which was passed as an optional parameter.
    strCriteria = strParentField & " = " & Mid(varParentID, InStr(1, varParentID, "C") + 1)
    ' Define the parent node
    Set nodParent = objTV.Nodes("node" & varParentID)
End If

' Look for records having the specified "parent"
rs.FindFirst strCriteria
Do Until rs.NoMatch
    ' Read node caption from the text field
    strLabel = rs(strTextField)
    ' Create a new node ID in the format ParentID &"C" & ChildID (eg: 4C12)
    strNodeID = "node" & rs(strParentField) & "C" & rs(strChildField)

    ' If optional parameter is missing (first call to this function)...
    If Not IsMissing(varParentID) Then
        'add new node to the next higher node for this record
        Set nodChild = objTV.Nodes.Add(nodParent, tvwChild, strNodeID, strLabel)
    Else
        ' Otherwise, add new node to the top level of the tree
        Set nodChild = objTV.Nodes.Add(, , strNodeID, strLabel)
    End If

    ' Bookmark our place in the recordset so that we can resume the search from the same point after the recursive call to this function.
    strBookmark = rs.Bookmark

    ' call this function recursively for "children"
    AddTreeData objTV, rs, strChildField, strParentField, strTextField, rs(strParentField) & "C" & rs(strChildField)

    ' Return to bookmared place in the recordset
    rs.Bookmark = strBookmark

    ' Find the next record having the same parentID
    rs.FindNext strCriteria
Loop

Exit Sub

EH_CircularReference: MsgBox "Exiting because of a circular reference in which a child record was determined to be it's own parent." Exit Sub

EH: MsgBox "Error " & Err.Number & ": " & Err.Description End Sub

Robert Robert · Accepted Answer · 2013-10-18T16:25:10

I received the same error on one of my User Forms in Excel!

If your new 2010 Office package is 64Bit, then the previous 32-Bit ActiveX Controls used in the 2007 version will not be compatible.

Refer to this link: http://msdn.microsoft.com/en-us/library/ee691831(office.14).aspx#odc_office2010_Compatibility32bit64bit_ActiveXControlCOMAddinCompatibility

If you try opening the form in design view and the TreeView control doesn't exist on the form, then this is likely to be the issue.

To find if you installed the 64-bit version: Open Access -> File -> Help -> and look under "About Microsoft Access" on the right - it should say 32-Bit or 64-Bit in brackets

As the link explains, you will have to replace the incompatible functionality - so you will have to use a new control.

Possible Solution:

If you open the form in design view, and select the down arrow on the Controls Group in the Ribbon Tab - there should be an option named "ActiveX Controls" (You have to be in design view to select it)

Search here for the "CTreeView" Control and try working with that instead of the traditional Microsoft TreeView Control (which shouldn't be listed on 64-Bit Access).

If you have 32-Bit office installed, then I can't figure out the problem with what you have posted - but I suspect it's the case.

Best Regards,

2007 to 2010 Conversion Compile Error: User-defined type not defined

1 Answers