calculated field delay in subform.

[samcoinc]

I am having a problem that had been run into before. I have a couple of calculated fields in a subform. One sums a field in the subform and one counts the records. I have a function that I wrote that takes all the data from the main and subform and does a calculation. If I do a refresh and then do the function the calculation is done before the subform calculates the sum and count.

How do I make function wait for the subform calculated field to populate before it runs. I suppose I could do dcount and dsum in the function instead of using the calculated field on the subform - wouldn't that be slow? Is there a better way?

thanks
sam

 

[smercer]

instead of a calculated field use a macro or VBA code to set the data.

How to do this:
1) in the data base window click on "Macros" and then "New"

2) in the actions column of the new macro use the drop down list and select "SetValue"

3) in the item box insert the name of the box that should contain the calculation. the name should be in squire brackets "[]".

4) in the expression box type your calculation without the equal sign in the beginning of the equation

5) save as "mac_calculations" or what ever

6) in the subform properties go to event tab and change the "After Update" to "mac_calculations" or whatever you saved it as.


Does this work?

good luck

 

[samcoinc]

Guess I don't quite understand what you are saying. I have a subform that in the footer has a control source that has =Count(*) also in the detail it has =Sum([ppfoot]). one counts the records in the subform and the other sums the some data.

The problem is I have a function that I wrote that I would like to run whenever anything changes on the form/subform.

I have a refresh on the main form to make sure the data is up to date then run the vb function. When the refresh runs the subform refreshes and then there is a delay before the count and sum field has data. By this time the function has already run. It is a big problem if they add records to the subform because I have an afterupdate on the control that refreses and runs the fuction. again the function runs before the subform count and sum populate so they have no data.

I even did an ontimer event hoping that the timer wouldn't run until the subform was done calcuating. That didn't work. The other thought is to put a timer that just waits a second but I don't want to do that (bad programing in my opinion) what happens if it runs on a slow computer and a second isn't enough.

Just one of those things that there should be an easy solution - I want to pause before my code is run until the subform gets done calculating. I would think this would be easy but I have yet to find a solution.

btw I am running access97
thanks for reading
sam

 

[smercer]

you put in the requery action on the next line after the calculation

 

[samcoinc]

Thanks I will try that. I am leaning and already started re-writing the form so the function is done in a query. I think this is going to be a better solution. I can sum and count the one table and link it to the main table. then do the calculations in that query. I am making the function so that I can ask for the return value that I need. I have about 10 varable going into the funcion and about 20 coming out. I have started rewriting the funcion so I can use it in multiple fields in the query.

kind of like this
calcfunc(data1,data2,data3,data4,"returnvalue")
so lets say in one query field I have
result1:calcfunc([data1],[data2],[data3],[data4],"returnvalue1")
next field I have
result2:calcfunc([data1],[data2],[data3],[data4],"returnvalue2")
and so on.

in the end of the function I have a case statement that then returns the value based on the "returnvalue"

This way I can use one funcion that i can keep strait as all the calculatons are related to each other.

maybe odd but I think it will work better as I can use the query anywhere.
(forms-reports)

Hope I am making sense
thanks again
sam