424,289 Members | 1,875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,289 IT Pros & Developers. It's quick & easy.

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

P: 3
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 (txtCombinedLenmm AND txtCombinedWeights) 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:
Expand|Select|Wrap|Line Numbers
  1.     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)))" 
  3.    Me.txtCombinedWeights.ControlSource = "=Round(nz([lstSusWgtKg],0)+nz([lstLPwgtKg],0)+nz([txtKgRingWgtGrnd],0)+nz([txtKgRingWgt],0),2)"
Expand|Select|Wrap|Line Numbers
  1.     Me.txtCombinedLenmm.Requery
  2.     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
Dec 14 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 5K+
P: 8,595
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:
Expand|Select|Wrap|Line Numbers
  1. Sub SomeControl_AfterUpdate()
  2.   Me.Recalc
  3. End Sub
Dec 14 '10 #2

P: 3
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.
Dec 16 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.