title: Passing Parameter Value in Access Query using vba
body: <p><strong>Our requirement -</strong></p>
  
  <p>We have few suppliers and we want to generate a monthly report of purchases from each supplier during the month and export to excel. I think the solution is to create a parameter query where I can pass the value of the supplier ID, Month and Year. The list of supplier keeps changing and is stored in a separate table.
  So, basically, I should be able to sequentially read the supplier ID from that table and pass as a parameter to my query to generate the report for that supplier. </p>
  
  <p><strong>The closest solutions</strong> that I found for my requirement was on (which are similar in nature) - </p>
  
  <p><a href="https://stackoverflow.com/questions/16336025/exporting-recordset-to-spreadsheet/21955460#21955460">Exporting Recordset to Spreadsheet</a></p>
  
  <p><a href="http://answers.microsoft.com/en-us/office/forum/office_2010-customize/filtering-a-query-used-by-docmdtransferspreadsheet/06d8a16c-cece-4f03-89dc-89d240436693" rel="nofollow noreferrer">http://answers.microsoft.com/en-us/office/forum/office_2010-customize/filtering-a-query-used-by-docmdtransferspreadsheet/06d8a16c-cece-4f03-89dc-89d240436693</a></p>
  
  <p><strong>Why I think there could be a better solution -</strong></p>
  
  <p>In the suggested solution, we are creating multiple queries and deleting these. Conceptually, I feel there should be a way to create a parameter query and use the do while loop to sequentially pass the value of the parameter (DeptName in above example) to the query and export the results to excel.</p>
  
  <p>I should be able to achieve this if I can use vba to pass value to a parameter query. And that is what I have not yet been able to figure out. </p>
  
  <p>Update on 24-Feb - </p>
  
  <p>Following is the code that I have written - </p>
  
  <pre><code>Private Sub Monthly_Supplier_Sales_Report_Click()
  Dim strDirectoryPath As String
  Dim DateFolderName As String
  DateFolderName = Format$((DateSerial(year(Date), month(Date), 1) - 1), "YYYY MM")
  strDirectoryPath = "C:\dropbox\Accounting\Sales Reports\" &amp; DateFolderName
  If Dir(strDirectoryPath, vbDirectory) = "" Then MkDir strDirectoryPath
  
  Dim Filename As String
  Dim strSQL1 As String
  Dim strSQL2 As String
  Dim DesignerCode As String
  Dim month1 As String
  Dim year1 As Integer
  Dim Query1 As DAO.QueryDef
  Dim query2 As DAO.QueryDef
  Dim rsDesigner As DAO.Recordset
  
  Set rsDesigner = CurrentDb.OpenRecordset("Designer Details Master")
  
  Do While Not rsDesigner.EOF
  DesignerCode = rsDesigner![Designer Code]
  month1 = "Jan" 'right now hardcoded, will call this programatically
  year1 = 2014   'right now hardcoded, will call this programatically
  
  strSQL1 = "SELECT * FROM [Sales Report Generation Data] WHERE [designer code] = '" &amp; DesignerCode &amp; "' AND [Shipping Month]= '" &amp; month1 &amp; "' AND [Shipping Year]=" &amp; year1
  
  strSQL2 = "SELECT * FROM [Sales Report Generation - Monthwise Inventory Snapshot] WHERE [designer code] = '" &amp; DesignerCode &amp; "' AND [Snapshot Month]= '" &amp; month1 &amp; "' AND [Snapshot Year]= " &amp; year1
  
  Set Query1 = CurrentDb.CreateQueryDef(Name:="TempSalesQuery", SQLText:=strSQL1)
  Set query2 = CurrentDb.CreateQueryDef(Name:="TempInventoryQuery", SQLText:=strSQL2)
  
  Filename = strDirectoryPath &amp; "\" &amp; DesignerCode &amp; Format$(Now(), " yyyy mm") &amp; ".xls"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempSalesQuery", Filename, False, "Sales Report"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "TempInventoryQuery", Filename, False, "Inventory"
  
  CurrentDb.QueryDefs.Delete "TempSalesQuery"
  CurrentDb.QueryDefs.Delete "TempInventoryQuery"
  
  rsDesigner.MoveNext
  Loop
  
  End Sub
  </code></pre>
  
  <p>Instead, The logic I want to apply is - </p>
  
  <pre><code>Do While Not 
  assign Value to Parameter 1 = rsDesigner![Designer Code]
  assign Value to Parameter 2 = Month1
  assign Value to Parameter 3 = Year1
  Run the two Parameter queries, for which about three parameters are the input value and export to excel in respective sheets.
  Loop
  </code></pre>
  
  <p>Just that I have not yet been able to figure out - how to achieve this.</p>