Cannot locate type mismatch error

I am of intermediate skill with VBA and I am trying to adapt some code to work on my database.  Right now the code is returning a type mismatch error.  Unfortunately it it not specific about the error and I don't know how to locate the object that is having the problem.

Private Sub LoadMyTreeView()
    On Error GoTo Err_Handler

    Dim strSQL As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim nod As Node
    Dim strPName As String
    Dim strPKey As String
    Dim strSName As String
    Dim strSKey As String
    Dim strAName As String
    Dim strAKey As String
    ' Clear the treeview nodes

    Set dbs = CurrentDb
    strSQL = "SELECT ID_Project, Project_Name, ID_PamActivityNo, PamActivityDesc, ID_PamActivitiesWork, PamActivitiesWorkName " & _
             "FROM q_TreeView ORDER BY Project_Name;"

    Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

    If Not rst.BOF Then rst.MoveFirst
    Do Until rst.EOF
         ' Add Project node
        If strPName <> rst!Project_Name Then
            strPKey = rst!ID_Project & "|"
            strPName = rst!Project_Name
            Set nod = tvwMyTreeView.Nodes.Add(, , strPKey, strPName, "publisher_open", "publisher_closed")
            strSName = ""
        End If
        ' Add PAM Activity node
        If strSName <> rst!PamActivityDesc Then
            'If Nz(rst!ID_PamActivityNo, 0) Then GoTo 6969
            strSKey = strPKey & rst!ID_PamActivityNo & "|"
            strSName = rst!PamActivityDesc
            Set nod = tvwMyTreeView.Nodes.Add(strPKey, tvwChild, strSKey, strSName, "store", "store")
            nod.Tag = rst!ID_PamActivityNo
            strAName = ""
        End If
        ' Add Activitiy Work node
        If strAName <> rst!PamActivitiesWorkName Then
            strAKey = strSKey & rst!ID_PamActivitiesWork & "|"
            strAName = rst!PamActivitiesWorkName
            Set nod = tvwMyTreeView.Nodes.Add(strSKey, tvwChild, strAKey, strAName, "author", "author")
            nod.Tag = rst!ID_PamActivitiesWork
        End If


    LoadMyListView ("")

    Set dbs = Nothing
    Set rst = Nothing
    Exit Sub
    MsgBox Err.Description, vbCritical, "ERROR"
    Debug.Print Err.Description
    Resume Next
End Sub

Open in new window

I have also included the whole database. The form with the problem is called "f_PAMActivitiesHistory".  The form that I started with is the other one, and it works on a different set of data though.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
You never define the object tvwMyTreeView.

Try adding:

Dim tvwMyTreeView as MSComctlLib.TreeView

Then set the reference to the object

set tvwMyTreeView = me.TreeviewControlName.Object

One way to find errors is using the debugger and stepping through the code.

Watch your variables and the values you're trying to put into them. If you declare a variable as a string, only a string or a number that can be converted to a string (coercion...bad coding) can be assigned to it. Null cannot. IOW, if any of your recordset columns return a null value, your code will fail when it tries to assign that value to a string variable. The usual best way to handle this is to wrap the value with the Nz function, as in

If strPName <> Nz(rst!Project_Name, "") Then
strPKey = Nz(rst!ID_Project)
You cannot put null into a string variable.

Sensi12002Author Commented:
fyed:  I tried it, and that did not change anything.

lee555j5: This was a good suggestion, but the sql statement I have does not return any null values currently.  Still though, I have added the Nz function to a few places just to be sure.   I do still get the "type mismatch" error without being shown where it is exactly coming from.

I have walked through the whole program following the "locals" window and I don't see it.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Dale FyeOwner, Developing Solutions LLCCommented:

Dim nod As MSComctlLib.Node
Rey Obrero (Capricorn1)Commented:
change this line

Private Sub LoadMyListView(ByVal au_id As Long)


Private Sub LoadMyListView(ByVal au_id)
Rey Obrero (Capricorn1)Commented:
or use this

Private Sub LoadMyListView(ByVal au_id As Long)

but instead of using

    LoadMyListView ("")


    LoadMyListView (0)
A brute force option would be to use several Debug.Print statements coupled with a loop counter. If your loop counter stops at 12, your problem could be in the data of 12th row returned by the SELECT statement. You can start with 1 Debug.Print up to placing one between every line.

Brute force, ugly, and crude; but...
Rey Obrero (Capricorn1)Commented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Removing the error handling;

'On Error GoTo Err_Handler

will mean that the code stops as soon as the error occurs.
Sensi12002Author Commented:
I love this website!!  Capricorn you got it!  So I guess I sending a null to Sub LoadMyListView() then putting that null into a string was the problem, or maybe it was in passing it to the sub in the first place.

lee55J5:  I wish I understood your last post better, debug with loop counter sounds interesting.  I am not sure how you would set that up.  Thanks for the comments though, I will ponder on that one.

Cheers everyone, and thanks again!
Just so you know...

Create a dummy loop counter, increment the counter each loop, show the loop counter at the beginning of each loop using Debug.Print.

This way, you know which loop iteration caused the error.

Alternatively, rather than filling up the Immediate window with hundreds or thousands of loop counts, simply increment the loop counter as shown but don't use the Debug.Print each loop iteration. Place a single Debug.Print statement or use MsgBox to show the loop count in the error handler. This would be much faster across thousands of records anyway.

Dim i as Long

Do While Not rst.EOF

  i = i + 1
  Debug.Print i

  Your loop statements here


Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.