MS Access Report - Modify RecordSource with VBA Using Parameter from Main Form

I didn't realize my initial post (which I deleted) was posting under a newbie account - apologies......

In any event, here's my issue:

I have a main form that lists various "Consultants." This main form also has parameters "Consultant Type" "Start Date" and "End Date" that can be used to filter the list.

What's needed is a Report, listing "Consultants" of a certain type. Bear in mind that this "type" filter can apply to any of 4 different type fields ("type1", "type2", "type3", "type4").

What I want to do is place the button that runs the report onto the main form, because the "Consultant Type" parameter is already there. I then want to generate the SQL based off that parameter (and a date parameter that I've already done), and use that recordset to populate the report.

The SQL works fine, but, the resulting data isn't being passed to the report, so I'm getting all records instead of just the ones I want. The recordsource of the report object is set to the background table in order to populate fields/textboxes.

Here's the code

On the Main Form

Private Sub Button_Click()
Dim s As String
If IsNull(Me.Type.Value) Then
        MsgBox "Please Select a Type", vbCritical, "Report Error"
    Else
        s = "Select Consultant.* from Consultant "
        s = s & "Where ((consultant.typeone = '" & Me.Type.Value & "') OR (Consultant.typetwo = '" & Me.Type.Value & "') OR (Consultant.typethree = '" & Me.Type.Value & "') OR (Consultant.typefour = '" & Me.Type.Value & "'))"
        s = s & "And (((Consultant.[End date]) <= DateAdd(""d"", -730, Now()))) ORDER BY Consultant.Last_Name"
        DoCmd.OpenReport "RptList", acViewPreview, , , acDialog
    End If

End Sub

Now, obviously, it all goes to pot when I open the report, because there's nothing in the DoCmd that says "Using this recordset." And that's my problem.

I'm trying to avoid creating a separate little dropdown form for the purpose of populating a piece of data that's right in front of me. Also, plugging the SQL directly into the report's recordsource using Forms![MainForm]![Type].Value for the "Type" variables didn't work. I suspect it might have something to do with the form not being active when the report button is clicked?

I thought QueryDef might work. But, I'm not grasping how to implement it, in order to know IF it will work. And I've been Googling in circles......

I don't know what I'm missing. HELP!

Solution

AS C Perkins Suggested use a wherecondition in docmd.Openreport

Something like

    DoCmd.OpenReport "RptList", acViewPreview, , , acDialog, wherecondition:="consultant.typeone = "  & Me.Type.Value

Another way is you can create the object using Query def and used this to change your query at run time based on the user selected criteria.

try like this

save Report_Query in MsAccess and use this query as a recordsource for your report

Dim s as string

 s = "Select Consultant.* from Consultant "
 s = s & "Where ((consultant.typeone = '" & Me.Type.Value & "') OR (Consultant.typetwo = '" & Me.Type.Value & "') OR (Consultant.typethree = '" & Me.Type.Value & "') OR (Consultant.typefour = '" & Me.Type.Value & "'))"
 s = s & "And (((Consultant.[End date]) <= DateAdd(""d"", -730, Now()))) ORDER BY Consultant.Last_Name"

CurrentDb.QueryDefs("Report_Query").sql = s

DoCmd.OpenReport "RptList", acViewPreview, , , acDialog