Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

cross tab query to a report 2

Status
Not open for further replies.

mustangirl

Programmer
Aug 16, 2001
27
US
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
 
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
 
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")) 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")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!GsDataFrm!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!GsDataFrm!EndingDate") _
= 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
 
Thank you very much! I will let you know if i get it working!!

shawndra
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top