Type mismatch Error in VBA when creating ActiveX control drop down list

Question

I got 3 ActiveX combo boxes in my sheet1. I used some code in This Workbook to populate first combo box list. Then I have created some function to get next set of combo box for cascading values. below is function:

  Function CascadeChild(TargetChild As OLEObject)
        Dim Myconnection As Connection
        Dim cmd As ADODB.Command
        Dim Myrecordset As Recordset
        Dim Myworkbook As String
        Dim strSQL As String

Set Myconnection = New ADODB.Connection
Set cmd = New ADODB.Command
Set Myrecordset = New ADODB.Recordset

'Identify the workbook you are referencing
   Myworkbook = Application.ThisWorkbook.FullName




'Open connection to the workbook
  Myconnection.Open "--"

Select Case TargetChild.Name

    Case Is = "Directorate"

        strSQL = "Select Distinct Directorate AS [TgtField] from  DBTable Where Division = '" & Sheet1.Division.Value & "' or 'All' = '" & Sheet1.Division.Value & "'"
    Case Is = "Area"
        strSQL = "Select Distinct Area AS [TgtField] from  DBTable Where ( Division = '" & Sheet1.Division.Value & "' or 'All' = '" & Sheet1.Division.Value & "') AND (Directorate  = '" & Sheet1.Directorate.Value & "' or 'All' = '" & Sheet1.Directorate.Value & "')"
End Select

'Load the Query into a Recordset
   Myrecordset.Open strSQL, Myconnection, adOpenStatic


'Fill the target child listbox
   With TargetChild.Object
        .Clear
        Do
        .AddItem Myrecordset![TgtField]
        Myrecordset.MoveNext
        Loop Until Myrecordset.EOF
        .Value = .List(0) '<<Automatically selects the first value in the ListBox
   End With



'Clean up
   Myconnection.Close
    Set Myrecordset = Nothing
    Set Myconnection = Nothing

End Function

Then I have written some code in Sheet1 in VBA:

Private Sub Division_Change()
Call CascadeChild(ActiveSheet.OLEObjects(Sheet1.Directorate.Name))
End Sub
Private Sub Directorate_Change()
Call CascadeChild(ActiveSheet.OLEObjects(Sheet1.Area.Name))
End Sub

First combo box giving values, then when I select value from ActiveX control, The error MSG populating with

Runtime Error, type mismatch

The Error coming with debug mode here .AddItem Myrecordset![TgtField] Any help

Answer 1

Try

.AddItem Myrecordset.Fields(0).Value