Iterate throgh records and run Module

[Ebes1099]

Is there a way to iterate through each record in a form and run a module?

I thought about using an update query but I think what I'm trying to do is too complicated.

To explain what I'm trying to do, I'm making calculation of a dollar amount times a percentage increase for each record in a form when the user clicks a button on the form. The user can select if they want to use the default increases of enter their own custom ones. The default increases are maintained in another table with another form. If the user wants to change those defaults, they can go into the default form and do that and I have a save button on there. When they click the save button I want it to automatically go through all the records in that other table and update them with the new percentages.

It's not quite as easy at it sounds. What I do is I copy the previous month's dollar value into the current month, and then at a renewal date I need to apply the first increase. Then copy that new value into the rest of the months. Someitmes there can be two renewal dates and I have a second renewal increase value that I use at that.

Either way, I have all that code written for when the user calculates the code from the Group form. The problem is that when they go change the defaults, the value's are already calculated with the old defaults and they don't update. I want them to recalculate based on the new default increases. That's why I was thinking of putting that code I have already written to do the calculations into a module, then when the defaults are updated and save is clicked I can loop through each record and run that module. Any help is appreciated. Thanks.

 

[lameid]

When you say you want the values recalculted my first thought is you want to use a query to calculate the results instead of a query so you do not update data a lot.

Otherwise nothing in what you describe sounds beyond an update query.

In either case it sounds like you want to use either the NZ function or the IIF function to determine the value you want to use for your perecentage.


If you need more help than that more information about the tables is needed or a query that joins all the related tables together with the field that needs updated and the fields used to calculate the update.

 

[Ebes1099]

Here's exactly what I'm doing. I have 18 months worth of values on my form. Aug-Dec of the prior year and Jan-Dec of the current year. Each group has a renewal month when the increase is applied. If it's Aug-Dec there are going to be 2 increases in my calendar, one in the Prior Year and 1 in the Budget Year. If it's before August, there's only 1 increase during the Budget Year.

Certain assumptions can be input by the user. A custom revenue increase for Year1 and Year2 or they can select to use the defaults for Y1 and Y2. There's a check box that says UseDefaults? The defaults are on a different form with another table behind that.

So the first time through each group they either use the custom or the default increase and then they calculate the value for each month. The current value is copied into the months all the way up until the first renewal month. At that month, I want to take the previous month's value and multiply it by the percent increase (the default if the box is checked, otherwise the custom). Then copy that value until the next renewal (if there is one) and at the second renewal, take the month before and multiply it by the Year2 increase. Then copy that value until the end of the year. If there's only 1 renewal, you just copy the first renewal increased value to the end of the year.

So, I have all the code written that does that on the form the first time through. It uses the original default increase value the user entered. Now to my issue, if once the user calculates all those out and they see they want to raise the default increase, they go into the form and enter new default increase values. Then they click the save button on the form and those new values are shown on the original form. But all the values that were calculated with the old increase don't update with the new defaults the user just entered. If I click the calculate button on that form it will change them. But I don't want to have to go to every record in that form and click that button when I change the defaults.

That's why I just want to use the code that's already behind that calculate button and have some sort of loop that goes through every record and runs that code.

It seems like with all the checking of the renewal month and copying the previous month value I can't do it in an update query. Maybe I can and I just don't know how. Thanks for the help and if you need any more info let me know.

 

[lameid]

The query would look something like this since you want to update based on the previous month's values. Since you have not shared code, table names or field names you will have to adapt it.

Update Table_Name as A Inner Join Table_Name as  B On A.ID = B.ID
Set A.Field = B.Field1 * B.Field2
Where A.Date_ID = DateAdd("m",-1, B.Date_ID)

If you are forced into a code solution, you would need a recordset and loop through it which would be a significant modification to your code.

 

[Ebes1099]

I actually used a recordset and looped through each record and recalculated the fields that needed it. I used some for loops using rst.fields(value) where the it looped on the value and used value-1 to get the data from the previous month. Seems to be working so far.