Report in wrong order

[ellenr]

I am not sure if this is the right forum, but here goes. I have a cool little form for the use of the organization's treasurer wherein she enters checks and deposits. I have a query that generates a running sum, so each line in the accounting report based on the table has the account total as of that date. The query behind the report is exactly what I want and is in the correct order. For example, the four transactions for 9/15/2014 are in the order entered, and the line total is correct. The report insists on listing the 4 in the example in some other order with or without OrderBy specified. With code from someone, perhaps on this forum, I added the following to the OnPage event:

Private Sub Report_Page()
Const TWIPSPERINCH = 1440
 
' Offset from right edge of Control where our
' Vertical Line will start - adjust as you desire.
' Expressed in Twips
Dim intLineMargin As Integer

' Need Control object to loop through Control Collection
Dim CtlDetail As Control

' Y position where we will start Drawing our Grid from
Dim startY As Integer

' Var will be set to the Margin you
' set for the Bottom of the Page
' and Top of the Page via
' the Page Setup Menu
' Expressed in TWIPS.
Dim varBMargin As Variant
Dim varTMargin As Variant

' PrtMips structs
Dim MIPSstr As udtPrtMips_str
Dim MIPS As udtPrtMips

' See Access help for PrtMip
MIPSstr.strRGB = Me.PrtMip
LSet MIPS = MIPSstr

' Copy value into our page Margin vars
varBMargin = MIPS.lngBottom
varTMargin = MIPS.lngTop


'intLineMargin is expressed in TWIPS
intLineMargin = 20

' We need to draw a vertical line at the left and right
' edge of our report. All other vertical lines are to the
' right edge of the existing controls
' You should place the right most control at the extreme
' right edge of your report - intMargin
' Draw Line Left Edge
With Me
' Won't take 0 for the starting Y param - use 1.
Me.Line ((0), 1 + Me.Section(acPageHeader).Height)-(0, .ScaleHeight - _
         (.Section(acPageFooter).Height))

End With

' OK lets draw a vertical line to seperate each field
' for each control in details control
For Each CtlDetail In Me.Section(acDetail).Controls
    With CtlDetail
        If Page = 1 Then
            Me.Line ((.Left + .Width + intLineMargin), 1 + Me.Section(acPageHeader).Height) _
            -(.Left + .Width + intLineMargin, Me.ScaleHeight - (Me.Section(acPageFooter).Height))
        Else
            Me.Line ((.Left + .Width + intLineMargin), 1 + Me.Section(acPageHeader).Height) _
            -(.Left + .Width + intLineMargin, Me.ScaleHeight - (Me.Section(acPageFooter).Height))
        End If
    End With
Next

'CleanUp time
Set CtlDetail = Nothing

End Sub

The result is a beautiful report (albeit in the wrong order) with vertical lines all the way to the bottom of the page.

If I remove the OnPage code, the report is still in the wrong order, so obviously that wasn't the problem. OK, I think it is corrupted. I open a new db, import all into it. Still in wrong order. So next, I begin to recreate the report from scratch. Hooray! Right order! But I still want the vertical lines between the columns. So now, I copy the OnPage code and paste it into the new report's OnPage code. I get error message: "Compile error: User-defined type not defined" with the line "Dim MIPSstr As udtPrtMips_str" highlighted. Ok, I delete all of it and reenter it line for line manually, leaving out the comments. Get the same error message. After trying to research it, the only suggestions were to add Activex reference which can't be the problem, because the other version of the report doesn't throw an error and both are in the same db.

I have attached a clip of the report and a clip of the underlying query. As you can see in the first capture.png, the check numbers are in the wrong order and the amounts in the Total line don't sync with the total above adjusted by the amount on that line.

 

[jdraw]

If you are using a query to set the order for a report, Access does not use that query as you think. Reports have their own Sorting and Grouping, and by the sounds of things, you will have to use Sorting and Grouping to get the order you need.

Good luck.

 

[ellenr]

Thank you, thank you! As many reports as I have written through the years and used Sorting and Grouping, it amazes me how I could possibly forget that! Chalk it up to lack of sleep!

 

[jdraw]

Happy to help.