Adding a parameter to function that calls a query

Joe Hanna

Board Regular
Joined
Apr 3, 2013
Messages
69
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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
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:
Code:
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

Board Regular
Joined
Apr 3, 2013
Messages
69
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
Hi,

Given a query such as this (notice the query references a form textbox):
Code:
SELECT 
    PartNum, PartDescription 
FROM 
    Part 
WHERE 
    PartNum = [Forms]![Form1]![PartNum];

You can write code in your form (for instance, as button event code):
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.
ξ
 
Last edited:

Joe Hanna

Board Regular
Joined
Apr 3, 2013
Messages
69

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Apr 3, 2013
Messages
69

ADVERTISEMENT

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

Board Regular
Joined
Apr 3, 2013
Messages
69
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

Board Regular
Joined
Apr 3, 2013
Messages
69
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
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:
Code:
Dim fPath As String
fPath = "C:\Users\Desktop\Access to Excel " & Format(txtDate.Value, "yyyy-mm-dd") & ".xls"
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,841
Members
413,944
Latest member
3xc3ln00b

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top