Only print records on Access report that have checkbox filled in on form

Hi all.

I'm working on an Access 2003 report who gets called after 2 forms are used.

When the end user is in the form: PinkEditWorkOrder there is a button that opens another form: PalletTag

The data source for the PalletTag form is a stored procedure:

ALTER PROCEDURE PalletTagLabel_Form
	(
		@OrderID varchar(8)
	)
AS
	SELECT     dbo.WorkOrders.OrderID, dbo.WorkOrders.CustomerName, dbo.WorkOrders.Community, dbo.WorkOrderItems.ItemIndex, dbo.WorkOrderItems.ProductID, dbo.WorkOrderItems.FreeDescription, 
                      dbo.WorkOrderItems.Description
FROM         dbo.WorkOrders INNER JOIN
                      dbo.WorkOrderItems ON dbo.WorkOrders.OrderID = dbo.WorkOrderItems.WorkOrderID
WHERE     WorkOrders.OrderID = @OrderID
	RETURN 

Open in new window


The input parameter field in the Data tabfor the PalletTag form is:  OrderID=[Forms]![PinkEditWorkOrder]![OrderIDField]

The data on this PalletTag form is CustomerName, Community etc. but it also has in the Detail section the ProductID and description. I have added a checkbox (checkbox_print) so that every detail line has this checkbox. What I want to happen is when the end user fills in the checkbox then only those productid, description will appear on the report (PalletTagReport) when they click a button on the PalletTag form to display the PalletTagReport.

The data source of the PalletTagReport is a stored procedure:
 
ALTER PROCEDURE PalletTagLabel_Tile
	(
		@OrderID varchar(8)
	)
AS
	SELECT 
                      WorkOrders.OrderID, WorkOrders.ProjectManager, WorkOrders.CustomerName, WorkOrders.Community, WorkOrders.Lot, WorkOrders.Block, WorkOrders.ProductType, WorkOrders.Vendor, WorkOrders.Warehouse,dbo.WorkOrderItems.FreeDescription, 
                      dbo.WorkOrderItems.Description
FROM         WorkOrders  INNER JOIN
                      dbo.WorkOrderItems ON dbo.WorkOrders.OrderID = dbo.WorkOrderItems.WorkOrderID

WHERE     WorkOrders.OrderID = @OrderID
	RETURN 

Open in new window


The input parameter field in the report is: OrderID=[Forms]![PalletTag]![txtOrderID]

I've tried playing with the Filter option in the report properties but I can't get it to simply print the products that have the checkbox filled in the form that opens the report. Any ideas?

Thank you in advance!
Sim1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
If I am understanding your request here, ...The basic syntax for the Report's Recordsourcwe would be something like this:

SELECT * FROM YourTableOrQuery WHERE checkbox_print=TRUE

Or if filtering when the report is Opened:

    DoCmd.OpenReport "YourReport", acViewPreview, , "checkbox_print=TRUE"


...but perhaps another expert can give you the exact syntax based on your object names...

;-)

JeffCoachman
0
Sim1980Author Commented:
But checkbox_print is not in the table where the stored procedure is pulling the data, but it's a field on the form that opens the report. Because there may be times when the end user only wants to print certain products on the report and not all the products.

Let me backtrack a minute, I just realized when I fill in one of the checkboxes then it fills it in for ALL the products in the detail section not the one I actually want to check.
0
Sim1980Author Commented:
I see why this is happening, because my checkbox is unbound. I don't want to add this checkbox to my table because it's just to determine what products to print on the report. The end user may decide 5 products to display on the report now, and then in an hour just want 2 and so on.

Should I add this print checkbox to my table, if multiple people will be using this form and some may want to print different products?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Sim1980Author Commented:
How about if instead of a checkbox in the detail section I bring in the products into a ListBox in MultiSelect mode?
0
Jeffrey CoachmanMIS LiasonCommented:
That would be a better approach, then the code to open the report would look like this:

Dim strCriteria As String
Dim varItemsSelected As Variant

    If Me.YourListBox.ItemsSelected.Count = 0 Then
        MsgBox "You must select at least one record."
        Exit Sub
    End If
   
    For Each varItemsSelected In Me.YourListBox.ItemsSelected
        strCriteria = strCriteria & "," & Me.YourListBox.ItemData(varItemsSelected)
    Next varItemsSelected

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)

    DoCmd.OpenReport "YourReport", acViewPreview, , "YourID IN(" & strCriteria & ")"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sim1980Author Commented:
Ok. I added your code to OnClick button on my form (PalletTag):

Dim strCriteria As String
    For Each varItemsSelected In Me.listb.ItemsSelected
        strCriteria = strCriteria & "," & Me.listb.ItemData(varItemsSelected)
    Next varItemsSelected

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    MsgBox (strCriteria)
    DoCmd.OpenReport "PalletTag_Tile", acViewPreview, , "ItemID IN(" & strCriteria & ")"

Open in new window


Then in my report "PalletTag_Tile", whose data source is a stored procedure:

ALTER PROCEDURE PalletTag_Detail
	(
		@OrderID varchar(8)
	)
AS
	SELECT 
                dbo.WorkOrderItems.ItemID
FROM                         dbo.WorkOrderItems
WHERE     WorkOrderItems.WorkOrderID = @OrderID
	RETURN 

Open in new window


In the "Input Parameters" field in the Data tab of the properties I put:
OrderID=[Forms]![PalletTag]!txtOrderID

Then I put the ItemID field in my detail section, but unfortunately it is still printing ALL the ItemIDs instead of the ones I selected in my list box in the previous form. By the way ItemID is not a text it's int.

Is there a setting I need to change on my report?
0
Sim1980Author Commented:
The problem was with the fact that the source is a stored procedure, so instead I made the report's source a VIEW and it works great now.

Thanks!
0
Jeffrey CoachmanMIS LiasonCommented:
;-)

Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.