DSUM Text Box Stuck in a loop

[Jademonkey2k]

Heya guys,



thats for the help with my previous posts, you were all very helpfull. Thats why iv come back

Iv got another issue with a DSUM function that seems to be stuck in a loop.

I have a text box on a forum with its control source set to.....

=DSum("[Line Total]","[ContractMaterials Query]")

The query in question (ContractMaterials Query) returns a list of materials that are related to a certain contract, linked via a ContractID Primary/Forign key.

In the query there are 3 fields im using, the material, the cost, and the quantity. I have setup inside the query an additional field that multiplies the cost and the quantity giving me the total material cost of that particular item for this contract....

Line Total: [Unit Price]*[Quantity]

This is working perfectly, its returning the values with no problem. The query itself is also returing all the corect records when i specify which ContractID i want them for.

However i have a basic text box on the form that i need to add up the line totals of all records that the query returns. As stated above im using the following statement in the control source for the textbox....

=DSum("[Line Total]","[ContractMaterials Query]")

When i open the form the text box appears to be blank, no value, no error, nothing. However when i click on the box the value seems to appear. I thought i may have just been a case of getting the textbox to requery itself when the form loads but i noticed somthing peculier with it. The box seems to flash or flicker when its not highlighted, or has focus.

I can only asume that it must be stuck in some kind of Requery loop where its contrstatly refreshing the value so fast that it cant be see.

I need this box to refesh on a regular basis as the user will be choping and chainging the materials all the time. I have gone though all the VBA code i have written for it and for the life of me i cannot see a reason for it being stuck in a loop.

Has anyone else had this issue or knows of a work around?

Cheers guys you are the best

[maximus]

Well the query that ContractMaterials Query does it have a condition to it. Coz you have written that:

"The query itself is also returing all the corect records when i specify which ContractID i want them for."

if a condition is there on the ContractID how is it that you are supplying the condition to the query when u are opening the form.

Is it referenced to a control on the form.

[Jademonkey2k]

Heya mate,

Yeah the query is displayed as a subform on my main form called "Contracts". On the contracts form there is a control thats holds the ContractID. The Query condition is that it referances this control on this form to get the ContractID that it needs to return the records.

I have set the form to refresh the value of the textbox on the form "On Current" Event. i thought this might have been causing the loop but after taking it out this morning i still have no luck :S

[weekend00]

Please note that if you are showing many records, Dsum may work very slowly.

[Jademonkey2k]

Heya,

yeah thats defiantly a possibility man, but query is only returning anywhere between 5-10 records at the most

[Jademonkey2k]

Ok so i have gone through the code line by line and removed any requery referances to the text box with the DSUM still not luck.

Just to test it i made a new form with a entry text box, a button and a texbox to display the restuls of the DSUM.

I set the query to grab the contract id from the new from, and when the button is pushed it grabs the records accordingly, however i also poped a requery statement into the code so that DSUM box will refresh when the records are returned.....Boom! Same thing

The box just sits there and flashes untill i click on it. Once clicked on it revelas the data i need so its working perfectly from a theoretical point of view. I just cant seem to see why the box is flashing and why it will only display the values i need when its physically clicked on. Its bizzar.

I took the DSUM statement out of the control source for the text box and ran the form again, this time it didnt flicker, on re-entering the DSUM statement it happend again so i can only assume that the DSUM statement im using is making the text box flash.....

=DSum("[Line Total]","ContractMaterials Query")

Im completly lost as to how its happening lol

[Jademonkey2k]

Anyone got any ideas?

[Jademonkey2k]

Heya guys,

Well 24 hours (a 100 cups of coffee later) i managed to resolve the issue. Why it was flashing and flickering i have no idea, however on the form where the text box is located, simply adding the following code to the on current event seems to have resolved the issue.

Me.Recalc

Again, why it was doing this is completely beyond me, never heard of of an issue like it, but for all those getting flashing/flickering text boxes with a DSUM control source, theres ya answer, add Me.Recalc to the current event of the form. Sorted

Thanks for the input anyway guys