Macro Loop values in Form ang get values to query

[mauryc1990]

Hi,



I have a problem with macro in Access. I have 2 tables with data:

1. Table "sl_data" contents ID and Date (Date from Table 1):
1 2017-04-30
2 2017-05-07
3 2015-01-13
....
3700 2016-02-26

About 3700 records.

2. Table "Table1" contens schedule payoff credit. Number_Contract, ID_Instalment, Date, Capital, Capital_Paid, Capital_Required

Cumulatively about 870 000 records. The main purpose is calculation for each date calculate Capital Paid and Capital Required .

For Instance, for Number Contract 1xxxx - We have 60 instalment in Table1 from date 2015-01-01 to 2025-07-01 and If I choose the date 2016-12-01 I want receive the amount of Capital_Paid and Capital_Required, but not only for one Number Contract but for all.

I think about Form with combobox. The Loop through the all records from Table "sl_data" and run a query with parameters.

Any ideas?

[aytee111]

Not sure how table "sl_data" fits in with your requirements. This is a totals query with date parameters. A form with a from-date and a to-date and the query will use those dates as criteria, using "Between Forms!formname!FromDate And Forms!formname!ToDate". Then the query can be made into a subform and in the AfterUpdate of the dates on the form, requery the subform.

[mauryc1990]

Table "sl_data" is created only for needs to creating combobox in Forms, but this is the same date which find in "Table1". For all these date I need assign the Capital Paid and Capital Required for about 870 000 records. I don't want to choose manually from Form the one of the date.

I think about Combobox with date and then create macro which:

1. Make a loop throw first date to last date and take a value(date) to query.
2. Run Query . Select Number_Contract, ID_Instalment, Date, Capital, Capital_Paid: (Suma(IIf([Table1]![DATE]<[Forms]![Form3]![DATA1];[Table1]![VCAPITAL]))) Capital_Required : (Suma(IIf([Table1]![DATE]<[Forms]![Form3]![DATA1];[Table1]![VCAPITAL])))
3. Create query which updates my Table1 With results of Capital_Paid, Capital_Required

I wonder if is possible to pass the parameters from FORM to query but not in Criteria but in Field like: Capital_Required : (Suma(IIf([Table1]![DATE]<[Forms]![Form3]![DATA1];[Table1]![VCAPITAL])))

[aytee111]

You don't need to repeat data on tables. For the combobox to select a date, click on the RowSource property and when the query window opens, select just the date, use it to sort. Then go into SQL view and add the word "SELECT DISTINCT ...". That will remove the need for the second table and will keep your data more accurate.

Why do you not want to use the dates as criteria? The result will be the same and less prone to errors (such as your capital paid and your capital required above will be the exact same value! and the syntax is incorrect!). You can refer to the field(s) on the form any way you wish to use them in queries.

[mauryc1990]

Thanks for advices, but I have 2 problems:

1. If I choose one of the date, the query only select the records from the Table1 which Date = Date from Form, but I need calculate values for all records.

For example Number_Contract has a 3 Instalment "2015-01-01", "2015-02-01", "2015-03-01". And if I choose the date 2015-02-20, the query should sum the values which date is 2015-01-01 and 2015-02-01 as Capital Paid and 2015-03-1 as Capital Required.


2. I can pass the parameters from FORM to Query Criteria, but how I can pass to query function, for example Capital_Paid: (Suma(IIf([Table1]![DATE]<[Parameter from FORM];[Table1]![VCAPITAL])))
So, for all Number_Contract in TABLE1 sum Capital as Capital Paid if parametr is > than date or if parametr is < sum as Capital Required

[aytee111]

You are going to have to write out number 1 above in logical sequence. What is going on exactly, what does 2015-01-01 have to do with 2015-02-20? What do the records look like, when there is a value in Capital_Paid is Capital_Required zero or can there be a value in it? You may want to consider separating it into two tables - capital paid and capital required.

The IIF syntax is: IIf(some condition,then do this[,otherwise do this]). I haven't used it the way you have but it should work referring to the field in the form.

[mauryc1990]

OK,

So my Table1 looks like:

ID_Instalment Num_Contract Number_Rate Date Capital Capital_Paid Capital_Requirement
1 XXXX1 1 2017-05-01 100 000 62 500 37 500
2 XXXX1 2 2017-06-01 100 000 65 000 35 000
3 XXXX1 3 2017-07-01 100 000 67 500 32 500
4 XXXX1 4 2017-08-01 100 000 70 000 30 000
5 XXXX1 5 2017-09-01 100 000 72 500 27 500

1 XXXX2 1 2017-05-14 100 000 62 500 37 500
2 XXXX2 2 2017-06-14 100 000 65 000 35 000
3 XXXX2 3 2017-07-14 100 000 67 500 32 500
4 XXXX2 4 2017-08-14 100 000 70 000 30 000
5 XXXX2 5 2017-09-14 100 000 72 500 27 500


1 XXXX3 1 2015-01-01 100 000 62 500 37 500
2 XXXX3 2 2015-02-01 100 000 65 000 35 000
3 XXXX3 3 2015-03-01 100 000 67 500 32 500
4 XXXX3 4 2015-04-01 100 000 70 000 30 000
5 XXXX3 5 2015-05-01 100 000 72 500 27 500

And the Number_Contract is about 15 000 records x Instalement = about 870 000 records.

So if I have a problem with the Formula, if the parameter is < then Date than Sum Capital as Capital Paid, if the parameter is > then Date than Sum Capital as Capital Requirement. I wonder if this statement include in code VBA or build in Query View as Criteria?

[aytee111]

Capital Paid: Sum(IIf([Date Capital]<[Forms]![Form3]![SelectDate],[Capital_Paid],0))
This should work!

Is this a one-time deal or will there be history stored in the table? It is a bit worrying that you include EVERYTHING before/after the date entered going back in time from day 1 across multiple years.

[mauryc1990]

Capital Paid: Sum(IIf([Date Capital]<[Forms]![Form3]![SelectDate],[Capital_Paid],0)) ===> This work in qeury but how I can use it in macro VBA? DoCmd.RunSQL or other method? I should open query, execute code with parameter and close query. Than I take next parameters and loop.

I can stored the data in table, but for new contract I must do this per week.

[aytee111]

The query will be the record source for your subform, where you are displaying the data. In the date field on the main form, in the AfterUpdate event, say: Me.subformname.Requery - that will use the query and display the new data based on the date selected.

[mauryc1990]

Hmm, I a bit confused. I don't understand how the query will be the record source in my subform? Can You more explain this issue?

[aytee111]

There is a form, let's call it main form. On this form there will be a text box where the user can enter a date.
There is a query where this date is referenced (as above).
Next, using the wizard, create a tabular form with that query as the base, which now becomes the record source for this new form. In design view of the main form, add this new form as a subform.

Now you will have a main form/subform arrangement. When you enter a date, requery the subform and it will display the new data based on the date entered.

[mauryc1990]

OK, thanks for advices

but I don't want select the date also enter a date manually, I want for all date in Table1 (this is the date from 2015-01-01 to 2017-12-01 without some days) calculate Capital_Paid and Capital_Requirement. I want to switch on it one button and receive a results as a table

[aytee111]

First, get the query working. Choose one date, 2017-04-30, it will be a make-table query. Check that the data in the table you create is correct based on that date.

Once the query is working, you can write a VBA routine to run that query for every date in the table. We can help with that.