title: MS Access Report - Modify RecordSource with VBA Using Parameter from Main Form
body: <p>I didn't realize my initial post (which I deleted) was posting under a newbie account - apologies......</p>
  
  <p>In any event, here's my issue:</p>
  
  <p>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.</p>
  
  <p>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").</p>
  
  <p>What I <em>want</em> 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.</p>
  
  <p>The SQL works fine, but, the resulting data isn't being passed to the report, so I'm getting <em>all</em> 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.</p>
  
  <p>Here's the code</p>
  
  <p>On the Main Form</p>
  
  <pre><code>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 &amp; "Where ((consultant.typeone = '" &amp; Me.Type.Value &amp; "') OR (Consultant.typetwo = '" &amp; Me.Type.Value &amp; "') OR (Consultant.typethree = '" &amp; Me.Type.Value &amp; "') OR (Consultant.typefour = '" &amp; Me.Type.Value &amp; "'))"
          s = s &amp; "And (((Consultant.[End date]) &lt;= DateAdd(""d"", -730, Now()))) ORDER BY Consultant.Last_Name"
          DoCmd.OpenReport "RptList", acViewPreview, , , acDialog
      End If
  </code></pre>
  
  <p>End Sub</p>
  
  <p>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.</p>
  
  <p>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?</p>
  
  <p>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......</p>
  
  <p>I don't know what I'm missing. HELP!</p>