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.

 

[Joe Hanna]

I did this, but I am getting type mismatch.


Is there a way to drop these files in their individual folders by repicating the file path or this is impossible because of the array and the loop function?

Thank you!!!

============

Private Sub Command4_Click()
'//Declare Variables
Dim a As Variant 'Array to hold part numbers
Dim i As Long 'Counter variable
Dim fPath As String 'File Path for Exported Spreadsheets
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT CPCLIST.[Cust Price Class] FROM CPCLIST")
i = 0
Do While Not rs.EOF
ReDim Preserve a(0 To i) Gives me Type mismatch
a(i) = rs.Fields("Cust Price Class").Value
i = i + 1
rs.MoveNext
Loop
rs.Close
'//Part Numbers we want to get
a = Array()
'//Iterate Part Numbers and export Query to Spreadsheet for each part
For i = 0 To UBound(a)
Me.PartNum = a(i) '//Set Textbox in Form to Part Number

fPath = "C:\Users\Desktop\Access to Excel " & Me.PartNum.Value & ".xls"

DoCmd.TransferSpreadsheet acExport, , "Query1", fPath, False
Next i

 

[xenou]

Make these two changes:
1) dim a differently
2) comment out (or delete) the other line

Private Sub Command4_Click()
'//Declare Variables
Dim a() As Variant 'Array to hold part numbers
Dim i As Long 'Counter variable
Dim fPath As String 'File Path for Exported Spreadsheets
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT CPCLIST.[Cust Price Class] FROM CPCLIST")
i = 0
Do While Not rs.EOF
ReDim Preserve a(0 To i) Gives me Type mismatch
a(i) = rs.Fields("Cust Price Class").Value
i = i + 1
rs.MoveNext
Loop
rs.Close
'//Part Numbers we want to get
'//a = Array()
'//Iterate Part Numbers and export Query to Spreadsheet for each part
For i = 0 To UBound(a)
Me.PartNum = a(i) '//Set Textbox in Form to Part Number

fPath = "C:\Users\Desktop\Access to Excel " & Me.PartNum.Value & ".xls"

DoCmd.TransferSpreadsheet acExport, , "Query1", fPath, False
Next i


You can set a folder differently but you need to be able to construct the folder path/file path is all. I don't know how the folders are set up.

 

[Joe Hanna]

You are genious!!!

The reason I want the array to receive its values from the same summary table was to make sure that there is not mismatch between Listbox values which run the queries and the file names.
It will be a problem if a Sales rep receives a report that he is not supposed to see.

I also added the second query, which displays in a separate tab. It is awesome.

I cannot get the date to work in the file name. I added a text box to the forrm with name 'Date' and pasted the string


\Access to Excel " & Me.PartNum.Value & Format(txtDate.Value, "yyyy-mm-dd") & ".xls"

, but does not work. maybe I am missing something in box formatting.

if it anything simple please share. I don't want to take more from your time. You have been of a great help!!!

Thank you again!!!

 

[xenou]

Well, so far so good.

\Access to Excel " & Me.PartNum.Value & Format(txtDate.Value, "yyyy-mm-dd") & ".xls"

, but does not work. maybe I am missing something in box formatting.

PartNum was my example field. YOu can't really be using the same field in your actual database, or that's an extreme coincidence. Probably you should post your whole code as it stands now, once again.

ξ

 

[Joe Hanna]

You are correct. I will not use PartNum in reality. In the rush i used it in my test database for simplicity.

Actually, the Me.PartNum.Value part works just fine.

The one that does not is Format(txtDate.Value, "yyyy-mm-dd").

 

[Joe Hanna]

Xenou, this is the code that I have tested, large part of which is working tanks to you.

The only part that I cant get to work is the one in blue.

Is there an easy way to alter the code so it drops ever excel file in the Sales Rep individual folder? For example, if Cust Price Class = AAA, the file will be dropped to folder C:\Users\Desktop\AAA, for BBB, in folder BBB.

I tried to play with it but due to the looping I could not get this part correctly. I do not know, maybe there is no way to do that.

Thanks!

====

Private Sub Command4_Click()
'//Declare Variables
Dim a() As Variant 'Array to hold part numbers
Dim i As Long 'Counter variable
Dim fPath As String 'File Path for Exported Spreadsheets
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT CPCLIST.[Cust Price Class] FROM CPCLIST")
i = 0
Do While Not rs.EOF
ReDim Preserve a(0 To i)
a(i) = rs.Fields("Cust Price Class").Value
i = i + 1
rs.MoveNext
Loop
rs.Close
'//Part Numbers we want to get
'//a = Array()
'//Iterate Part Numbers and export Query to Spreadsheet for each part
For i = 0 To UBound(a)
Me.PartNum = a(i) '//Set Textbox in Form to Part Number
fPath = "C:\Users\ib008775\Desktop\Access to Excel " & Me.PartNum.Value & Format(txtDate.Value, "yyyy-mm-dd") & ".xls"
DoCmd.TransferSpreadsheet acExport, , "Query1", fPath, False
DoCmd.TransferSpreadsheet acExport, , "Query2", fPath, False
Next i

End Sub

=======

 

[xenou]

Sorry, it's getting confusing. My example field is called txtDate but I think you said your field is called Date. So you need to use your field name, not my field name:

Format(Me.Date.Value, "yyyy-mm-dd")


You can create a file path from any information available in your form data fields. But I'm confused where the AAA and BBB are coming from. Is that a value in a field too?

 

[Joe Hanna]

Sorry, it's getting confusing. My example field is called txtDate but I think you said your field is called Date. So you need to use your field name, not my field name:

Format(Me.Date.Value, "yyyy-mm-dd")


You can create a file path from any information available in your form data fields. But I'm confused where the AAA and BBB are coming from. Is that a value in a field too?

I am sorry I was not clear.
You are correct. I replaced txtDate with different name and it worked just fine. I tested it. This is and example of a file name that I get "Access to Excel HPH 2013-04-04". It is amazing what is possible!!!
For some reason it did not allow me to rename before, so I thought I should use the Date name as listed.

As far as AAA and BBB. These are, lets say the Sales persons codes that are called in the queries by [Customer Price Class] field, and the same appear on the excel file name. In the file above it is "HOH"

So, with the current set up all files are droped in the same location, so I have to copy them in their corresponding folders. I could not change the file path for each Sales rep because the code loops after 'Next i'. So I was curious if files can be dropped in their respective folders. For example for Sales rep with code AAA in folder AAA. I assume that this will be very difficult to be accomplished.

Thanks.

 

[xenou]

Alright, this is a file name with a sales code in it:

This is and example of a file name that I get "Access to Excel HPH 2013-04-04".

But I don't see a folder name here.

What is the full path to one of these files in one of these folders? And what is the actual name of the field that contains the sales code?

 

[Joe Hanna]

Hi Xenou,

The full path would be for example:

C:\Users\Desktop\John Smith-AAA for report Access to 'Excel AAA 2013-04-04.xls'
C:\Users\Desktop\Tom Jones-BBB for report Access to 'Excel BBB 2013-04-04.xls'
C:\Users\Desktop\Simon Coll-CCC for report Access to 'Excel CCC 2013-04-04.xls'

The full statement currently is

fPath = "C:\Users\Desktop\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"

I assume if it was not for the loop, the statement would look something like this:

fPath = "C:\Users\Desktop\John Smith-AAA\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"
fPath = "C:\Users\Desktop\Tom Jones-BBB\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"
fPath = "C:\Users\Desktop\Simon Coll-CCC\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"