UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dynamic Crosstab Report, Access 2016    
 
   
dmeehanjr
post Oct 2 2019, 02:41 PM
Post#1



Posts: 88
Joined: 15-December 08



i've had a dynamic crosstab report, with varying columns (name and number). I wanted to add a weighted average. On each Detail_format, i put out the data columns, as well as adding each to an array for that column. At report end, i take each column total and divide it by the rows weight.

In stepping thru/Debugginng...i noticed that when Detail_retreat, it moves recordsource to previous; redoes the format, but then added that value again to the totals...thus skewing the avg..

is there way identify this 'move previous' action, so as to not add values again? thnking i could set a global variable...

thanks!
Go to the top of the page
 
June7
post Oct 2 2019, 03:32 PM
Post#2



Posts: 876
Joined: 25-January 16



I've never seen Detail_retreat used.

Need to provide your code for analysis.

This post has been edited by June7: Oct 2 2019, 03:33 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dmeehanjr
post Oct 2 2019, 03:44 PM
Post#3



Posts: 88
Joined: 15-December 08



Its a manually coded routine i've altered for various dynamic crosstab reports over the years.
thanks!

-----------------------------------------------------------------------------------------------------------
CODE
Option Compare Database
Option Explicit

' Constant for maximum number of Element columns query would
' create.
Const conTotalColumns = 10

' Variables for Database object and Recordset.
Dim dbsReport As Database
Dim rstReport As Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim intweight As Long
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim strQuarter As String
Dim strPallet As String


Private Sub InitVars()

    Dim intX As Integer

    ' Initialize lngReportTotal variable.
'    lngReportTotal = 0

    ' Initialize array that stores column totals
      For intX = 1 To conTotalColumns
        lngRgColumnTotal(intX) = 0
       Next intX
      
      intweight = 0


End Sub

Private Function xtabCnulls(varX As Variant)

    ' Test if a value is null.
    If IsNull(varX) Then
        ' If varX is null, set varX to 0.
        xtabCnulls = 0
    Else
        ' Otherwise, return varX.
        xtabCnulls = varX
    End If

End Function

Private Sub Command163_Click()
    
    DoCmd.RunCommand acCmdPrint

End Sub

Private Sub Detail_format(Cancel As Integer, FormatCount As Integer)
    ' Place values in text boxes and hide unused text boxes.

    Dim intX As Integer
    Dim rowWeight As Integer
    
     rowWeight = rstReport.Fields("Weight").Value
     intweight = intweight + rowWeight
  
    
    
    ' Verify that NOT at end of recordset.
    If Not rstReport.EOF Then
        ' If FormatCount is 1, place values from recordset into text boxes
        ' in detail section.
        If Me.FormatCount = 1 Then
            
            For intX = 1 To intColumnCount
                ' Convert Null values to 0.
                Me("Col" + Format$(intX)) = xtabCnulls(rstReport(intX + 9))
                '  calc & Add to column total
                If Me("Col" + Format(intX)) <> "BAL" Then
                 lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + (Me("Col" + Format(intX)) * rowWeight)
                End If
            Next intX

            ' Hide unused text boxes in detail section.
            For intX = intColumnCount + 1 To conTotalColumns
                Me("Col" + Format$(intX)).Visible = False
            Next intX

            ' Move to next record in recordset.
            rstReport.MoveNext
        End If
    End If
    
  

End Sub







Private Sub Detail_Retreat()
    rstReport.MovePrevious

End Sub



Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Dim intX As Integer

    ' Put column headings into text boxes in page header. strip off sort numbers
  '    Mid(rstReport.Fields(intx+7).Name, 3, Len(rstReport.Fields(intx+7).Name))
    For intX = 1 To intColumnCount
       Me("Head" + Format$(intX)) = Mid(rstReport.Fields(intX + 9).Name, 3, Len(rstReport.Fields(intX + 9).Name))
   '     Me("Head" + Format$(intX)) = rstReport(intX + 7).Name
    Next intX

    ' Hide unused text boxes in page header.
    For intX = (intColumnCount + 1) To conTotalColumns
        Me("Head" + Format$(intX)).Visible = False

    Next intX


End Sub

Private Sub Report_Close()

    On Error Resume Next

    ' Close recordset.
    rstReport.Close

End Sub

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "There is no data for this report. Canceling report..."
    Cancel = -1

End Sub
Private Sub Report_Open(Cancel As Integer)
    ' Create underlying recordset for report

    Dim intX As Integer
    Dim qdf As QueryDef
    Dim Frm As Form
    
    ' Set database variable to current database.
    Set dbsReport = CurrentDb

    ' Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("qrySalesReleaseElementAnalysis_Crosstab3dig")
    ' Set parameters for query based on values
  
    qdf.Parameters("[Forms]![frmSalesReleasesPopUp]![SalesReleaseID]") _
        = [Forms]![frmSalesReleasesPopUp]![SalesReleaseID]
'   qdf.Parameters("[Forms]![frmSalesReleasesPopUp]![sbfrmSalesReleaseDetails].[Form]![cboProduct]") _
'       = [Forms]![frmSalesReleasesPopUp]![sbfrmSalesReleaseDetails].[Form]![cboProduct]
    'qdf.Parameters("Forms!EmployeeSalesDialogBox!EndingDate") _
    '    = Forms!EmployeeSalesDialogBox!EndingDate

' Open Recordset object.
    Set rstReport = qdf.OpenRecordset()

' check if over max # of elements (10 + 8 other fields in query)
    If rstReport.Fields.Count > 20 Then
       Cancel = True
        MsgBox "Query returned more than Report Max of 10 Elements." _
            , vbExclamation
        Exit Sub
    End If
' Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count - 10

End Sub



Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
     Dim adoConnect As ADODB.Connection
    Dim adoCommand As New ADODB.Command
    Dim adoRS As ADODB.Recordset
    Dim intX As Integer
    Dim lngcustomerid As Long
    Dim lngThisProductID As Long
    Dim strMessage As String
     lngcustomerid = Forms![frmSales]![cboCustomerID]
    lngThisProductID = Forms![frmSalesReleasesPopUp]![sbfrmSalesReleaseDetails].Form![cboProduct]
    
      For intX = 1 To intColumnCount
    '    If lngRgColumnTotal(intX) / intweight <> 0 Then
         Me("Tot" + Format(intX)) = lngRgColumnTotal(intX) / intweight
    '    End If
      Next intX
      
       '  Hide unused text boxes in report footer.
    For intX = intColumnCount + 1 To conTotalColumns
        Me("Tot" + Format(intX)).Visible = False
    Next intX
    
    
      'get connection
    Set adoConnect = Application.CodeProject.AccessConnection
     ' check customer docs using customer and product
    With adoCommand
            .ActiveConnection = adoConnect
            .CommandType = adCmdText
            .CommandText = "SELECT tblCustomerDocs.*, tblProducts.Product FROM tblProductFamilies INNER JOIN " _
             & "((tblCustomerDocs INNER JOIN qryCustDOcMAX ON (tblCustomerDocs.DateIssue = qryCustDOcMAX.MaxOfDateIssue) AND " _
             & "(tblCustomerDocs.Product = qryCustDOcMAX.Product) AND (tblCustomerDocs.ProductFamily = qryCustDOcMAX.ProductFamily)) " _
             & "INNER JOIN tblProducts ON qryCustDOcMAX.Product = tblProducts.ProductID) ON tblProductFamilies.ProductFamilyID = " _
             & "tblProducts.ProductFamilyID WHERE tblCustomerDocs.Customer = " & lngcustomerid & " And tblCustomerDocs.Product = " _
             & lngThisProductID & " ORDER BY tblProductFamilies.ProductFamily, tblProducts.Product;"

            Set adoRS = .Execute
    End With
    
    If adoRS.RecordCount > 0 And (Forms![frmSales]![cboCustomerID] = 52 Or Forms![frmSales]![cboCustomerID] = 221) Then
'     and Forms![frmSalesReleasesPopUp]![sbfrmSalesReleaseDetails].Form![cboProduct] = 146 Then
      strMessage = "Conforms to Vista Metals " & adoRS.Fields("DocRef") & " dated " & adoRS.Fields("DateIssue")
       Me.txtCustDoc.Value = strMessage
    End If
    
    
    adoRS.Close
    Set adoRS = Nothing
    Set adoCommand = Nothing
    adoConnect.Close
    Set adoConnect = Nothing
  
End Sub

Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)
    
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  
    ' Move to first record in recordset at beginning of report
    ' or when report is restarted. (A report is restarted when
    ' you print a report from Print Preview window, or when you return
    ' to a previous page while previewing.)
    rstReport.MoveFirst

    'Initialize variables.
    InitVars
    If Forms!frmSalesReleasesPopUp.chkRev = True Then
     Me.lblRev.Caption = "REVISED"
    End If

End Sub

Go to the top of the page
 
isladogs
post Oct 2 2019, 04:42 PM
Post#4


UtterAccess VIP
Posts: 1,701
Joined: 4-June 18
From: Somerset, UK


I've used dynamic crosstab queries for many years and have also never used this event
What is its purpose in your crosstab reports?

--------------------
Go to the top of the page
 
dmeehanjr
post Oct 2 2019, 05:23 PM
Post#5



Posts: 88
Joined: 15-December 08



i;ve used this from..https://support.microsoft.com/en-us/help/328320/how-to-create-a-dynamic-crosstab-report-in-access-2002

also seen it customized in bunch of other places in forums. i believe the retreat happens as it does a 1st pass to check formatting/spacing...before it actually commits to the print?
or something like that...?
Go to the top of the page
 
isladogs
post Oct 2 2019, 05:30 PM
Post#6


UtterAccess VIP
Posts: 1,701
Joined: 4-June 18
From: Somerset, UK


This link is the MS explanation Section: OnRetreat
As I mentioned I've never used it
Disable it temporarily, Do your report averages now work as intended? Does anything else fail by doing this?

--------------------
Go to the top of the page
 
dmeehanjr
post Oct 2 2019, 05:36 PM
Post#7



Posts: 88
Joined: 15-December 08



throws a 3021 run time...no current record -
Go to the top of the page
 
isladogs
post Oct 2 2019, 05:56 PM
Post#8


UtterAccess VIP
Posts: 1,701
Joined: 4-June 18
From: Somerset, UK


OK so its obviously an integral part of your code which I haven't yet studied.
However I'm just about to log off as its midnight here
Hopefully someone else can review your code

--------------------
Go to the top of the page
 
June7
post Oct 2 2019, 08:58 PM
Post#9



Posts: 876
Joined: 25-January 16



That's a lot of code and trying to understand and follow behavior just from reading is difficult. I don't want to try to build a db to use it. Can you provide db?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
dmeehanjr
post Oct 9 2019, 10:11 AM
Post#10



Posts: 88
Joined: 15-December 08



i figured out a solution - i set a global boolean on the 'retreat' event, and value it true when event occurs - so in detail format event, it does not add again to totals.

I tried first setting using "format Count", as allegedly ms doc says would increment each time a detail line was formatted...but it did not go to 2, even though watching the value while stepping thru code in debug - so it would "retreat" back to format the same recordsource data row, and format the same detail line on report.

still do not understand why the me.formatcount did not increment to 2..
Go to the top of the page
 
isladogs
post Oct 9 2019, 11:25 AM
Post#11


UtterAccess VIP
Posts: 1,701
Joined: 4-June 18
From: Somerset, UK


Glad you've found a solution but I would still suggest you use different code for your dynamic crosstab reports.
I have many examples none of which use code as long /complex as yours.
For example, see Extended File Properties for a much simpler approach

--------------------
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th October 2019 - 04:09 PM