How to set Parameter values for a saved query via VBA?

[lookingforK]

Hi,

I am using MSAccess 2010 to export data into MS Excel 2010 spreadsheets.

I am justwondering:
Is there a wayto set the Parameter value for the query via VBA?

For example:
There are 10regional managers. When I click a button on a form, 10 sets of data per managerare going to be exported into Excel spreadsheets.

I have createda saved query named [For exporting] with a parameter [Manager Name] for the field[Master Table].[Manager].

The VBA for thebutton has 2 subs:
1) Sub 1 forthe loop for 10 managers

...
strQuery = "SELECT DISTINCT [Master Table].[Manager] FROM [Master Table] WHERE ((([Master Table].[Manager]) Is Not Null));"
Set rstStores = CurrentDb.OpenRecordset(strQuery)
rstStores.MoveFirst
Do Until rstStores.EOF
{assign the value rstStores![Manager] to the Parameter of the saved query}
 
GenerateReport strReportPath, Trim(rstStores![Manager])
rstStores.MoveNext
Loop
End If
...

2) Sub 2 (i.e. GenerateReport) for exportingdataset into Excel spreadsheet one by one for each manager.
Here, I use thecommand 'TransferSpreadsheet'

...
DoCmd.TransferSpreadsheet acExport, , "For exporting", ReportPath & "\Temp1.xlsx"
...

How to writethe code in the red part?



Thank you in advance.

[Dal Jeanis]

I started to say "Use a temp variable", but I realized I don't understand the problem. Your code already is getting the value you want and passing it to the GenerateReport subroutine.

It CAN be as simple as this: [TempVars]![Manager] = rstStores![Manager]
and then using [TempVars]![Manager] in the saved query.

You can review this site for some more about how to use tempvars
http://blogs.office.com/b/microsoft-...-and-2010.aspx

[June7]

Options:

1. query parameter references a textbox on form - use code looping through the rstStores recordset to populate the textbox
Me.textboxname = rstStores!Manager

2. modify the query definition through QueryDef collection

Re Dal's suggestion, I've never used TempVars and don't know if query can reference one as a parameter.

[lookingforK]

Thank you Dal Jeanis.

I tried your method. It is simple and good.

Thank you June7.

I do use a Combo Box actually. I would like to check through QueryDef collection.

[June7]

Issue solved? I have marked thread solved.

[Perceptus]

I use the below code in part with running multiple update queries with parameters in code. Hope this helps. gdb = currentdb.

Public Function ExecuteParamterQuery(ByVal strQueryName As String, _
                                     ParamArray pArray() As Variant) As Recordset
    ' Written by alex miglin/perceptus 5/28/2012
    Dim Myqdf                   As DAO.QueryDef ' Query Definition
    Dim intCounter              As Integer      ' Used For loop
On Error GoTo Err_ExecuteParamterQuery
    Set Myqdf = gdb.QueryDefs(strQueryName)
    If UBound(pArray) > LBound(pArray) Then
        For intCounter = LBound(pArray) To UBound(pArray) Step 2
            Myqdf.Parameters(pArray(intCounter)) = pArray(intCounter + 1)
        Next intCounter
    End If
    Set ExecuteParamterQuery = Myqdf.Execute
Safe_ExecuteParamterQuery:
    Myqdf = Nothing
    Exit Function
Err_ExecuteParamterQuery:
    WarningBox "This query did not work." & vbCrLf & vbCrLf & Myqdf.SQL
End Function

[Dal Jeanis]

lookingforK - If you could post your code that worked, that might be helpful for the next person that googles this problem.

June7 - I'm not very confident with temp variables yet, but I know that they're basically stored as any other table, so Jet has to be able to handle them, given the right syntax. Of course, I could be wrong, and they could be pseudo-controls on some other kind of object. It looks like lookingforK got them to work, though, so I must be close to right.

perceptus - cool little widget there. I've actually never thought of using "& vbCrLf &" to make my messages more readable. Nice.

I love things that are intuitively obvious in retrospect.

[lookingforK]

The code is simple:
...
Dim tManager As String
...
...
' Write the following for the red part
tManager = rstStores![Manager]
[TempVars]![tempManager] = tManager
...
...
...

Remember to set up the parameter [TempVars]![tempManager] for the saved query.

[Dal Jeanis]

Good enough! Thanks for posting it up here.