?
Solved

Table Of Contents - A second question

Posted on 2008-11-11
6
Medium Priority
?
393 Views
Last Modified: 2015-11-29
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

0
Comment
Question by:PerksP
  • 2
  • 2
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22931110
what are you referring to with  [MainSection] ?
  { =UpdateToC([MainSection],Report)  }
0
 
LVL 58
ID: 22931123
<<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
 

Author Comment

by:PerksP
ID: 22931322
[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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22931391
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
 

Author Comment

by:PerksP
ID: 22931525
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
 
LVL 58
ID: 22931547
<<[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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question