SetValue to change Query ?

[Pete]

Hi,

Is it possible to use the SetValue macro command to change the criteria of a
field in a query ? The Field is "Account Manager" and I would like to design
macros which changes the critera to the name of a different person each time.

Many Thanks in advance
Pete

 

[Steve Schapel]

Pete,

No, the SetValue macro action is not applicable to this task.

If you could clarify the meaning of "each time", someone may be able to
suggest a suitable alternative solution.

 

[Pete]

Thanks Steve - I thought that might be the answer !

I have about 20 account managers, each one has a query set up containing
sales data - all the fields are the same - the only difference is the
criteria changes depending on the name of the account manager.

I use 20 macros - one per account manager - each one exports the query data
to excel. i was trying to streamline this by having one central query and
using setvalue in the macro to pass the criteria (the account manager name)
onto the central query. That way, if I had to add a field to the query, I
would only need to do it once rather than 20 times.

 

[Lance]

Yes, it is possible. You just need to write your query to base criteria off
a form field. You then manipulate that form via a macro.

But this is something that would be better off run via VBA, you could make
it totally table driven and save yourself a lot of maintenance hassle in the
future.

 

[Steve Schapel]

Pete,

Since this involves a process of cycling through a set of records and
performing an action for each one, this is one area where VBA procedure
shines compared with a macro approach.

The problem is that neither the OutputTo or TransferSpreadsheet methods
(I assume you are using one of these) supports a Where condition argument.

So one thing you can do is use code to manipulate the SQL property of
the query, to create an instance of the query for each criteria value.

I assume you have a query or table that lists the account managers, in a
field I assume is called AMName. So in my example I will call this
"ListOfManagers". And I will use "YourQuery" as the name of your
central query with no criteria in it. In which case, here is some
example code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim BaseSQL As String
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT AMName FROM ListOfManagers")
Set qdf = dbs.QueryDefs("YourQuery")
BaseSQL = Left(qdf.SQL, Len(qdf.SQL)-3)
With rst
Do Until .EOF
strSQL = BaseSQL & " WHERE AMName=" & """" & ![AMName] & """"
qdf.SQL = strSQL
DoCmd.OutputTo acOutputQuery, "YourQuery", ...etc
.MoveNext
Loop
.Close
End With
qdf.SQL = BaseSQL
Set qdf = Nothing
Set rst = Nothing
Set dbs = Nothing