Dynamics Community Forum Thread Details

Report Writer with VBA calculated fields ignored

In GP2010 I'm modifying the 'Historical Stock Status Summary Report by Site' and adding some new fields. The new fields are defined as calculated String fields and are set in the Report_BeforeAF() subroutine. One field is calculated from data on the report already and the others are filled in from an SQL query.

Every thing works as expected except that the calculated fields are all blank for the first item one each page after page 1. Page one prints correctly then one each subsequent page the first line displays none of the calculated fields, the rest of the page displays correctly.

I've printed the report to a text file and get the same results.

Even went so far as to open a text file and print the data to that file as the report runs. That file ends up with the correct data for all records proving the code is being called and supplying the correct data. RW just ignores the calculated fields on the first line of every page.

WTF?????

The Code:

Private Sub Report_BeforeAF(ByVal Level As Integer, SuppressBand As Boolean)

' For testing this is were I opened a text file for append and wrote the Level and the Item number to it
    If Level = 2 Then
        Dim ItemNum As String
        Dim Rs As New ADODB.Recordset
        Dim SQL As String
        Dim Qty As Double, Cost As Double
        Me.kkLastRecCostLASTF2 = "-"
        Me.kkLastRecDateLASTF2 = "-"
        Me.kkOrigCostLASTF2 = "-"
        Me.kkUofMLASTF2 = "-"
        Me.kkAveCOstLASTF2 = "-"

        ItemNum = Trim(Me.ItemNumberLASTF2.Value)

        Cost = CDbl(Me.ExtendedPriceSUMF2)
        Qty = CDbl(Me.TRXQTYSUMF2)

        If Qty <> 0 Then
            Me.kkAveCOstLASTF2 = Format(Cost / Qty, "###,##0.000")
        Else
            Me.kkAveCOstLASTF2 = "-"
        End If
        SQL = "SELECT top 1 last_originating_cost, lrcptcst, lsrcptdt"
        SQL = SQL & " FROM IV00103"
        SQL = SQL & " WHERE ITEMNMBR ='" & ItemNum & "' ORDER BY lsrcptdt Desc"
        Rs.Open SQL, kkCommon.GetCon
        If Not Rs.EOF Then
            Me.kkLastRecDateLASTF2 = Rs.Fields("lsrcptdt").Value
            Me.kkLastRecCostLASTF2 = Format(Rs.Fields("lrcptcst").Value, "###,##0.000")
            Me.kkOrigCostLASTF2 = Format(Rs.Fields("last_originating_cost").Value, "###,##0.000")
        End If
        Rs.Close
        Rs.Open "select baseuofm from iv40201 where uomschdl=(select uomschdl from iv00101 where itemnmbr='" & ItemNum & "')"
        If Not Rs.EOF Then
            Me.kkUofMLASTF2 = Rs.Fields(0).Value
        End If
        Rs.Close
        Set Rs = Nothing
   End If