Solved Pass Form Filtered Records to a Report (1 Viewer)

kengooch

Member
Local time
Today, 10:46
Joined
Feb 29, 2012
Messages
137
I am trying to pass the filtered results of a Form to a Report. The Form has a Combo Box that obtains it's data from the tDept Table which has two fields. tDeptAbbrv and tDeptNm Sample Values:
tDeptAbbrv tDeptNm
ANC Ancillary Testing
Autop Autopsy
BB Blood Bank

tDeptAbbrv is linked to tPlcydpt in the Main tPolicy Table that is used to build the form.
When you select the tDeptAbbrv item it filters the records on the fDept form by tPlcydpt
The ComboBox has the Following Code to filter the records on the fDept form is...

Code:
Private Sub luDept_AfterUpdate()
On Error GoTo jError
    DoCmd.SearchForRecord , "", acFirst, "[tDeptAbbrv] = " & "'" & Screen.ActiveControl & "'"
    luDept = Null
    DoCmd.GoToControl "luPlcyNm"
    GoTo jEnd
jError:
    MsgBox Error$
    Resume luDept_AfterUpdate_Exit
jEnd
End Sub

There is a Print Button on the form that then call's the rPolicyReport that I want to show the same records as display on the filtered form above.
This button contains the following Code
Code:
Private Sub bPlcyCmplt_Click()
'This Code Handles the Report based upon the Filter selections
MsgBox "pause"
    DoCmd.OpenReport "rPolicyComplete", acViewReport, , "tPlcydpt= " & [tPlcydpt]
    With Reports("rPolicyComplete")
        .OrderBy = "tplcyspID"
        .OrderByOn = True
    End With
End Sub

When I click the Print button it shows all of the records instead of just the filtered records.
Obviously I'm missing something somewhere, any help would be appreciated.

thanks in advance for your help
 

Ranman256

Well-known member
Local time
Today, 13:46
Joined
Apr 9, 2015
Messages
4,339
docmd.OpenReport "rMyRpt",acViewPreview , ,"[id]=" & txtID
 

kengooch

Member
Local time
Today, 10:46
Joined
Feb 29, 2012
Messages
137
Thanks for such a quick response! I put the code in accordingly and it pops up a msgbox asking for a parameter value Autop, if I ignore this it shows a blank report, if I type in the value I want here it then shows the correct report. This unique key is a text field, do I need to use single quotes or something?

DoCmd.OpenReport "rPolicyComplete", acViewReport, , "[tPlcydpt]=" & tPlcydpt
 
Last edited:

Cronk

Registered User.
Local time
Tomorrow, 04:46
Joined
Jul 4, 2013
Messages
2,770
The syntax is
docmd.OpenReport ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs

Filtername is the name of a query

Try
Code:
DoCmd.OpenReport "rPolicyComplete", acViewReport, , ,"[tPlcydpt]=" & tPlcydpt

or if tPlcydpt is a string
Code:
DoCmd.OpenReport "rPolicyComplete", acViewReport, , , "[tPlcydpt]='" & tPlcydpt & "'"
 

kengooch

Member
Local time
Today, 10:46
Joined
Feb 29, 2012
Messages
137
Hurray!! This worked with the removal of one "," to get the Where Clause back to the correct place in the equation! Thanks so much!!
Here is what worked.
DoCmd.OpenReport "rPolicyComplete", acViewReport, , "[tPlcydpt]='" & tPlcydpt & "'"
 

Users who are viewing this thread

Top Bottom