MS Error Compile Error User-defined type not defined" (1 Viewer)

[lowanam1]

Good morning, I am trying to convert from Access 2007 to 2010 and I am getting this error "Compile Error User-defined type not defined. Please help Thanks!

Private Sub cmdAppend_Click()
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

'set up the recordset
Dim myRSQuotes As New ADODB.Recordset
myRSQuotes.ActiveConnection = cnn1
'variables for the work required
Dim ctl1 As Control
Dim lngloop1 As Long
'variables for the subcontractors
Dim ctl2 As Control
Dim lngloop2 As Long
'variable to hold the contractors for form filtering
Dim strIDs As String
'open the recordset based on the tblSubcontractorQuotes table
myRSQuotes.Open "tblSubcontractorQuotes", , adOpenDynamic, adLockOptimistic

'set the controls to the work required list or subcontractor list
Set ctl1 = Me.lstWorkReq 'work required control
Set ctl2 = Me.lstSubs 'subcontractor control

'check to make sure at least 1 required work item has been selected otherwise return message
If ctl1.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one required work item from the list"
ctl1.SetFocus
Exit Sub
End If
'check to make sure at least 1 required subcontractor has been selected otherwise return message
If ctl2.ItemsSelected.Count = 0 Then
MsgBox "You must select at least one subcontractor from the list"
ctl2.SetFocus
Exit Sub
End If

'for each selected work, add the subcontractors to the tblSubcontractorQuotes table via the recordset
For lngloop1 = 0 To ctl1.ItemsSelected.Count - 1
For lngloop2 = 0 To ctl2.ItemsSelected.Count - 1

'store the IDs of the subcontractors
If ctl2.ItemsSelected.Count = 1 Then
strIDs = ctl2.ItemData(ctl2.ItemsSelected(lngloop2))
Else
If lngloop2 = 0 Then
strIDs = strIDs & ctl2.ItemData(ctl2.ItemsSelected(lngloop2))
Else
strIDs = strIDs + "," & ctl2.ItemData(ctl2.ItemsSelected(lngloop2))
End If
End If

If DCount("*", "tblSubcontractorQuotes", "fkCompanyID=" & ctl2.ItemData(ctl2.ItemsSelected(lngloop2)) & " AND fkworkrequiredID=" & ctl1.ItemData(ctl1.ItemsSelected(lngloop1))) = 0 Then
With myRSQuotes
.AddNew
!fkCompanyID = ctl2.ItemData(ctl2.ItemsSelected(lngloop2))
!fkworkrequiredID = ctl1.ItemData(ctl1.ItemsSelected(lngloop1))
.Update
End With
End If
Next lngloop2

Next lngloop1

myRSQuotes.Close
Set myRSQuotes = Nothing
Set ctl1 = Nothing
Set ctl2 = Nothing
MsgBox "Proceed to the Next Screen to Enter Quotes"
'Debug.Print strIDs
DoCmd.OpenForm "frmSubcontractors", acNormal, , "pkCompanyID in (" & strIDs & ")"


End Sub

 

[DrallocD]

In Tools / References make sure that you have "Microsoft Activex Data Objects n.n Library" selected. If not, select it.

 

[lowanam1]

Thank you. When i hit Alt F11 and went to tools, References is blacked out and I am unable to select it. ???

 

[lowanam1]

Ok I figured it out. I had to go into the design view and then view code and not go the Alt F11 route. However this is what I see that is close to what you described...which one should I select? Thanks for your help!

Microsoft Activex Data objects multi dimensiaonal 2.8 Library
Microsoft Activex Data objects multi dimensional 6.0 Library
Microsoft Activex Data objects 2.0 Library
Microsoft Activex Data objects 2.1 Library
Microsoft Activex Data objects 2.5 Library
Microsoft Activex Data objects 2.6 Library
Microsoft Activex Data objects 2.7 Library
Microsoft Activex Data objects 2.8 Library
Microsoft Activex Data objects 6.1 Library
Microsoft Activex Data objects recordset 2.8 Library
Microsoft Activex Data objects recordset 6.0 Library

 

[DrallocD]

I would use Microsoft Activex Data objects 2.8 Library

 

[lowanam1]

Thank you that removed the error code however the form is no longer populating the data that should show up on the form .....hope that makes sense. thanks !

 

[lowanam1]

Im not sure what checking that box does but my form doesnt work any longer....is there something else that I am missing?

 

[DrallocD]

There is error in the logic where you build up strIDs. They should be built in the outer loop, not the inner loop so you would need to reverse the loops. As it is, by selecting more than 1 item in lstWorkReq you will corrupt the strID list.

 

[DrallocD]

Checking the box allows you to use ADODB connections and recordsets. The alternative is to use DAO (which I use 99% of the time).

 

[DrallocD]

The alternative is to uncheck the ADODB reference and "late bind" the objects:

    Dim myRSQuotes As Variant
    Set myRSQuotes = CreateObject("ADODB.Recordset")
    myRSQuotes.Open "tblSubcontractorQuotes", CurrentDb.Connection, 2, 3

 

[lowanam1]

I am not familiar with code. I did not write this code. Can you please explain in more detail. Thank you again

 

[DrallocD]

Try removing the ADODB reference and replacing your code with the following:

Obviously I can't compile this without your form so it may not work but should give you an idea of how to change the code to fix the strID problem and late bind the ADODB object.

Private Sub cmdAppend_Click()
    Dim strIDs As String
    Dim thisSub As Variant, thisWorkItem As Variant
    Dim myRSQuotes As Variant
    Set myRSQuotes = CreateObject("ADODB.Recordset")
    myRSQuotes.Open "tblSubcontractorQuotes", CurrentDb.Connection, 2, 3  'adOpenDynamic, adLockOptimistic
 
    'check to make sure at least 1 required work item has been selected otherwise return message
    If lstWorkReq.ItemsSelected.Count = 0 Then
        MsgBox "You must select at least one required work item from the list"
        lstWorkReq.SetFocus
        Exit Sub
    End If
    'check to make sure at least 1 required subcontractor has been selected otherwise return message
    If lstSubs.ItemsSelected.Count = 0 Then
        MsgBox "You must select at least one subcontractor from the list"
        lstSubs.SetFocus
        Exit Sub
    End If
 
    For Each vSub In lstSubs.ItemsSelected
        'Add the SubContractor ID to the list
        strIDs = strIDs + IIf(LenB(strIDs) = 0, "", ", ") & lstSub.ItemData(vSub)
        For Each thisWorkItem In lstWorkReq.ItemsSelected
            If DCount("*", "tblSubcontractorQuotes", "fkCompanyID=" & lstSub.ItemData(thisSub) & " AND fkworkrequiredID=" & lstWorkReq.ItemData(thisWorkItem)) = 0 Then
                With myRSQuotes
                    .AddNew
                    !fkCompanyID = lstSub.ItemData(thisSub)
                    !fkworkrequiredID = lstWorkReq.ItemData(thisWorkItem)
                    .Update
                End With
            End If
        Next vItem
    Next vSub
 
    myRSQuotes.Close
    Set myRSQuotes = Nothing
 
    MsgBox "Proceed to the Next Screen to Enter Quotes"
    'Debug.Print strIDs
    DoCmd.OpenForm "frmSubcontractors", acNormal, , "pkCompanyID in (" & strIDs & ")"
End Sub