Passing a Field Name as a Parameter to a Query

[John Baker]

I am trying to make a call to an action query (qry_prod_prob_build) in the Group Header of a report that builds/creates a table that is used to graph a chart in the Group Footer section of the report. The action query works fine if I hard code the product name into the parameters collection (see subroutine below). However, what I really need to do is pass the product name into the subroutine call but I cannot figure out the correct syntax.

Am I on the right track with this approach? Can anyone lend a helping hand?

Thanks,
John


Private Sub ProductTypeHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs("qry_prod_prob_build")
With qdf
.Parameters("PRODUCT_TYPE") = "PRODUCT NAME"
.Execute
End With
Set qdf = Nothing

End Sub

 

[John Baker]

A Different Twist!

Ok, I determined that I can capture the PRODUCT_TYPE value from the Reports collection (see code below) in my subroutine call so I no longer have to figure out how to 'pass the value' into the subroutine. This is working fine, I have been able to confirm that the subroutine gets executed for each Product Type.

Now, my problem is this: Although the subroutine gets executed during the Report Header Format event, that creates a table which is used to graph a chart in the Product Type Group Footer, the chart that prints for each Product Type is identical! I surmise that I am executing the Event Procedure at the wrong time.... however, I am unable to figuer it out!

Any help or guidance would be appreciated.
John

Private Sub ProductTypeHeader_Format(Cancel As Integer, FormatCount As Integer)

Dim qdf As QueryDef
DoCmd.RunSQL "Drop Table tbl_rhd_prod_prob"
Set qdf = CurrentDb.QueryDefs("qry_rhd_prod_prob_build")

With qdf
.Parameters("PRODUCT_TYPE") = [Reports]!rpt_rhd_prod_prob].Controls![txtProductType]
.Execute
End With

Set qdf = Nothing

End Sub