pass parameters to record source with query Access2007

Question

I am relatively new to access and I have been tasked to improve the navigation of the app.

I have a form which is used to open the report. The record source of the report is as follows,

SELECT 
    Projects.Project_Number, 
    Projects.Customer, 
    Projects.End_User, 
    Projects.Engineering_Company, 
    [Merged Ship and Delivery Dates].Sched_Next_Delivery_Date, 
    [Merged Ship and Delivery Dates].Sched_Next_Ship_Date, 
    Projects.QC_Pack_Req, 
    Projects.Target_QC_Pack_Date, 
    Projects.Invoice_QC_Pack 
FROM 
    Projects LEFT JOIN [Merged Ship and Delivery Dates] 
    ON Projects.Project_Number = [Merged Ship and Delivery Dates].Project_Number 
WHERE 
    (((Projects.Project_Number = [Project Number] ))) 
ORDER BY Projects.Project_Number;

I am trying to get the report to open up without prompting me every time. There are instances where I need to refresh the report or open it from other forms. I have tried to use

DoCmd.OpenReport "Project Control Sheet", _ 
                acViewReport, , _ 
                "[Project_Number]=" & Me.ProjectNumber, , _
                "[Project_Number]=" & Me.ProjectNumber

It is still unable to pass the parameters to the record source. Is there anyway for me to pass the parameters to the recordsource(query)?

I have tried to use

Forms![formName]ProjectNumber

in the where statement but this only works for a single form and I have other forms which opens up this report.

The purpose of refreshing the form is to allow users to view the changes made to the report after they have updated it.

Answer 1

If you remove the WHERE clause from the report's Record Source query, you can later filter the rows it returns with the WhereCondition option of the DoCmd.OpenReport method.

Dim strWhereCondition As String
strWhereCondition = "[Project_Number]=" & Me.ProjectNumber.Value
Debug.Print strWhereCondition ' <- view this in Immediate window; Ctrl+g will take you there
'DoCmd.OpenReport "Project Control Sheet", acViewReport, , strWhereCondition
DoCmd.OpenReport ReportName:="Project Control Sheet", _
    View:=acViewReport, WhereCondition:=strWhereCondition

If you want a different approach, you could use a TempVar, since your Access version is 2007.

TempVars.Add "ProjectNumber", Me.ProjectNumber.Value

And change the Record Source query to use the TempVar ...

WHERE Projects.Project_Number = [TempVars]![ProjectNumber]