Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 740
  • Last Modified:

Form query to report vba error

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
eaglecmg
Asked:
eaglecmg
1 Solution
 
Rey Obrero (Capricorn1)Commented:
Check your references.
See if you have Microsoft DAO 3.x Object library included
0
 
eaglecmgAuthor Commented:
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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now