Query Calc Slow to Update

[Bill]

Hi All,

Not sure whether this is query or form related hence the post to both NG's.
Hope this is ok.

I think what I am doing is pretty basic. I have a subform that lists the
components of a product. The query on which the subform is based calculates
qty*cost=TTL to give a total. This works fine.

The main form has a field with a DSum function that sums the totals as
follows for the component ref. The DSum function is below. The results of
the Dsum doesn't update immediately or even after going to the previous
record and back again. Eventually it does but I can't see why there is any
delay at all or what eventually causes it to update.

Can anyone help please?

=DSum("[q_Prod Components]![TTL]","[q_Prod Components]"," [q_Prod
Components]![Prod Ref] =" & 'Ref')

Ta.
Bill

 

[Dale Fye]

Bill,

Your DSUM appears to be formatted improperly.

The first part of the criteria portion is probably correct, but the last part

& 'Ref'

does not make any sense. What you have now will try to compare [Prod Ref]
to the literal string "Ref". Actually, I would think what you have posted
would actually generate an error.

If you have a textbox on your form called txtRef, then it might look like:

=DSum("[q_Prod Components]![TTL]", _
"[q_Prod Components]", _
"[q_Prod Components]![Prod Ref] =" & me.txtRef)

If the value in txtRef is a string instead of a number, it should look like

=DSum("[q_Prod Components]![TTL]", _
"[q_Prod Components]", _
"[q_Prod Components]![Prod Ref] ='" & me.txtRef & "'")

Note that I have used a single quote just to the right of the = , and have
wrapped a single quote between double quotes on the end.

HTH
Dale

 

[Bill]

Thanks Dale,

No questions back at the moment. I will look at what you have posted and
give it a try.

I can assure you however that i don't get an error, just a delay. I think
you are on to something with the way I have done the 'Ref' bit though!

Thanks again.
Bill.

 

[Bill]

Thanks Dale, the value is txt so you second suggestion worked, except that I
got a #Name? error initially but removing the me part sorted it. Still a
delay in updating though!

 

[Dale Fye]

Bill,

Are you running on a network or on your desktop?

Is the application split (front / back)? If so, where is does each part of
the application reside?

When do you expect that field to change? After you have made a change in
the subform? If so, you could use an event in the subform to force that
control in the main form to requery with code similar to

me.parent.txt_Total.Requery

I'm not sure where (which event of the subform) you would put this code, but
I would suspect it would be in an event procedure that adds a new component
or something like that.

HTH
Dale

 

[Bill]

Thanks Dale, that has helped.
Regards.
Bill.