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

[lookingforK]

Hi,

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


I am just wondering:
Is there a way to set the Parameter value for the query via VBA?

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

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

The VBA for the button has 2 subs:
1) Sub 1 for the loop for 10 managers

[FONT=Times New Roman][COLOR=black][COLOR=black][FONT=Times New Roman]...[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]strQuery = "SELECT DISTINCT [Master Table].[Manager] FROM [Master Table] WHERE ((([Master Table].[Manager]) Is Not Null));"[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Set rstStores = CurrentDb.OpenRecordset(strQuery)[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]rstStores.MoveFirst[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Do Until rstStores.EOF[/FONT][/COLOR]
[COLOR=red][FONT=Times New Roman]{assign the value [/FONT][FONT=Calibri][U][I]rstStores![Manager][/I][/U][/FONT][FONT=Times New Roman] to the Parameter of the saved query}[/FONT][/COLOR]
 
[COLOR=black][FONT=Times New Roman][B][COLOR=blue]GenerateReport[/COLOR][/B] strReportPath, Trim(rstStores![Manager])[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]rstStores.MoveNext[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Loop[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]End If[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]...[/FONT][/COLOR]
[/COLOR][/FONT]

2) Sub 2 (i.e. GenerateReport) for exporting dataset into Excel spreadsheet one by one for each manager.
Here, I use the command 'TransferSpreadsheet'

[FONT=Times New Roman][COLOR=black][COLOR=black][FONT=Times New Roman]...[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]DoCmd.TransferSpreadsheet acExport, , "For exporting", ReportPath & "\Temp1.xlsx"[/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]...[/FONT][/COLOR]
[/COLOR][/FONT]

How to write the code in the red part?:banghead:

Thank you in advance.

 

[pbaldy]

I would use a form for the parameter instead of the brackets method. Then your red line is:

Forms!FormName.TextboxName = rstStores![Manager]

 

[DavidAtWork]

pb's suggestion should work fine, just point the criteria line of the Manager field in the query to the textbox, but I have one question in that are you setting the report path or filename different for each manager, because it appears that you will overwriting the same file each time or am I misunderstanding.

David

 

[pbaldy]

Depends; it isn't shown how the path variable is set. It could incorporate the manager value.

 

[lookingforK]

Thank you pbaldy.

Actually, I do use a form for the parameter. But I would like to try another way since I read a post mentioning:
"... You can't set the parameter values in this way then use docmd.Openquery. WHen you set the parameters via DAO like this your only setting them for the
querydef object. When you use docmd.openquery you effectively creating another instance of the query object which isn't related to the first. ..."

So I want to check if the above rule has the same effect on DoCmd.TransferSpreadsheet.


Thank you DavidAtWork.

For the issue of the file names, I incorporate the manager value and time ...

 

[pbaldy]

Not sure I agree with that, but I don't have the context. In any case, to use a different method you're probably going to have to switch from TransferSpreadsheet to automation, then you can use a QueryDef and set the parameters.

 

[lookingforK]

Thank you pbaldy.

I got a simple solution: use a Temp Variable i.e. [TempVars]![…]

 

[pbaldy]

Glad you found a solution.

 

[HartJF]

I read a post mentioning:
"... You can't set the parameter values in this way then use docmd.Openquery. When you set the parameters via DAO like this your only setting them for the querydef object. When you use docmd.openquery you effectively creating another instance of the query object which isn't related to the first. ..."

That makes sense to me. But if I try to open a recordset against the querydef, I'm getting Error 3061 Too few parameters.... I've successfully used Execute against a querydef with set parameters in other projects, but I need to inspect the recordset in this project.

Here's the code:

With CurrentDb.QueryDefs!myquery
.Parameters!prl = ComputedValue     'both long integers
Set rsf = .OpenRecordset(dbOpenForwardOnly)
End With

I could dynamically create the query's SQL
strSQL = "SELECT ... WHERE ID = " & ComputedValue
but the stored query would execute faster, I believe.

Thanks for your help, people!

 

[pbaldy]

This works for me (relevant lines only):

  Dim db                      As DAO.Database
  Dim rs                      As DAO.Recordset
  Dim qdfStatement            As DAO.QueryDef
 
  Set db = CurrentDb()
  Set qdfStatement = db.QueryDefs(strQuery)
  qdfStatement![Forms!frmMainMenu!txtDate] = Forms![frmMainMenu]![txtDate]
  qdfStatement![Forms!frmMainMenu!txtEndDate] = Forms![frmMainMenu]![txtEndDate]
  qdfStatement![Forms!frmMainMenu.fraServiceType] = [Forms]![frmMainMenu].[fraServiceType]

  Set rs = qdfStatement.OpenRecordset

 

[HartJF]

Stupid error... it always is.

myquery was adapted from a working query, but I missed one adaptation.

My VBA approach is valid. Thanks for the effort, Paul.