loop a query with different parameters

[pieterw]

Hi guys
first of all a lot of thanks for those of you posting helpfull info
it already helped me out a lot!
This time I have a specific question, but not much of an idea where to start or how to manage:

I have these items (simplified)
-table with data (let's say invoices, invoice amount and client number)
-query (simple select/totalize query) that calculates the total per clientnumber (client number is asked as parameter to the user when the user opens the query)
-macro (exports the result of the query in excel)

Is it possible to 'loop' this macro/query?
I would like to make a table with the different client numbers
then launch 1 query, that is looped over and over for each client (in the new client table), and saves the different excel files on the harddisk.

Does anyone have an idea how to proceed?

Thanks for every helpfull reaction

 

[DCrake]

Yes this is possible, but it all depends on your VBa knowledge and understanding.

Basically you would have a form with a list box on that contains the customers. This would be a multi select list box. You would highlight the customers in questions. Then click button which would call a function in a loop for each item selected in the listbox. This function would be the one that you have now for one customer. The only difference is that tyou would not have the parameter in the query but in your vba function.

 

[pieterw]

Thanks for your reaction

my knowledge VBA is poor,however with the help of this forum and some of my colleagues, i am sure it will work

I think i can manage making the drop list (combo box)
but how can I write the function to run the loop over and over? :s

any idea how it needs to be built up?
thanks!

Pieter

 

[DCrake]

Ok then. Firstly you need a list box not a combo box.
Your list box will be a list of all your customers which will include their Primary Key and their customer name.

You make the list box Multi select

You then need a command button to call the new function with the on click event.

Sub CommandButton_OnClick()

   Call ExportCustomersByName

End Sub

Sub ExportCustomersByName()

Dim vItem As Variant
Dim Ctl As Control
Dim CustID As Long

    Set Ctl = Me.LstCust
    '/Loop through the listbox for only those items that have been selected
    For Each vItem In Ctl.ItemsSelected
        CustID =  CLng(Ctl.Column(0, vItem))
        '/Here you would run the command that exports the customer data to 
        '/a new spreadsheet.
        '/You will need to decide how you are going to name the workbooks
        '/The CustId is the identifier that destinguishes  one customer from 
        '/another.
    Next vItem
    Set Ctl = Nothing
End Sub

 

[pieterw]

Hi again

This is great! question posted, and instant code for the answer
unfortunately the debugger opens and show me the first error:

it is situated here (bold):

Dim vItem As Variant
Dim Ctl As Control
Dim CustID As Long
Set Ctl = Me.LstCust
'/Loop through the listbox for only those items that have been selected
For Each vItem In Ctl.ItemsSelected
CustID = CLng(Ctl.Column(0, vItem))
'/Here you would run the command that exports the customer data to
'/a new spreadsheet.
'/You will need to decide how you are going to name the workbooks
'/The CustId is the identifier that destinguishes one customer from
'/another.
Next vItem
Set Ctl = Nothing
End Sub

Could this have something to do with the name of my list box?

 

[DCrake]

That's correct you need to replace this with the name of your list box.

 

[pieterw]

The name of the list box is now

List0

i reversed your methodology and overwrote the name of the list box to

LstCust

code seems ok now, but nothing happens when i execute it

where in the code do i need to input the 'command' to execute the query (over and over)?

 

[DCrake]

Post the code you have now that does the Excel job and I will tell you

 

[pieterw]

the button used to be a macro that executes a query (exporting the result into excel)

this is the SQL code behind the query

SELECT [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude] AS D_PA, tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], Sum(tbl_kpi_data.T7000) AS SumOfT7000, Sum(tbl_kpi_data.[SumOfActual Inv Qty SI]) AS [SumOfSumOfActual Inv Qty SI], tbl_kpi_data.[Sales Unit]

FROM tbl_periodeparameters, ((tbl_kpi_data INNER JOIN [tbl_BPM accounts] ON tbl_kpi_data.[Notion Hirework(Y/N)] = [tbl_BPM accounts].[Notion Hirework(Y/N)]) INNER JOIN tbl_individu ON tbl_kpi_data.[Payer SI] = tbl_individu.[Individual n° SAP]) INNER JOIN tbl_periods ON tbl_kpi_data.[Booking Month SI] = tbl_periods.[Booking Month SI]

WHERE (((tbl_periods.Period) Between [periode van] And [periode tot]))

GROUP BY [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude], tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], tbl_kpi_data.[Sales Unit]

HAVING ((([tbl_BPM accounts].[BPM account])="700010") AND ((tbl_individu.[N° Magnitude])=[BPM Partner:]));

This query asks the user for the BPM Partner-> needs to be the ones selected in the list box
the query also uses other parameters the periods), stored in tables.

I hope this is what you are looking after, sorry for using so much of your time :s

 

[DCrake]

SELECT [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude] AS D_PA, tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], Sum(tbl_kpi_data.T7000) AS SumOfT7000, Sum(tbl_kpi_data.[SumOfActual Inv Qty SI]) AS [SumOfSumOfActual Inv Qty SI], tbl_kpi_data.[Sales Unit]

FROM tbl_periodeparameters, ((tbl_kpi_data INNER JOIN [tbl_BPM accounts] ON tbl_kpi_data.[Notion Hirework(Y/N)] = [tbl_BPM accounts].[Notion Hirework(Y/N)]) INNER JOIN tbl_individu ON tbl_kpi_data.[Payer SI] = tbl_individu.[Individual n° SAP]) INNER JOIN tbl_periods ON tbl_kpi_data.[Booking Month SI] = tbl_periods.[Booking Month SI]

WHERE (((tbl_periods.Period) Between [periode van] And [periode tot]))

GROUP BY [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude], tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], tbl_kpi_data.[Sales Unit]

HAVING ((([tbl_BPM accounts].[BPM account])="700010") AND ((tbl_individu.[N° Magnitude])=[BPM Partner:]));

First job:
Make the Having condition a Where Condition. This will speed up the query.


Then in the code I provided below the

CustID = ... Line

Dim StrSQL as String

StrSQL = "SELECT [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude] AS D_PA, tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], Sum(tbl_kpi_data.T7000) AS SumOfT7000, Sum(tbl_kpi_data.[SumOfActual Inv Qty SI]) AS [SumOfSumOfActual Inv Qty SI], tbl_kpi_data.[Sales Unit]

FROM tbl_periodeparameters, ((tbl_kpi_data INNER JOIN [tbl_BPM accounts] ON tbl_kpi_data.[Notion Hirework(Y/N)] = [tbl_BPM accounts].[Notion Hirework(Y/N)]) INNER JOIN tbl_individu ON tbl_kpi_data.[Payer SI] = tbl_individu.[Individual n° SAP]) INNER JOIN tbl_periods ON tbl_kpi_data.[Booking Month SI] = tbl_periods.[Booking Month SI]

WHERE (((tbl_periods.Period) Between [periode van] And [periode tot]))

GROUP BY [tbl_BPM accounts].[BPM account], tbl_individu.[N° Magnitude], tbl_kpi_data.Source, tbl_kpi_data.[Accounting Doc Nr SI], tbl_kpi_data.[Sales Unit]

HAVING ((([tbl_BPM accounts].[BPM account])=" [B][COLOR="Red"]& CustId &[/COLOR][/B] ") AND ((tbl_individu.[N° Magnitude])=[BPM Partner:]));"

This will pass the value of the current customer ID to the SQL statement.

The next question is how are you performing the Export routine? This is only the underlying query for the export routine.

 

[pieterw]

this export routine was simply done by the macro
I had a macro with

Output to

and details:

Object type : Query
Object Name: 700010_
Output format: Microsoft Excel 97-2003 (*.xls)
Output file:
Autostart: Yes
template file:
Encoding:

I guess something similar needs to be done by code?
(except without the autostart, and with an output file name equal to the one selected in the list)

Hopefully you still have the courage to help me out? :s

 

[DCrake]

Click on the Macro and then select Tools > macros from the menu and choose convert to macro to Visual Basic. To turn into an SQL statment

 

[DCrake]

Here is a sample code that you need to employ to rewrite the SQL statement for the underlying query which will change the filter option to the new customer

Public Sub QFix(CustId As Long,StrSQL As String)


Dim qd     As QueryDef
   Set qd = CurrentDb.QueryDefs("YourQueryNameHere")
         qd.SQL = strSQL
         qd.Close
    CurrentDb.QueryDefs.Refresh
    
End Sub

You then place this after you have done the building of the SQL String

 

[pieterw]

Hi

Back with this same issue, but found out some help already
basically this is the situation now:
-table with data (let's say invoices, invoice amount and client number)
-query (simple select/totalize query) that calculates the total per clientnumber (client number is asked as parameter to the user when the user opens the query)
-table with all the unique client number

FORM:
-contains combolist linked with the table (client numbers)
-cmdbutton with code that looks like this

change combovalue to client 1
execute (export) query
change combovalue to client 2
execute (export) query
change combovalue to client 3
and so on and so on...

this causes me a lot of code rules

is there a way I can write code
'choose next combovalue'
then
over and over
and stop with last one

thanks for your help! (and the hints already posted)

 

[chrisguk]

I learnt a lot from that thread!!!