vba - 使用 vba 在 Access 查询中传递参数值

Question

我们的要求-

我们的供应商很少，我们希望生成当月每个供应商的采购月度报告，并导出到 excel。我认为解决方案是创建一个参数查询，我可以在其中传递供应商 ID、月份和年份的值。供应商列表不断变化，并存储在单独的表中。因此，基本上，我应该能够从该表中按顺序读取供应商 ID，并将其作为参数传递给我的查询以生成该供应商的报告。

最接近的解决方案，我找到了我的要求(性质相似)-

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

为什么我认为可以有更好的解决方案 -

在建议的解决方案中，我们正在创建多个查询并删除它们。从概念上讲，我觉得应该有一种方法来创建参数查询并使用 do while 循环将参数值(上例中的 DeptName)顺序传递给查询并将结果导出到 excel。

如果我可以使用 vba 将值传递给参数查询，我应该能够实现这一点。这就是我还没有弄清楚的。

2 月 24 日更新 -

以下是我写的代码-

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

相反，我要应用的逻辑是 -

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

只是我还没有弄清楚 - 如何实现这一目标。

Answer 1

这是一种解决方案。请注意，我创建了两个查询将使用的函数。只需创建您的两个查询并保存它们(请参阅下面的示例 SQL)，添加您的代码以选择日期，一切都应该没问题。

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