Passing Parameter Value in Access Query using vba

Question

Our requirement -

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.

The closest solutions that I found for my requirement was on (which are similar in nature) -

Exporting Recordset to Spreadsheet

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/filtering-a-query-used-by-docmdtransferspreadsheet/06d8a16c-cece-4f03-89dc-89d240436693

Why I think there could be a better solution -

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.

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.

Update on 24-Feb -

Following is the code that I have written -

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\" & 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] = '" & DesignerCode & "' AND [Shipping Month]= '" & month1 & "' AND [Shipping Year]=" & year1

strSQL2 = "SELECT * FROM [Sales Report Generation - Monthwise Inventory Snapshot] WHERE [designer code] = '" & DesignerCode & "' AND [Snapshot Month]= '" & month1 & "' AND [Snapshot Year]= " & year1

Set Query1 = CurrentDb.CreateQueryDef(Name:="TempSalesQuery", SQLText:=strSQL1)
Set query2 = CurrentDb.CreateQueryDef(Name:="TempInventoryQuery", SQLText:=strSQL2)

Filename = strDirectoryPath & "\" & DesignerCode & Format$(Now(), " yyyy mm") & ".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

Instead, The logic I want to apply is -

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

Just that I have not yet been able to figure out - how to achieve this.

Answer 1

Here is one solution. Note that I created Functions that the two queries will use. Just create your two queries and save them (see sample SQL below), add your code to pick dates and everything should be fine.

Option Compare Database
Option Explicit

Dim fvShipMonth     As String
Dim fvShipYear      As Integer
Dim fvDesignerCode  As String

Public Function fShipMonth() As String
    fShipMonth = fvShipMonth
End Function

Public Function fShipYear() As Integer
    fShipYear = fvShipYear
End Function

Public Function fDesignerCode() As String
    fDesignerCode = fvDesignerCode
End Function

Private Sub Monthly_Supplier_Sales_Report_Click()
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 rsDesigner  As DAO.Recordset

'SAMPLE SQL
'SELECT * FROM [Sales Report Generation Data] " & _
'WHERE [designer code] = '" & fDesignerCode() & "' AND [Shipping Month]= '" & fShipMonth() & "' AND [Shipping Year]=" & fShipYear()

    fvShipMonth = "Jan"
    fvShipYear = 2014
    Set rsDesigner = CurrentDb.OpenRecordset("Designer Details Master")
    Do While Not rsDesigner.EOF
        fvDesignerCode = rsDesigner![Designer Code]
        Filename = strDirectoryPath & "\" & DesignerCode & Format$(Now(), " yyyy mm") & ".xls"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "<your Query 1>", Filename, False, "Sales Report"
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "<your Query 2>", Filename, False, "Inventory"
        rsDesigner.MoveNext
    Loop
    rsDesigner.Close
    Set rsDesigner = Nothing
End Sub

Answer 2

1. Open your database
2. Create a NEW module
3. Paste the following code
4. Place your cursor inside Function TEST_MY_CODE()
5. Press F5

6. Paste the results from the immediate window in your response.

   Option Compare Database
   Option Explicit
   
   Dim fvShipMonth     As String
   Dim fvShipYear      As Integer
   Dim fvDesignerCode  As String
   
   Public Function fShipMonth() As String
       fShipMonth = fvShipMonth
   End Function
   
   Public Function fShipYear() As Integer
       fShipYear = fvShipYear
   End Function
   
   Public Function fDesignerCode() As String
       fDesignerCode = fvDesignerCode
   End Function
   
   Function TEST_MY_CODE()
       My_Click_EVENT      ' Test the code I provided
   End Function
   
   Private Sub My_Click_EVENT()
   Dim month1      As String
   Dim year1       As Integer
   
   fvShipMonth = "Jan"
   fvShipYear = 2014
   Debug.Print "**** START TEST ****"
   Debug.Print "fvShipMonth = " & fvShipMonth
   Debug.Print "fvShipYear = " & fvShipYear
   Debug.Print "fShipMonth() = " & fShipMonth()
   Debug.Print "fShipYear() = " & fShipYear()
   Debug.Print "**** END TEST ****"
   
   End Sub