Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Form query to report vba error

Posted on 2004-04-29
2
Medium Priority
?
736 Views
Last Modified: 2008-02-07
I am designing a form query that will use a calander to select a date range and other criteria to a report automatically.  The calander part of the form seems to be working.  The report however is giving me a compile error type not defined on my query defintions.  This code came from a solutions developer database as a starting point.  My knowledge of vba is very limited and I am not sure why this error is happening.  I also need to know what I have to add to the form and report to add addtional criteria.  The table itself has 64 fields and we will only need to pull 26 on a regular basis is there anyway to define the 26 fields that will be pulled in the report code or do we have to pull on 64 fields since they are in the table?  I have posted the report code below.  I can send you the rest of the code if needed.  Thanks for any help you can give in finishing this product.

eaglecmg



Private Sub Detail_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 Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim intX As Integer
    Dim lngRowTotal As Long

    ' If PrintCount is 1, initialize lngRowTotal 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 Detail_Retreat()
' Always back up to previous record when detail section retreats.
    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.
    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_Close()
On Error Resume Next

    ' Close recordset.
    rstReport.Close

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 by using criteria entered in
    ' QASDataPull form.

    Dim intX As Integer
    Dim qdf As QueryDef

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

Exit Sub
    End If

    ' Set database variable to current database.
    Set dbsReport = CurrentDb

    ' Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("QASDataPull")

    ' Set parameters for query based on values entered
    ' in QASDataPull form.
    qdf.Parameters("Forms!QASDataPull!BeginningDate") _
        = Forms!QASDataPull!BeginningDate
    qdf.Parameters("Forms!QASDataPull!EndingDate") _
        = Forms!QASDataPull!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 ReportFooter_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 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

End Sub
0
Comment
Question by:eaglecmg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 10950555
Check your references.
See if you have Microsoft DAO 3.x Object library included
0
 

Author Comment

by:eaglecmg
ID: 10950592
Update to above question.

This report does not need all of the total functions built into the code above.  Can this code be simplified to just display the data from the table using the 26 fields we need to see using the query?  The above code is tied to a crosstab query and report.  We just need these to be select query and report since we do not need any calculations done on the data.

Any help you can give will be very appreciated.

eaglecmg
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question