Table Of Contents - A second question

I just had a really helpful answer from JimD to a question I posted a few minutes ago and wanted to add a second question but accepted it too fast.  

I am now stuck on the second procedure in the article "How to Create a Table of Contents or Index for a Report" kbid=210269.  When I run the report I get a message "The expression on click produced the following error Type Mismatch".  

Under the MainSection header I have put =UpdateToC([MainSection],Report) as stated in the article.  When I run this in the Nortwind database it runs like a dream so it must be something in my table but I'm stuck as to what.
Function UpdateToC(ToCEntry As String, Rpt As Report)
    'Call from the OnPrint Property of the section containing
    'The Table of Contents Description Field
    'Updates the Table of Contents table.
    
    ToCTable.Seek "=", ToCEntry
    
    If ToCTable.NoMatch Then
        ToCTable.AddNew
        ToCTable!Description = ToCEntry
        ToCTable![Page number] = IntPageCounter
        ToCTable.Update
        End If
        
End Function

Open in new window

PerksPAsked:
Who is Participating?

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

x
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.

Rey Obrero (Capricorn1)Commented:
what are you referring to with  [MainSection] ?
  { =UpdateToC([MainSection],Report)  }
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I just had a really helpful answer from JimD to a question I posted a few minutes ago and wanted to add a second question but accepted it too fast. >>

  Even though accepted, your still allowed to post comments for up to 7 days.

<<I am now stuck on the second procedure in the article "How to Create a Table of Contents or Index for a Report" kbid=210269.  When I run the report I get a message "The expression on click produced the following error Type Mismatch".  >>

  This means that your stating something in an expression that is expected to be something else.  For example, the function:

Function UpdateToC(ToCEntry As String, Rpt As Report)

  Is expecting two arguments, a string and a report object.  If your first argument is a numeric, you would get a type mis-match.  So make sure that [MainSection] is a text data type.  If it's not, you can modify that to:

CStr([MainSection])

  Which will convert it to a string or you can modify the UpdateToC() function to accept a numeric as the first argument (I would do the first).

JimD.
0
PerksPAuthor Commented:
[MainSection] is a text field within my main directory table.  I have just tried modifying it to CStr([MainSection]) just to see what happened and at first it ran but returned Error0 in the table and after that it produced the error Invalid Use of Null.  

I have also checked the table of contents table and Description is a Text field and Page Number is a number (long integer)

I have removed the CStr and it is now back to Type Mismatch.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rey Obrero (Capricorn1)Commented:
what is the recordsource of your report?  is it a table or query?
do you have records returned by opening the recordsource of the report?
0

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
PerksPAuthor Commented:
Thank you Capricorn 1  you pointed me in the right direciton.

My recordsource was a query that returned records but some of the records did not have anything in the MainSection field.  When I excluded these everything ran fine.

Thank you both - you have saved me hours of head-scratching!!
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<[MainSection] is a text field within my main directory table.  I have just tried modifying it to CStr([MainSection]) just to see what happened and at first it ran but returned Error0 in the table and after that it produced the error Invalid Use of Null.  >>

  Well it sounds like your getting some junk in that field that you should not be.  Here's what I'd do to pin it down:

1. Put the Cstr() back around [MainSection] in the call to UpdateToC()

2. In the function UpdateToC(), place a stop just after the comments.

3. Execute the report.

  What will now happen is that the code will stop every time UpdateToC() is called.  You can step through the code to find out where it is failing by using F8.  F5 will continue from the point your at forward.

   As your stepping through, you can hover the mouse cursor over a variable or property and it will give you the value.  You can also call up the debug window with Ctrl/G and print to that window anything you want to look at (ie.  Debug.?  IntPageCounter).   You can also set variables (ie. IntPageCounter = 1) to modify what's happening on the fly.   So by stepping through the code, you should be able to see what the problem is.

  Once you know what the problem is, and say for example that it is that MainSection is null, you can either add code to handle the null in UpdateToC() or figure out why in the first place your have a null in [MainSection] at all (you might need to change the query the report is based on for example).

  And a note on the data that Mainsection contains; another good debugging tip is to run the query the report is based on on its own so you can see what data the report is working with (which is why I always like to build my reports on saved queries).  You can actually leave that open to the side and see which record the report is on as it is executing and your are stepping through the code.  This often lets you figure out why it's actiing the way it is based on the data you see in the query.

  Give it a swing and see what you can figure out.  If you get stuck holler.

JimD.
0
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.