Geek asks and answers

Passing a parameter value in an access request using vba

Our requirement is

We have few suppliers, and we want to generate a monthly purchase report from each supplier for a month and export to Excel. I think the solution is to create a parameter request where I can pass the value of the vendor identifier, month and year. The list of suppliers continues to change and is stored in a separate table. Therefore, basically, I should be able to sequentially read the supplier identifier from this table and pass as a parameter in my request to create a report for this supplier.

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

Export Recordset to Spreadsheets

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 might be a better solution -

In the proposed solution, we create several queries and delete them. Conceptually, I believe that there should be a way to create a parameter request and use a while loop to pass the parameter value (DeptName in the above example) sequentially into the request and export the results to succeed.

I should be able to achieve this if I can use vba to pass the value into the parameter request. And this is something that I still could not understand.

February 24th update -

Below is the code I wrote -

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

It’s just that I still couldn’t understand how to achieve this.

+3
2

. , , . (. 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
0
  • TEST_MY_CODE()
  • F5

  • .

    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
-1

More articles:


All Articles