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]

This part is actually on a serever:

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

so when I paste the exact link, which is something like:

(\\network\Sales Team\ " & Me.PartNum.Value & "....)

it produces an error when compiling, while your expression does not.

Is it possible that this does not work on networks?

 

[xenou]

I'm not having trouble using a UNC folder address (\\blah\blah\blah).

The way you posted your code you are missing a beginning double quote though:

(\\network\Sales Team\ " & Me.PartNum.Value & "....)

Should be:

([B][COLOR="#FF0000"]"[/COLOR][/B]\\network\Sales Team\ " & Me.PartNum.Value & "....)

 

[Joe Hanna]

I apologize that I omitted that in my previous post.

I have the double quote in the actual code, though.
Strange.


Oh I found what the problem might be in this row:

& Me.PartNum.Value & "'") & "\" ) Then


Might be that it should not thave the backward dash because it looks for continuation.

Still strange because it did not produce an error on the first string that you pasted previously. </SPAN>

 

[xenou]

CreateObject("Scripting.FileSystemObject").FolderExists will work with or without the final slash. Not sure. If its not even compiling then the problem is syntax in the code.

 

[Joe Hanna]

Xenou,

Ifter playing a little with it, I got this to work with the statement that you provided and the rest of the code.

I appologize. I misunderstood your initial post.

Thank you again! I learn one piece at a time.

 

[Joe Hanna]

I have this listbox that is linked to a query to retrieve the sales code parameters from that query.
In turn these codes are used in the procedure above to generate the queries that need to be exported.

I know that when this form will be in use, sometimes we will need to export reports not for all sales codes that are listed on the original listbox, but just for some of them.
I attempted to acieve this task with checkboxes, but it seems rather complex task.
However, I learned that I can possibly do that same thing by using my existing listbox and select MultySelect=Simple in the Other tab of Properties.

I tried to run the export, but it still exports all reports that are in the list, no matter whether non of the codes in the listbox is chacked or just some of them are checked.

Any idea how I can achieve my goal?

Thank you so much!!!

 

[xenou]

You probably want to run your code based on the selections in the listbox. If only one is selected it runs once, if two or more are selected then it runs the selected items, and if all the items are selected it runs them all. These are all just variations of one algorithm: run the selected codes.

Start by creating a single listbox in a new form and practice accessing selected list items in a loop. Be sure to handle the special case of nothing selected. Once you are comfortable with multi-select listboxes and iterating list items, you should be good to go.

ξ