Results 1 to 8 of 8

Thread: Function Help

  1. #1
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Unanswered: Function Help

    I'm trying to create a table of contents for a report using the process from this link. http://support.microsoft.com/default...b;en-us;131588

    It just describes how to do it with one field. I want multiple fields in my TOC. It should look something like this:


    Action Item #2 Blow, Joe Open 7/20/2006 Page 2
    Action Item #3 Blow, Moe Open 7/20/2006 Page 3
    Action Item #2 Blow, Flo Open 7/20/2006 Page 5
    Action Item #2 Blow, Roe Open 7/20/2006 Page 7
    Action Item #2 Blow, Gertrude Open 7/20/2006 Page 11

    I know this is a basic function thing but I never really got a good grasp on functions. How do I pass the Action Item number, Person Name, Status and Date from the main report, through the UpdateToc() function to the table that the table of contents is based on? The fields are all on the main report and in the table and spelled the same.

    The error I'm getting is 'The expression OnPrint you entered in the event property setting produced the following error: The object doesn't contain the Automation Object 'AI'.' *****(AI is my Action Item field)

    This is what my UpdateToc function looks like right now (there are a few more fields than in my example above):
    Code:
    --------------------------------------------------------------------------------

    Function UpdateToc(SCTicket As String, IssueTitle As String, AI As Long, ActionItemOwner As String, _
    ActionItemStatus As String, ActionItemDueDate As Date, 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!SCTicket = SCTicket
    toctable!IssueTitle = IssueTitle
    toctable!AI = AI
    toctable!ActionItemOwner = ActionItemOwner
    toctable!ActionItemStatus = ActionItemStatus
    toctable!ActionItemDueDate = ActionItemDueDate
    toctable![PageNumber] = Rpt.Page
    toctable.Update
    'End If
    End Function

    --------------------------------------------------------------------------------


    Thanks,

    John

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    What you are doing here looks ok to me in priciple.

    - Check that your toctable contains all the required fields
    - Check you have the right datatype for AI
    - Check that AI is in your report (or at least in your report source), I think this is the most likely cause of your error.
    - What does your UpdateToc statement look like in the OnPrint event?
    - It may be that AI needs to be in the group where you've put the OnPrint. I try to test if I get some time.

    Chris

  3. #3
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Function Help

    Thanks for taking a look Howey.

    - Check that your toctable contains all the required fields
    It does.

    - Check you have the right datatype for AI
    I double-checked all of the datatypes and they match the original table.

    - Check that AI is in your report (or at least in your report source), I think this is the most likely cause of your error.
    AI is in my report source. The textbox was named 'AI #' I changed it to 'txtAI' thinking the '#' could cause trouble.

    - What does your UpdateToc statement look like in the OnPrint event?
    My Update Toc in the onPrint currently looks like this --> =UpdateTOC([SCTicket],[IssueTitle],[txtAI],[ActionItemOwner],[ActionItemStatus],[ActionItemDueDate],[Report])

    - It may be that AI needs to be in the group where you've put the OnPrint. I try to test if I get some time.
    AI is in the group with the OnPrint. Now it's txtAI and I'm getting the same error. A clue might me the fact that whatever field I put in second in the =UpdateToc statement is the one that is named in the error message. That's why I think the function is my problem.

    Thanks again for taking a look Howey.

    John

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I was able to produce a three column toc table fine with two columns coming from text fields in the group level. I also did four columns for good measure.

    I just noticed you have used the variable tocentry as per the example but you are not declaring as an argument in your function declaration. so you need to replace tocentry with SCTicket I think. SCTicket must be an indexed field in your toc table.

    If you're still struggling, maybe you can post your d/b and we can take a look.

    hth
    Chris

  5. #5
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    That's good news, Chris. Now I know it can be done. I guess my mistake was not declaring tocentry as an argument in the function. I'll try that. If not, I'll strip the db down to just this process with a little bit of data. Can you post the code you used? That might be all I need. Thanks.

    John

  6. #6
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21

    Table Of Contents database

    OK, I've been trying for a while now and can't get the function to work if I pass more than one variable. I tried attaching a .zip file of the database but it failed. Can you please post the code you used and how you called it the report groups OnPrint event? I'm sure I'm misssing something elementary. Thanks again for all of your help.

    John
    Last edited by JBWebDatabase; 07-25-06 at 12:38. Reason: Attachment didn't work

  7. #7
    Join Date
    Jan 2004
    Location
    Chicago
    Posts
    21
    I got it! I ended up doing something completely different. My InitToc function (which I didn't include here, but is in the link to Microsoft in my original post) now clears out the table I use as a data source for the Table of Contents report. Then it appends records to that same table that meet my criteria. This table also has a field I update with the page number. That field gets updated in the OnPrint event when the SCTicket and AI number of the currently printing Action Item match the current one as I loop through the table IF that item itself meets my criteria. Basically I ended up using the append query and updating the table in code instead of passing the values. Thanks for the help, even though I took a different path.

  8. #8
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I've attached a simple d/b containing three tables two of which I borrowed from the Northwind d/b because I was lazy.

    I've just seen that you've sorted your probelm but I've done the example anyway.

    Regards
    Chris
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •