Command button to filter report and print

[tsamrein]

I have a command button that prints a preview of a report. is there anyway to make this also prompt the user for a Work Order number and use that to filter the report? This is the command button functions.

Private Sub cmdPrintPurchaseRequirements_Filtered_Click()
DoCmd.OpenReport "rptPurchaseRequirements_Selected_3_M", acViewPreview
' DoCmd.OpenReport "rptPurchaseRequirements_Selected_3_M1", acViewPreview
' DoCmd.OpenReport "rptPurchaseRequirements_Selected_3_LMOB", acViewPreview
End Sub

I am thinking I will need to create a new report and run a similar command from button, but a colleague of mine has different thoughts.

Thanks in advance.

[ranman256]

if cboWO is a combo box,

'----------------
sub btnReport_click()
'----------------

If IsNUll(cboWO) Then
  docmd.OPenReport "rMyReport",acViewPreview
Else
  docmd.OPenReport "rMyReport",acViewPreview,,[WorkOrder]='" & cboWO & "'"
End If
end sub

[Micron]

Don't replicate forms/reports for similar end uses; it's not good design practice. If you want this prompt every time the button is clicked, the simplest solution is to make the work order field a parameter field in the query for the report by surrounding a prompt in square brackets. You place this in the criteria field; e.g. [Work Order Number]. Some people don't advocate the use parameters in queries for whatever reason; I think it's just personal preference. The lack of a value after being prompted may result in unsatisfactory results, but it's quite a simple operation to copy your existing query, parametize it and see how it performs either way. You can also filter the report using the DoCmd.OpenReport filter parameter if you have a form control that contains the work order number.

Alternatively, you can provide a form textbox or combo for the value input and test if it's null or not. You'd have to execute one way if it's null and another if it's not. Depending on your preference, in vba you can
- build a valid sql statement without the WHERE part, and concatenate the WHERE part based on the existence of a value
- or run either of 2 queries (but then you're duplicating objects unnecessarily)
- or create a query definition on the fly but not store it in the Nav pane (probably more complicated than what you need)
- or create DAO parameters - definitely more complicated than what you need.
The point of the above being that as with most situations, there's more than one approach.