Results 1 to 12 of 12
  1. #1
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49

    TOC with more than one field used

    I've been able to create a module that pulls in and creates a table of contents. However, it will only allow me to update one field with page numbers the item is found on. I would like to have it create page numbers for the subfields below. Basically, I have a main sort by "category" which gives me my page numbers, but I would also like to generate page number for each "title" that falls below the category. I used this code to create my TOC:

    Option Compare Database


    Option Explicit
    Dim db As DAO.Database
    Dim TOCTable As DAO.Recordset
    Dim intPageCounter As Integer

    Function InitTOC()
    'Called from OnOpen property of the report.
    'Opens the database and the table for the report.
    Dim qd As DAO.QueryDef

    Set db = CurrentDb()
    'Resets the page number back to 1
    intPageCounter = 1
    'Delete all previous entries in Table of Contents table.
    Set qd = db.CreateQueryDef("", "Delete * From [Table of Contents]")

    qd.Execute
    qd.Close

    'Open the table.
    Set TOCTable = db.OpenRecordset("Table Of Contents", dbOpenTable)
    TOCTable.Index = "Category"

    End Function
    Function UpdateToc(TocEntry As String, Rpt As Report)
    'Call from the OnPrint property of the section containing
    'the Table of Contents Category field.
    'Updates the Table Of Contents table.
    TOCTable.Seek "=", TocEntry

    If TOCTable.NoMatch Then
    TOCTable.AddNew
    TOCTable!Category = TocEntry
    TOCTable![Page Number] = intPageCounter
    TOCTable.Update
    End If
    End Function


    Function UpdatePageNumber()
    intPageCounter = intPageCounter + 1
    End Function


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Can you provide db for analysis? Follow instructions at bottom of my post.

    For future, please post code between CODE tags to retain indentation and readability.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49
    I've attempted to upload a database that's been stripped down to what I believe is needed for analyzing but cannot meet the minimum size allowed to upload. I'll just deal with what I have going in it now. It functions to get one level of TOC but just cannot get the correct code to pull another level. I want a TOC grouped by "category" that finds the page number and matches it which is working. I have the next level under that listed but cannot get the page numbers to attach to that 2nd level. It only gives me my "titles". I'm happy to see at least the titles are there, but sure was hoping to get the page numbers along with them. Thanks, June7 for trying to help.

  4. #4
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49

    DB added for review

    I hope this finally works. I have only those files created that I believe are needed to figure this out. I'm wanting to get 2 levels of page numbering to show up on the TOC page. It works perfectly for the level 1, category, and lists the level 2 titles, but no page numbering on the titles level.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Would have been nice to have a set of dummy records to work with but I added some.
    Neither rptIndex nor ContentsReport will show any TOC page numbers. PageNumber textbox is not displaying value. Weird. I had to build a new ContentsReport.

    Why does rptIndex have a textbox bound to PageNumber field which doesn't exist in the RecordSource?

    Why are there two ingredients fields?

    TableOfContents table only gets all categories if I move to last page of CookbookContentsOnly report. Annoying.

    Advise not to use spaces in naming convention.

    Didn't include LowerRoman() function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49

    Updated db file

    I'm sorry, June7, for stripping too much out of the db. I was advised to do this so I could send the file through. I made several attempts to upload and the file just was too large. I received help from someone else in the forum on how to clear more and the file was finally able to go through. I had to create a new database and put only the items in it that I felt had to be there. Again, I'm so sorry for the inconvenience. Yes, to have the TOC put the page numbers in, you must do the print review and go to the last page. It's the only way it worked so all corresponding page numbers would be inserted into the Table of Contents table therefore updating the contents and index reports as well. The reason there is textbox for the PageNumber is because that's a listing of the second level of TOC I was hoping to achieve. Nothing I did worked. I left it there as a reminder to manually insert those when the project is finished, but I really hoped to get a code or make changes to whatever needs done so it would work. I'm not sure what you are talking of when you mention naming convention. I've created other database structures but I'm far from understanding it as well as most of you here do. I still consider myself a beginner. The reason for the two ingredients fields was to have them fit with less page length space when the reports were printed out. I didn't want to use columns because all entries would be so different. Therefore, I chose to create two. I know it's an inconvenience, but it works well for me. I've added back in some of what I had taken out and am uploading a revised db. Perhaps this will help you understand more of the problems I've experienced. The TOC code was one I found on youtube which I previously shared here in the forum, thus the reason the report must be ran and then viewed at the final page for it to update the files.
    Attached Files Attached Files

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I worked through issues of first posted db and got it to work. Now looking at expanded functionality.

    By naming convention I mean names of everything - fields, tables, queries, forms, reports.

    Conventional approach for data structure would have a related dependent table for ingredients. Each ingredient would be a record, not multiple elements in one field.
    But if what you have works for you, go with it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49
    I figured out after thinking about it what you meant on the naming convention so I played with the test db I just added and started changing things. It's all working this way once I found everything that needed changed. Been a secretary for about 40 years so it's hard for me to not leave spaces. I'll remember this in the future. Thanks for the tip!

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Here is a version I think does what you want.
    dbCookbook3.accdb
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49
    I've been able to take your db and add in the other items I had along with each of the records in the original table, and it's working perfectly except a couple of things. I noticed that the pagination was stopping every time it found a Title with an apostrophy (') in the tile, i.e. Grandma's Peanut Butter Fudge. I started removing the apostrophies (') out of the field entry in the titles and as I did, the pagination would continue. Once they were gone, the reports worked exactly as I had hoped. I also had to edit the Category entry and insert the List Items. I saved the code that the Entry form was showing in the "category" field when I tried using it rather than the list items I had previously had to choose from. Is there a reason I should have left the code that showed up? Another issue I'm noticing is that every time I click to save the db, it's not giving me a Save option. I have to use the Save As in order to save the db. Is there something I'm missing that's now causing it to not just operate on the Save?

    I just cannot thank you enough for what you have given me. Not only was I able to get things to work and pages to function to what I wanted them to be, but I was able to go in and edit what I had other than what I sent to you and it's looking awesome! I only need to find out these few things I've noticed and brought to your attention. Once those are corrected, I'm good to go unless I notice any other issues as I continue to add to the files. Thank you very much!

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    One way to handle apostrophes is to 'escape' them by doubling so SQL statement will process as normal text not as delimiter characters:
    Code:
    CurrentDb.Execute "UPDATE RecipeTable SET TitlePageNum = " & intTitlePageCounter & " WHERE Title = '" & Replace(Reports!CookbookContents.Title, "'", "''") & "'"
    
    I did modify Category dropdown list to use table as source instead of static value list. Normally, I would not build lookups in tables and I could have simply removed dropdown setting in table since it was not relevant to primary issue. I use comboboxes and listboxes on form. You did not provide form in sample db. Not clear to me what code you are referencing, since there wasn't a form.

    I have no problem saving db. No idea why that would be an issue.
    Last edited by June7; 10-04-2019 at 06:12 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    NewbyForAccess is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2015
    Posts
    49
    Thanks for the help again, June7. I was able to switch the code and it's working exactly how I want, but when I tried running the book, it's showing me there's a data mismatch and doesn't finish the print preview. I've had this problem off and on and have yet to understand it and why it does this because it seems to be hit and miss. I thought the second sample database I uploaded had the form, but I may have overlooked it. I did go in and create the combobox and it's working just fine. I had used them before, but it has been a few years. I've copied one of the reports and was able to get a slightly different one. Now if I can get the data mismatch that it shows is an event in the On Print event to stop acting up, I'll be good to go. Everything else is exactly what I wanted. Thanks so much!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 21
    Last Post: 09-29-2017, 01:30 PM
  2. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  3. Replies: 17
    Last Post: 03-16-2014, 10:52 PM
  4. Replies: 3
    Last Post: 12-27-2013, 02:33 PM
  5. Replies: 1
    Last Post: 03-03-2012, 10:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums