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]

Sorry, this can't be done without a loop (because you are only working with one file at a time, so there's no point in having three filepaths here when you can only use one of them).

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"

You need to think about how *on each loop* you can provide the code with the information it needs. Specifically, on each loop we need to know name and code of the sales person (that is what we don't have). We also need to know the PartNum and the Date (which we've already worked out).

I have been assuming that the sales person's name and code is information in your query. Is that true? If so, what are those field names.

 

[Joe Hanna]

I caused a lot of confusion with these field names and I sincerely apologize! </SPAN>
</SPAN>
I am working with a Test Database located on my Desktop. That is why I can adjust my field names and form names to your code. I can easily adjust the names to the actual names in the real database when I implement the whole project.</SPAN></SPAN>
==========
</SPAN></SPAN>
The function is working. Files are dropped. They are dropped, however, all in one folder (the Desktop).</SPAN></SPAN>
==========
</SPAN></SPAN>
<<"I have been assuming that the sales person's name and code is information in your query. Is that true? If so, what are those field names. ">>
</SPAN></SPAN>
The Sales Person codes are in the queries, Names are not.
Unfortunately my Access Tables have only Sales Rep code in the following format (XXX), while the folders are in the format (FirstName SecondName - XXX).
I can possibly rename the folders to reflect only Sales rep Code as XXX, getting rid of the FirstName LastName if that will help.</SPAN>
</SPAN>
==========
</SPAN></SPAN>
What I did is I created a query named CPCLIST. It draws just 1 column from the original tables. This column is CPC, short from [Customer Price Class]- sorry for the confusing name. As I said this is a test database. This query provides the 3 digit Sales Rep code, such as AAA, BBB, CCC.
</SPAN></SPAN>
Then I grouped the CPC column in that CPCLIST query to get rid of duplicates and sorted Ascending.</SPAN></SPAN>
Then I linked the CPCLIST Query to the Form ListBox. </SPAN></SPAN>
Then I linked the Box to both:</SPAN></SPAN>

1. Queries that are ran by the function, using the </SPAN>PartNum </SPAN>field in the following statement </SPAN></SPAN>

WHERE [Cust Price Class]=Forms!Form1!PartNum;</SPAN></SPAN>

1. To the function itself again by </SPAN>PartNum </SPAN> – that was your whole piece.

</SPAN></SPAN>

So, now queries get their parameter from WHERE [Cust Price Class]=Forms!Form1!PartNum;</SPAN></SPAN>
File names are assembled based on the same ListBox located in Forms!Form1!PartNum;
</SPAN></SPAN></SPAN>
This ensures that Queries are run based on the same Parameters as those used to assemble excel file names, so the possibility for errors is brought to minimum. </SPAN></SPAN>
All works as you designed it – perfectly.
</SPAN></SPAN>
The only thing that I could not figure out is whether I can drop the files into their respective folders somehow. </SPAN></SPAN>

Please let me know whether I can elaborate. </SPAN></SPAN>

Thank you again!!!</SPAN></SPAN>

 

[xenou]

If names aren't available to your code, then maybe you can use some kind of If-Then algorithm in your code:

If Me.PartNum.Value = "AAA" then
fPath = ".... John ...."
ElseIf Me.PartNum.Value = "BBB" then
fPath = ".... Jack ...."
ElseIf Me.PartNum.Value = "CCC" then
fPath = ".... Mary ...."
End If

This assumes that the sales code is enough to identify the folder, even without the name.

 

[Joe Hanna]

What can I say... You are amazing!

What a great tip. It worked perfectly.

If Me.PartNum.Value = "PES" Then
fPath = "C:\Users\Desktop\OP - PES\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"
ElseIf Me.PartNum.Value = "HPH" Then
fPath = "C:\Users\Desktop\JS - HPH\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"
ElseIf Me.PartNum.Value = "WDC" Then
fPath = "C:\Users\Desktop\CC - WDC\Access to Excel " & Me.PartNum.Value & " " & Format(EnterDate.Value, "yyyy-mm-dd") & ".xls"
End If


Everihing works like charm. OMG

Xenou, do you think that Access can accidently send the files somherere else (in folder that is not specified)?

 

[xenou]

Good deal. It looks pretty failsafe - it will only go where you've told it to (computers are wonderfully stupid that way - they only do what you tell them to do). The usual pitfall with file handling is things like the folder not existing. It doesn't look like that would be a problem here.

 

[Joe Hanna]

If

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

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

End If

Hello again!

Is it possible insted of hardcodding the Names of the forlders to loop a procedure that recteates the name of each individual folder in the path, in a similar way it is looped when selecting parameters for the queries.

In other words:

In the example above \PES - John Smith\ represents the folder where files should be dropped.
Where PES is the Sales Rep Code , John Smith is the name.

I have a query called 'SrCodeName' with 2 columns - One with 'Sales Rep Code' (PES, HPH...), Second with 'Sales Rep Names' (John Smith, Tim Levato,... ) that corespond to their codes.

Is it possible and how to say: when PartNum.Value = "PES" then use a query 'SrCodeName' in the path above to match Code with Name and create a string like this:

C:\Users\Desktop\HPH - Tim Levato\


I am not sure I explained good enough. Please let me know if you need me to clarify.

Thank you again!!!

 

[xenou]

Yes, instead of hard coding the list in the code, you can query for the names at run time. Probably DLookup is easiest:

fPath = C:\Users\Desktop\" & Me.PartNum.Value & " - " & _
    DLOOKUP("[Sales Rep Name]","[SrCodeName]", "[Sales Rep Code] = '" & Me.PartNum.Value & "'") & "\"

Note that if the name isn't found, you'll get Null returned, resulting in a bad filepath. So you'll have to handle the error when you try to save the file, otherwise it will crash if that happens (that's okay if you're running it yourself -- you'll know what to do -- but not so good if someone else is using the code).

 

[Joe Hanna]

Xenou,

I tested it on my test database and it worked.

It is beyond my mind how you get all this things done from the first time.

People in this forum are so helpful. I wish I knew some of you in real life.

Greetings from Chicago! I wish you a great weekend!!!

 

[Joe Hanna]

Hello Xenou,

I encountered the following issue:
Sometimes users can alter the name of the folders in which the files are stored.

Since exporting takes substantial time to complete, I am trying to create a procedure which checks whether the folders exist before exporting is started as a separate function.


I searched the net for several days and tryed to compile it to no avail.

This is how the separate command looks like:

==================================
'//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
FileFolderExists("C:\Users\Desktop\" & Me.PartNum.Value & " - " & _
DLOOKUP("[Sales Rep Name]","[SrCodeName]", "[Sales Rep Code] = '" & Me.PartNum.Value & "'") & "\"

=============================
Do you have any idea how I can compile this?

Thank you!

 

[xenou]

Add this to the beginning of your sub:
Something like this (i'm not sure of the precise program flow but this shows how to check if a folder exists):

[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]
    [COLOR="Navy"]If[/COLOR] CreateObject("Scripting.FileSystemObject").FolderExists("C:\Users\Desktop\" & Me.PartNum.Value & " - " & DLOOKUP("[Sales Rep Name]", "[SrCodeName]", "[Sales Rep Code] = '" & Me.PartNum.Value & "'") & "\") [COLOR="Navy"]Then[/COLOR]
       [COLOR="SeaGreen"]'//Keep going[/COLOR]
    [COLOR="Navy"]Else[/COLOR]
       [COLOR="SeaGreen"]'//Stop - no folder[/COLOR]
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
    
    [COLOR="SeaGreen"]'//More code ...[/COLOR]

[COLOR="Navy"]Next[/COLOR] i