cross tab query to a report 2

[mustangirl]

I have a crosstab query. The column headings are times that a logistic routes leave a cross dock to go to our manufacturing facility. I run this query for each cross dock to each facility. Everytime I run it the times change in the column heading and the number of columns(routes) change. I want one report to run all those queries. I want the report to read the columns in the query and place them on the report and then remove them. Is that possible, if so how?

Thanks,
Shawndra

 

[jazzlady]

Mustangirl,
If you are using MS Access 97, the solutions database has a solution for you. The instructions are easy to implement and work beautifully. I use it for a MS Access 2000 database, however the customer decided to upgrage to MS Access XP in June and I am in the process of converting the database to MS Access XP. The code does not work for XP. I fix one error and get another.(still trying to fix) Keep this in mind if you plan to upgrade soon.

Hope this helps.

If anyone has a solution for crosstab report using MS Access XP, please let me know. Thanks

 

[mustangirl]

I have access 2000, can you tell me how to do it?

 

[errolf]

There is a report sample available " Create report with dynamic column headings" It is available on M.S knowledge page. However here is the code.

Option Compare Database 'Use database order for string comparisons.
Option Explicit

' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column.
Const conTotalColumns = 14

' 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 lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

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

Dim intX As Integer
' 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 - 1))
Next intX

' Hide unused text boxes in detail section.
For intX = intColumnCount + 2 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 Detail1_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in detail section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Place row total in text box in detail section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub

Private Sub Detail1_Retreat()

' Always back up to previous record when detail section retreats.
rstReport.MovePrevious

End Sub

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

End Sub

Private Sub PageHeader0_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).NAME
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

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


Private Sub Report_Activate()

' Hide built-in Print Preview toolbar.
'DoCmd.ShowToolbar "Print Preview", acToolbarNo

End Sub

Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub

Private Sub Report_Deactivate()

' Show built-in Print Preview toolbar.
'DoCmd.ShowToolbar "Print Preview", acToolbarWhereApprop

End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("GsDataFrm&quot) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "GsDataFrm in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!GsDataFrm
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("IanExcelTransQry1&quot
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!GsDataFrm!BeginningDate&quot _
= frm!BeginningDate
qdf.Parameters("Forms!GsDataFrm!EndingDate&quot _
= frm!EndingDate

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

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count



End Sub

Private Sub ReportFooter4_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Place column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Place grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX
End Sub


Private Sub ReportHeader3_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

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


Hopes this helps
errolf

 

[mustangirl]

Thank you very much! I will let you know if i get it working!!

shawndra

 

[StefanScheidegger]

Hello,

I am having problems with processing this code. Access/Visual Basic gives me the notification: "error with compiling, user-definied type not defined (sorry this is a translation from German to English). The error seems to be in the part
________________________
Dim dbsReport As Database
Dim rstReport As Recordset
___________________________

Do you have any ideas what could be the reason for this error?`regards, Stefan