Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Report footer fields (Access2003)

    In Report Footer code, I'm parsing a field MiscNoPart and want to display the fields M1 and M1Q results in the report footer. How do I name the fields to get them to display. If I use a standard text box, the result thinks it's coming from the report query and puts up an input box when the report runs.


    Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer) 'count misc materials for report footer
    Dim Order_Number As String
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("backlog-scrub-all-rev5", dbOpenDynaset)
    Do While Not rst.EOF
    rst.Edit
    gc = InStrRev(MiscNoPart, "x")
    OrderNumber = rst![Order_Number]
    Misc = rst![MiscNoPart]
    gch = 10
    gc2 = InStrRev(MiscNoPart, "x", gch)
    m1 = Mid(MiscNoPart, gc + 1, 10)
    m1q = Mid(MiscNoPart, gc - 2, 2)
    .
    .
    .
    .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report footer fields (Access2003)

    I don't understand why you're opening a recordset, looping through the records and editing them in the On Format event of the report footer. Where does MiscNoPart come from? Is it a field in the report's record source, or is it a field in the recordset?

  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report footer fields (Access2003)

    The field is in every record but it's length and content changes with each record. Some fields could be 200+ characters long, some zero. Sample field content below:
    1x1450-00072 1x1450-00073 1x1475-00068 2x6050-00048 4x6051-00034
    The code will parse each occurance of "x" and create "buckets" of like part numbers (xxxx-yyyyy) and total the qty. I want to then display the buckets and quantity in the footer.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report footer fields (Access2003)

    Yechhh - what an absolutely disgusting data format! <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15>

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report footer fields (Access2003)

    This line in the report detail was intended like a memo line. The line (and table) is generated while scrubing an Excel report. One user of the report asked if all the memo content could be "counted" for each part# listed and placed in the footer. Adding this function is not critical path. If unable to display strings in footer generated with footer code, this problem goes away.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report footer fields (Access2003)

    If you can still alter the design, it would be better to add each part to a separate record, like this:

    <table border=1><td align=center>ID</td><td align=center>Quantity</td><td align=center>PartNumber</td><td align=right>37</td><td align=right>1</td><td>1450-00072</td><td align=right>37</td><td align=right>1</td><td>1450-00073</td><td align=right>37</td><td align=right>1</td><td>1475-00068</td><td align=right>37</td><td align=right>2</td><td>6050-00048</td><td align=right>37</td><td align=right>4</td><td>6051-00034</td><td align=right>42</td><td align=right>3</td><td>1475-0068</td><td align=right>...</td><td align=right>...</td><td>...</td></table>
    That makes it much easier to summarize the data.

  7. #7
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report footer fields (Access2003)

    Your solution looks interesting. Are you suggesting creating a seperate 2 field table with the part# and qty. Creating that during the scrub of the Excel file prior to creation of the "memo" field is probably ok.
    How does the summary table then get to the footer if my assumption is correct.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Report footer fields (Access2003)

    If your backlog-scrub-all-rev5 table contains just one record, the new table to create would contain just two fields: part number and quantity.
    If backlog-scrub-all-rev5 contains more records, you'd need at least one extra field in the new table to identify which records belong together.

    To display a summary in the report footer:
    - Create a totals query based on the new tables that groups by part number and sums the quantities.
    - Create a small report based on this query.
    - Place this report as a subreport in the report footer of the existing report.

  9. #9
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Report footer fields (Access2003)

    I have the query working already and will get the summary report working. Have not inserted a sub-report before but should be straight forward. Thanks for the help.

Posting Permissions

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