Using Requery on a Form

[chrisguk]

Hi,

I a form with calculated fields in it. I believe I can do this by putting =Sum(Nz([field])+Nz([field2])) in the control source on the form field.

So anyway. I have a requery macro in the after update box and it keeps trying to create a new record. The form doesnt like this because it is a linked child form with a parent.

I keep getting "You cannot add or change a record because a related record is required in tbllog"

Any help will be great?

 

[boblarson]

You don't need to requery. You will use RECALC -

So, if the after update is on the subform but the main form needs to be recalculated:

Me.Parent.Recalc

if the code is on the parent and the subform needs it

Me.SubformControlNameHere.Form.Recalc

(where SubformControlNameHere is the name of the control on the parent form which HOUSES the subform, not the subform name itself, unless they share the exact same name). And the .Form. part stays exactly as shown.

and if the code is on the same form as needs to recalc -

Me.Recalc

 

[chrisguk]

Bob Thanks for that. It worked perfectly. Have a slightly different issue on the same form.

I have =Sum(Nz([std_cost_av_base])/Nz([no_days])*365) as the control for one of the fields but it has stopped calculating and just says #Error?

Any ideas?

 

[boblarson]

You may need to account for no_days being 0 which then would have a divide by 0 error:

=Sum(IIf(Nz([no_days],0) = 0, Null, (Nz([std_cost_av_base],0)/[no_days])*365))

 

[chrisguk]

Ok Bob I will give that a go and let you know. Many Thanks

 

[chrisguk]

Hi Bob,

Not sure what is supposed to be happening with that. My fault though because there are two other calculations on the form. I have pasted all below for you to look at:

=Sum(Nz([I_saving])+Nz([ac_saving])+(Nz([std_cost_av_base])/Nz([no_days])*365))

=Sum(IIf(Nz([no_days],0) = 0, Null, (Nz([std_cost_av_base],0)/[no_days])*365))

=Sum(Nz([b_end_date])-Nz([b_start_date]))

So the fields are the result of a few calculated fields, hope this makes sense

 

[chrisguk]

You may need to account for no_days being 0 which then would have a divide by 0 error:

=Sum(IIf(Nz([no_days],0) = 0, Null, (Nz([std_cost_av_base],0)/[no_days])*365))

I tried that but didnt seem to work. I do have two other calculations working on the form so I have pasted everything below. Is this the best way to do it or should I use a query?

=Sum(Nz([std_cost_av_base])/Nz([no_days])*365)

=Sum(Nz([I_saving])+Nz([ac_saving])+(Nz([std_cost_av_base])/Nz([no_days])*365))

=Sum(Nz([b_end_date])-Nz([b_start_date]))

In fact with figures other than zeros in the boxes I get #error

 

[boblarson]

1. You can't refer to a text box for use in the calculation if it has a calculation of its own. You have to use the entire calculation.

2. Make sure that no controls used in any of the calculations have the same name as the fields they are bound to.

3. You will still need to account for the possible divide by zero error in the first and second formulas there.

 

[chrisguk]

1. You can't refer to a text box for use in the calculation if it has a calculation of its own. You have to use the entire calculation.

2. Make sure that no controls used in any of the calculations have the same name as the fields they are bound to.

3. You will still need to account for the possible divide by zero error in the first and second formulas there.

Hi Bob,

I take note of what you state about the fact you cannot put in a calculation the calculation of another field and have to use the entire code.

I am having trouble putting these two together to make one entire calculation:

=Sum(IIf(Nz([no_days],0) = 0, Null, (Nz([std_cost_av_base],0)/[no_days])*365))

"This is the calculation in no_days" =Sum(Nz([b_end_date])-Nz([b_start_date]))

 

[boblarson]

I believe it should be:

=Sum(IIf(Nz(Nz([b_end_date])-Nz([b_start_date]),0) = 0, Null, (Nz([std_cost_av_base],0)/ Nz([b_end_date])-Nz([b_start_date]))*365))

 

[chrisguk]

I believe it should be:

=Sum(IIf(Nz(Nz([b_end_date])-Nz([b_start_date]),0) = 0, Null, (Nz([std_cost_av_base],0)/ Nz([b_end_date])-Nz([b_start_date]))*365))

Have to say you seem to know your stuff.

Probably a good idea to explain the logic of the calculation as that is producing -26million.

Result field = std_av_base "divided" by the number of days in field: no_days "times" 365

The other thing, the b_end_date - b_start_date is not calculating properly. I just tried it minus 1 day and gave me a figure of 1157 instead of 1

Sorry to be a pain

 

[chrisguk]

Bob it all works now. I had another look at your suggestions made a few tweaks and works perfect. Many thanks I have learnt a lot from that.

 

[boblarson]

Bob it all works now. I had another look at your suggestions made a few tweaks and works perfect. Many thanks I have learnt a lot from that.

Glad you could get it yourself before I was able to come back to the forum. It is much better if you can figure it out from the direction because you'll remember it a whole lot better as you go further along.