Open report with filter 2

[enduserextra]

I'd like to add a button to a form that will print preview a report that shows all the records for the current record ID only. Is there a way to do this? The ID is in tableA and the corresponding information is in tableB.

I created a macro and it prompts to enter the ID number you want to view the information for in the report. I don't want it to prompt, just show the report by pulling the ID from the current record that is displayed in the form.

Here's the code:

Private Sub CommandButton_Click()
On Error GoTo Err_CommandButton_Click

Dim stDocName As String

stDocName = "tableA"
DoCmd.OpenReport stDocName, acPreview

Exit_CommandButton_Click:
Exit Sub

Thanks!

 

[PHV]

Something like this ?
DoCmd.OpenReport stDocName, acPreview, , "NameOfTableColumn=" & Me![Name of control]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[enduserextra]

Thanks for the quick response!

If I understand you correctly "NameOfTableColumn" I should replace with the column name I want it to pull info from but what is Name of Control??? Sorry I'm new to this.

 

[PHV]

NameOfTableColumn is the name of the field in the underlaying query of the report you want to filter.
Name of control is the name of the control (eg TextBox) holding the value the above field must meet.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[enduserextra]

PHV, Thanks for being patient with me.

This causes a pop-up to open and ask which field I want to sort on. I don't want a prompt, I just want it to pull from a specific field in the form. Also, how does it tell which table to pull the field from, as this form is a conglomeration of fields from different tables?

Thanks again!

 

[PHV]

I just want it to pull from a specific field in the form.
Name of control is precisely the name of the control bounded to this specific field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[enduserextra]

Could you give me an example of what you mean?

 

[PHV]

Can you please post the SQL of the underlaying query of the report, and the names of the form and of the control in this form that holds the ID.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Tsumara]

Howdy
I think I'm having a simular problem. I have a form and a report based on a query. My form is in continuous view. I have a command button that opens the report on the form. I want the report to only display the records for a specific vendor that has focus or is filtered. I've tried a couple of variations of code on my command button, but its still not working. Below is the code for my query and command button.
Query

SELECT tblLEIcontainers.[LEIcont#], tblLEIcontainers.LEIwasteCode, tblLROLWasteCodes.[Vendor#], tblLROLWasteCodes.[VendApproval#], tblDVcompanies.VendorCompany, tblLEIcontainers.WasteType, tblLROLWasteCodes.WasteDesc, tblLEIcontainers.StartDate, tblLEIcontainers.CompleteDate, tblLEIcontainers.Weight, tblLEIcontainers.ContType, tblLEIcontainers.ContVolume, tblLEIcontainers.CompleteUser, tblLEIcontainers.ShipDate, tblLEIcontainers.[ShipPaper#], tblLEIcontainers.ShipUser, tblLEIcontainers.Shipper, tblLEIcontainers.Destination, tblLEIcontainers.DestOLD
FROM tblDVcompanies RIGHT JOIN (tblLROLWasteCodes RIGHT JOIN tblLEIcontainers ON tblLROLWasteCodes.LROLWasteCode = tblLEIcontainers.LEIwasteCode) ON tblDVcompanies.[Dvendor#] = tblLROLWasteCodes.[Vendor#]
WHERE (((tblLEIcontainers.CompleteDate) Is Not Null) AND ((tblLEIcontainers.ShipDate) Is Null));

Command Button
code]
Private Sub cmdPreviewToShiprpt_Click()
On Error GoTo Err_cmdPreviewToShiprpt_Click

Dim stDocName As String

stDocName = "rptLEIcontainersToShip"
DoCmd.OpenReport stDocName, acPreview, , "qryLEIcontainersToShip.Vendor#" & Forms![frmLEIcontainersToShip]![Vendor#]

Exit_cmdPreviewToShiprpt_Click:
Exit Sub

Err_cmdPreviewToShiprpt_Click:
MsgBox Err.Description
Resume Exit_cmdPreviewToShiprpt_Click

End Sub
[/code]
Any Help would be greatly appreciated

 

[PHV]

Have you tried to replace this:
"qryLEIcontainersToShip.Vendor#" & Forms![frmLEIcontainersToShip]![Vendor#]
By this ?
"qryLEIcontainersToShip.[Vendor#]='" & Forms![frmLEIcontainersToShip]![Vendor#] & "'"
If Vendor# is defined as numeric in qryLEIcontainersToShip then get rid of the single quotes

 

Thanks for the snappy post PHV.

I tried .
"qryLEIcontainersToShip.[Vendor#]='" & Forms![frmLEIcontainersToShip]![Vendor#] & "'"
and I get an error message,
"Data type mismatch in criteria expression."

That mismatch is a little odd concidering that they share the source.

I changed Vendor# to VendorCompany and it works great.

"qryLEIcontainersToShip.[VendorCompany]='" & Forms![frmLEIcontainersToShip]![VendorCompany] & "'"

Thanks A lot PHV! That was driving me nuts.

 

[PHV]

To avoid the "Data type mismatch in criteria expression." message:
qryLEIcontainersToShip.[Vendor#]=" & Forms![frmLEIcontainersToShip]![Vendor#]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[socomfort]

Hello:

I am a novice user and have been perusing the FAQs and posts for some time. I am having a small problem with a report. The issue is very similar to the problem Tsumara reported.

The difference is that I have a subreport [rptSubMIP from qryRptMIPDetail] inside of a main report [rptMIP from qryRptMIPHeader]. I have attached a cmd button which permits users to view a report for the current record on a form.

The button code follows:

Private Sub cmdMIPReport_Click()
On Error GoTo Err_cmdMIPReport_Click

Dim strDocName As String
Dim strWhere As String

strDocName = "rptMIP"
strWhere = "MIPid = " & Me.MIPid

DoCmd.OpenReport strDocName, acViewPreview, , strWhere
DoCmd.OpenREport "rptMIP", acViewPreview

Err_cmdMIPReport_Click:
MsgBox Err.Description

End Sub

While both queries pull MIPid, the filter only applies to rptMIP, because I can see the return values in the report body. I get the filtered result for the Header but multiple line items in the Detail.

The MEMBERS collection in my code is telling rptMIP to filter for MIPid. I am not sure how to let the filter flow through to rptSubMIP.

I am guessing that I may need a control reference either in rptMIP or in rptSubMIP, but I really don't know how to do this.

Reports!rptMIP!rptSubMIP.MIPid = Reports!rptMIP.MIPid ??

Any help you can provide this novice user is greatly appreciated.

 

[Pampers]

Hi socomfort,
This is the code i use to printpreview a current report( with subreport); maybe you can use it
Private Sub cmdFreightList_Click()
On Error GoTo Err_cmdFreightList_Click

Dim stDocName As String
Dim stWhere As String
stDocName = "rprtFreightList"
stWhere = "OperationsID=" & Me.operationsID
DoCmd.OpenReport "rprtFreightList", acPreview, , stWhere

Exit_cmdFreightList_Click:
Exit Sub

Err_cmdFreightList_Click:
MsgBox Err.Description
Resume Exit_cmdFreightList_Click
End Sub


Pampers.

You're never too young to learn

 

[socomfort]

Thanks for the reply Pampers. As I was reading your email, I realized my problem. Somehow, the Master / Child link between my reports got nuked. Thanks again for your help!