Form query to report vba error

Posted on 2004-04-29
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.


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.
        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.

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.

End Sub

Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
    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.)

    'Initialize variables.

End Sub
Question by:eaglecmg
LVL 119

Accepted Solution

Rey Obrero earned 500 total points
ID: 10950555
Check your references.
See if you have Microsoft DAO 3.x Object library included

Author Comment

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.


Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now