Waiting for a textbox to update

[ted.martin]

I have a filtered Form that when the form opens, if the user immediately presses a command button to print the form's data, a dlookup value in a textbox sometimes prints as zero rather than the correct value.

If I introduce a 2 second pause in the cmdprint report code, everything works fine as the form then has time to fully load. You can see the textbox waiting for the value to be calculated when you open the form. There is a definite delay and if you press the print command button too soon, the result is incorrect.

I have tried using DoEvents in cmdprint event but it seems to make no difference.

I just don't think using a 2 seconds Pause in the cmdprint code is kool.

Any suggestions would be welcome.

 

[midmented]

Does the recordset in the dlookup have a lot of records in it?

I would try grabbing the value on report open and not take it from the textbox.

 

[ted.martin]

Thanks - will try that.

 

[RuralGuy]

The Open event of a form is too early in the process to reference a control on the form. You need to use the OnLoad event.

 

[ted.martin]

Thanks Rural Guy - sorry I used the term 'open' generically. the text box that is causing the problem is initialised during the OnLoad event.

I suppose, my question is really related to using a 2 second pause. Is this kool or clumsy?

regards

 

[RuralGuy]

Have you tried putting your DLookup in the OnLoad event instead of the control source of the TextBox?

 

[ted.martin]

Thanks Rural Guy - I have been looking at the code sequencing again following your suggestion. My DLOOKUP is in the OnLoad event BUT one element of the text box's calculated control source is where the DLOOKUP relates to data ([F-Invoice Components subform].Form!Quotation) from a subform on the main form.

txtVAT.controlsource = roundcurr(([F-Invoice Components subform].Form!Quotation*DLookUp("[VATrate]","Company Details")),2)

Until the subform loads, I imagine the onLoad Dlookup can't run and so waits until it can. Maybe this is the problem and the 2 second Pause is merely a device to prevent the CmdPrint code running before the subform has loaded.

Just thinking out loud - if I make the cmdprint button.visible = false in the main forms OnLoad event, can I then make it visible only when the subform has loaded. That might do it.

 

[RuralGuy]

The SubForm loads *before* the main form.

 

[RuralGuy]

I believe you should be setting the .Value property of the txtVAT control instead of the .ControlSource property. Does this value change without exiting the form? If so then we should explore other options.

 

[ted.martin]

I used .ControlSource because the value is an expression that comes from the SubForm. I have tried changing it to .Value and but the result then become zero. The problem is the default ControlCource is the table and it is under certain circumstances e.g the InvoiceDate, I need the textbox to use the value from the expression.

Here in the Uk they changed the VAT rate from 1st December. Therefore if the invoice is dated before 1st dec., the VAT is the (historic) stored value. If it is after 1st Dec, then the form needs to calculate it. This value can of course change as more invoice lines are added.

Naturally I save the record when the Invoice is complete.

When the form loads, this value is initially zero and then within about half a second it you can see it change change after whatever ControlSource is appropriate is selected. There is clearly a sequencing process here.

The delay is clearly seen when the form is waiting for the figures (values) to come through from the subfrom. The Mainfrom is the Invoice Header and the Subform is the Invoice Details - could be several lines on this subform and the total invoice value the mainform is waiting for is the sum of all the values from the Subform to be calculated.

Thank you for your time on this one; I just feel that putting in a time delay for the mainform to catch up is not really professional.

 

[RuralGuy]

I just feel that putting in a time delay for the mainform to catch up is not really professional.

It may also not be reliable if the machine is busy with other things. I generally use the Current event of the SubForm to add up any sums I need and then explicitly set them into the MainForm. That way I'm not subject to the delay. You'll find the form is considerably snappier doing it this way.

 

[ted.martin]

Thanks again. Interesting approach. The Invoice Total is in the SubForm footer and all I am doing is referencing this field =nz(Sum([Line Cost])) in the Main Form. Not sure what you mean then by using the OnCurrent event of the subform.

 

[RuralGuy]

The SubForm has its own RecordSet whose RecordSetClone you can loop through and get your own Sum. It is much faster than waiting for the Access prioritized scheme to get around to your Footer control.

 

[ted.martin]

Ok thanks you - got that so I will give it a try. Thanks for being patient.

 

[RuralGuy]

Great! Post back here if you still need assistance.

 

[KenHigg]

Great! Post back here is you still need assistance.

'is you need assistance' - Sounds like Alabama english

 

[RuralGuy]

'is you need assistance' - Sounds like Alabama english

Fixed. Thanks Ken.

 

[ted.martin]

I have been playing with this technique and you are correct it does provide the data faster. The problem I have now is what subform event do I use to calculate the running totals that currently appear in the footer? If my subform has (say) 3 lines, that is 3 line total figures to add up.

If any of those lines change, it will need to recalulate.

Thanks again.

 

[RuralGuy]

What would cause any of the three lines to change? Have you looked at the OnCurrent event of the SubForm?

 

[ted.martin]

Essentially manual intervention. This form is a Main form as the Invoice header with a subform of multiple line for the invoice details. Any changes to the subform will affect the Footer total for the invoice value read from the subform footer and inserted in my troublesome slow to update Main form field.