Adding a parameter to function that calls a query

[Joe Hanna]

Hello,
I am trying to automate a report process. So I have 2 queries that are set up. Each report can be run for different Sales Reps by Parameter.

Parameter criteria is 3 digit code, so once the query is ran a pop up window asks for Sales rep code.

I created a form that I tied to a macro that calls the following function code.

Function TestExport()
DoCmd.TransferSpreadsheet acExport, , "Query1", "C:\Users\Desktop\Access to Excel AAA.xls", False
DoCmd.TransferSpreadsheet acExport, , "Query1", "C:\Users\Desktop\Access to Excel AAA.xls", False
End Function


Basically, the code calls the queries, creates an Excel file with name "Access to Excel AAA" and drops them on separate tabs in the file.
However, I need to replicate the process for 50 Sales reps by using different codes entered in the parameter field for each Sales rep.

So how can I include an expression in the Funtion above so it enters different parameter in Sales rep code field once the query is called out without interrupring the process untill all 50 files are created?

Thank you so much.

 

[xenou]

Hi,
I would probably set this up in a form. The form would have a textbox that holds the sales rep code. In the form code you just provide a loop routine that changes the value in the textbox. Query1 will be rewritten as Query2, which will reference the form:

Select Blah...Blah... WHERE SalesCode = Forms!SomeForm!SomeTextbox ...

The routine in your form code will just go through the sales codes, change the value, and run all of the sales codes:

Dim arr
arr = ("1","2","3",...)
For i = 0 to ubound(arr)
    SomeTextBox.Value - arr(i)
    Call TheFunctionThatDoesItAll()
Next i

And that does it ...

Is that enough to go on?
ξ

 

[Joe Hanna]

Thank you for the reply Xenou,

I think I know what you have in mind with arrey. However, I am not sure how to write the code and where to place it and how to tie it. I am not good with VBA code.
If you have time to elaborate on that I will appreciate it.

Thanks again!

 

[xenou]

Hi,

Given a query such as this (notice the query references a form textbox):

SELECT 
    PartNum, PartDescription 
FROM 
    Part 
WHERE 
    PartNum = [Forms]![Form1]![PartNum];

You can write code in your form (for instance, as button event code):

[COLOR="Navy"]Private[/COLOR] [COLOR="Navy"]Sub[/COLOR] Command1_Click()

[COLOR="SeaGreen"]'//Declare Variables[/COLOR]
[COLOR="Navy"]Dim[/COLOR] a [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Variant[/COLOR]        [COLOR="SeaGreen"]'Array to hold part numbers[/COLOR]
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]           [COLOR="SeaGreen"]'Counter variable[/COLOR]
[COLOR="Navy"]Dim[/COLOR] fPath [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]     [COLOR="SeaGreen"]'File Path for Exported Spreadsheets[/COLOR]

[COLOR="SeaGreen"]'//Part Numbers we want to get[/COLOR]
a = Array("P100", "P101", "P102")

[COLOR="SeaGreen"]'//Iterate Part Numbers and export Query to Spreadsheet for each part[/COLOR]
[COLOR="Navy"]For[/COLOR] i = 0 [COLOR="Navy"]To[/COLOR] UBound(a)
    Me.PartNum = a(i) [COLOR="SeaGreen"]'//Set Textbox in Form to Part Number[/COLOR]
    fPath = "C:\Users\Desktop\Access to Excel " & Me.PartNum.Value & ".xls"
    DoCmd.TransferSpreadsheet acExport, , "Query1", fPath, False
[COLOR="Navy"]Next[/COLOR] i

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

See if you can get that working for your case -- post back if more questions.
ξ

 

[Joe Hanna]

I created an Even Procedure off of a button placed on a form for the second code, but I am not sure where to paste the first code.

Thank you!

 

[xenou]

The first is just the sql for a query. You need to adjust your query (or create a new one) to reference a form textbox that has a value for the field that you are using as a parameter.
ξ

 

[Joe Hanna]

I am Trying to get the query to work off of the form selection before I move to the function.
This is the Query VBA:

SELECT SFData1.[Bill To#], SFData1.[Bill To Name], SFData1.[Bill To Address 1], SFData1.[Cust Price Class]
FROM SFData1
WHERE [Cust Price Class]=Forms!Form1!PartNum;

The query does not work fro some reason.
By "reference a form textbox", do you actually mean List box? because I do not see how I can reference table column in a text box.
In the list box I do the wizard. However, when I reference to the original table it geves me duplicate values as well.

When I reference to the query, it does not display any values at all in the list box.

When I use the wizard and select "use own values", I go to "Row Source" and enter the values. But for some reason it does not work properly. Even If I enter only one value, the query displays wrong results.
Otherwise the query works good.

I am doing something wrong but cannot find out what.

 

[Joe Hanna]

Okay I fixed it.

I used a List box wizard and I selected the Parameter values from a separate query that groups by the parameter, so it displays only unique values in the List box.

I am moving to the function itself. I hope it will work with List box. =/

 

[Joe Hanna]

I think I got this to work thank to your code xenou!!!

is it possibe to make the function to read the a = Array() directly from the List box?

Additionally, I have to add date that will not be current date to the name of each file. I was thinking that users may enter the date on a separate box in the form, from which the function will be able to pick it up. How can I do these enhancements?

Thank you so much for your help!!!!!!!

 

[xenou]

Listboxes are something of a pain (the syntax isn't intuitive and there's different kinds of listbox setups). It might be easier if you duplicate the listbox source. For instance, if the listbox source is "SELECT [SomeField] FROM Table1" then you can populate your array:

Dim rs As DAO.Recordset
Set rs = CurrentDB.OpenRecordset("SELECT [SomeField] FROM Table1")
i = 0
Do While Not rs.EOF
    Redim Preserve a(0 to i)
    a(i) = rs.Fields("SomeField").Value
    i = i + 1
    rs.MoveNext
Loop
rs.Close

Now you have a() with the same values as the Listbox in it.



As far as the date, if it's in a textbox then you can build the string with it. Let's say the date is in a field named txtDate:

Dim fPath As String
fPath = "C:\Users\Desktop\Access to Excel " & Format(txtDate.Value, "yyyy-mm-dd") & ".xls"