Filtering a report from a cmd button

[mondeoman]

I have a report called "rpt_support_request_print" which is itesel based on a quesry with the the following SQL:

-----------------------------------------------------------------------

SELECT tbl_Support_Request.Request_ID, tbl_Maintenance_Request.Job_Purchase_Number, tbl_Maintenance_Request.Facility_Name, tbl_Maintenance_Request.Priority, tbl_Support_Request.Site, tbl_Support_Request.Caller_Initials, tbl_Support_Request.Caller_Surname, tbl_Support_Request.Date, tbl_Support_Request.Time, tbl_Support_Request.email, tbl_Support_Request.Telephone, tbl_Support_Request.Extension, tbl_Support_Request.Request_Type, tbl_Maintenance_Request.Job_Description
FROM tbl_Support_Request LEFT JOIN tbl_Maintenance_Request ON tbl_Support_Request.Request_ID = tbl_Maintenance_Request.Request_ID
ORDER BY tbl_Maintenance_Request.Job_Purchase_Number;

--------------------------------------------------------------------------

The report is opened through a cmd button on a form which allows the user to either, print, email or preview the report. What I want to be able to do is filter the report depending on the job number on a form called: "frm_Request_Detail". This form has a field called "job_number" which is a unique number stored in a table called tbl_Maintenance_Request and the field in the table is called "Job_purchase Number".

Can you tell me how I can allow the user to click the relevant cmd button and filter the report based on the "Job_Purchase_Number"? Also where do I put the actual code?

 

[lucishoe22]

i may have some code that would help...

Private Sub ViewReport(ReportName As String, WhereStr As Variant)
On Error GoTo Err_ViewReport
If IsNull(WhereStr) Then
WhereStr = ""
End If
DoCmd.OpenReport ReportName, acPreview, , WhereStr

Exit_ViewReport:
Exit Sub

Err_ViewReport:
MsgBox Err.Description
Resume Exit_ViewReport
End Sub

-----------------------
Private Sub runreportcmd_Click()
ViewReport "Report Name", Me![job_number]
End Sub


on your form frm_Request_Detail place the runreportcmd in the on_click section of the command button. place the viewreport function in the module also.

 

[mondeoman]

Many thanks