CODEWITHSUNDEEP
ms-accessms-access-2007vba
user2642406
user2642406

Reputation: 13

How do I Create a report displaying the results of a query controlled by a form

I have a form that "determines the criteria" to be displayed from a query. here is the code - works just how i want it to:

    Option Compare Database
    Option Explicit
    Private Sub Command47_Click()
    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"
    If Not IsNull(Me.Combo26) Then
    strWhere = strWhere & "([Responsible Operator] Like ""*" & Me.Combo26 & "*"") AND "
    End If
    If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & "([DateInitiated] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
    End If
    If Not IsNull(Me.txtEndDate) Then
    strWhere = strWhere & "([DateInitiated] < " & Format(Me.txtEndDate, conJetDate) & ") AND "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Else
    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True
    End If
    End Sub

    Private Sub cmdReset_Click()
    Dim ctl As Control
    For Each ctl In Me.Section(acHeader).Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    ctl.Value = Null
    Case acCheckBox
    ctl.Value = ""
    End Select
    Next
    Me.FilterOn = True
    End Sub

    Private Sub Exit_Click()
    DoCmd.Close
    End Sub

    Private Sub Review_Click()
    Dim carnum As Long
    carnum = Me.CARNumber
    DoCmd.OpenForm "CARData Form", , , "CARNumber = " & carnum
    End Sub

    Private Sub PrintPerformance_Click()
    DoCmd.OpenReport "Rpt1 Performance", acViewPreview
    End Sub

I created a print option on the form, and would like to display the results of the records shown on that form, in a report. I created a report linked to the form via the print option, but can't get it to display the the current results of the form. The actual source for the report is the same source query as for the form. I am guessing that is not correct - How do I extract the data from the form to the report? Thanks

Upvotes: 1

Views: 1059

Answers (1)

HansUp
HansUp

Reputation: 97101

It seems the form's code builds a Filter string and applies it to the form's record source. Later you want to open the report with that same filter applied to the report's record source. If that is true, use the form's Filter property as the WhereCondition to OpenReport.

Private Sub PrintPerformance_Click()
    DoCmd.OpenReport "Rpt1 Performance", acViewPreview, _
        WhereCondition:=Me.Filter
End Sub

Upvotes: 0

Related Questions