Editing a query's criteria from VBA

[Jen]

If you're getting the criteria from a form you would put
[Forms]![form_name]![control_name] where you have the []:
where form_name is the name of the form the criteria is
coming from and control name is the name of the specific
control where the criteria is coming from.

Now, in the VBA code, the sql statement is passed as a
string value, so you would need to do something like this:

Sub ........ ' probably some event procedure on the form

Dim strSQL As String

strSQL = "SELECT [Status Sort Codes].ST_Sort_Code, " & _
"[qry Union CM for Multiple-Status].STATUS, " & _
"[qry Union CM for Multiple-Status].SALES " & _
"FROM [Status Sort Codes] INNER JOIN " & _
"[qry Union CM for Multiple-Status] ON " & _
"[Status Sort Codes].Status = " & _
"[qry Union CM for Multiple-Status].STATUS " & _
"WHERE ((([qry Union CM for Multiple-Status].Region) ='" &_
[Forms]![form_name]![control_name & "') AND " & _
"(([Status Sort Codes].ST_Sort_Code)=1 " & _
"GROUP BY [Status Sort Codes].ST_Sort_Code, " & _
"[qry Union CM for Multiple-Status].STATUS " & _
"ORDER BY [Status Sort Codes].ST_Sort_Code;"

Me.Recordsource = strSQL

Exit Sub

Hope this helps.

Regards,
Jen

-----Original Message-----
Windows NT; Office 2000

I have a series of Sales reports, by region, that have
3 "summary" sub-reports in the header and a "detail"
report by salesman in the "detail" section.

Rather than create a separate report for each region, I
have a single report "template".

When I use code to define the Record source as a SQL
Statement I get a "join not supported" error on an Inner
Join from a table to a Union Query. I copied the Sql from
the query builder into my VBA Code. In the query builder
it works.

SELECT [Status Sort Codes].ST_Sort_Code, [qry Union CM for
Multiple-Status].STATUS,[qry Union CM for Multiple-
Status].SALES
FROM [Status Sort Codes] INNER JOIN [qry Union CM for
Multiple-Status] ON [Status Sort Codes].Status = [qry
Union CM for Multiple-Status].STATUS
WHERE ((([qry Union CM for Multiple-Status].Region)=[])
AND (([Status Sort Codes].ST_Sort_Code)=1
GROUP BY [Status Sort Codes].ST_Sort_Code, [qry Union CM
for Multiple-Status].STATUS
ORDER BY [Status Sort Codes].ST_Sort_Code;

How can I simply "feed" the region into the query..

Example...

Open the query in edit mode
Change the "Region" criteria to "West" or "East"
Save the revised query
Open and print the report.(Subreport Record source would
be "fixed" to the query.)

I know there's a way to do this, but I can only find help
in the KB and the VBA and Access help files for changing
the parameters in temporary "rst = Sql..." type recordsets
and not for changing or feeding the parameter to an
existing query.

'Preciate any help or referral to an answer...

bruce

.