How do I update the calculated textbox based on listboxes in an unbound form?

[Ruth Barn]

The issue is that the calculated text box does not actually requery until each of the associated listboxes have been clicked into and exited.

I have an unbound form that looks up various pieces of a product number entered in an unbound text box (ProductNumber) and returns weights and lengths in unbound listboxes that are based on queries.
The criteria for the queries are based on functions that return a specific string from the product number.
Then there is a calculated textbox control (txtCombinedLen mm AND txtCombinedWeig hts) based on the list box controls (which is also the listbox's underlying query's value). The calculation only uses the control if there is a result in that listbox control.

On the On_Exit of the product number control:
all associated controls are being requeried, the calculated textbox's control source is determined and then the calculated control is requeried:

    Me.txtCombinedLenmm.ControlSource = "=IIf(nz([lstRingSizeGroundmm],0)=0 And nz([lstRingSizemm],0)=0,Round(CDbl(nz([lstSusLenmm],0))+CDbl(nz([lstLPlenmm],0))+146.05,2),CDbl(Round(nz([lstSusLenmm],0)+nz([lstLPlenmm],0)+nz([lstRingSizemm],0)+nz([lstRingSizeGroundmm],0)+146.05,2)))" 

   Me.txtCombinedWeights.ControlSource = "=Round(nz([lstSusWgtKg],0)+nz([lstLPwgtKg],0)+nz([txtKgRingWgtGrnd],0)+nz([txtKgRingWgt],0),2)"

Then

    Me.txtCombinedLenmm.Requery
    Me.txtCombinedWeights.Requery

Is there a way to have the text box recalculate the entire formula with all involved controls without having to get focus and exit each of the controls in the calculation? Neither RECALC, REFRESH, nor REQUERY for the form or controls is working.

Thank you in advance for any help - nothing logical seems to be working.
with ruth

[ADezii]

ruth, try using the Recalc Method which will immediately update all Calculated Controls on a Form. The Syntax is simple, but you must place the Code in an appropriate Context, as in:

Sub SomeControl_AfterUpdate()
  Me.Recalc
End Sub

[Ruth Barn]

Thank you ADezii. Being an unbound form the AfterUpdate doesn't function. I did try recalc in the control_exit sub and it still does not update/recalc the calculated text box control without clicking into and out of the listbox controls used in the calculation. Any other ideas are appreciated.