AutoUpdate form control

[bobfin]

I have a form that displays 20 records at a time. On each line, 2 controls display table fields & 1 control displays a value calculated from the other 2. I want the value in that control to be recalculated & displayed whenever either of the other 2 values are changed by the form user. Only the value for that line should be changed. I tried putting a macro in the After Update event of the 2 controls. It ran the query that does the calculation, but all lines got changed, even tho the query had a criteria specifying the control.

 

[Jack Cowley]

What is the calculation that you are doing with the two controls? You should be able to do your caculation in the form similar to this in the After Update event of the two controls:

Me.Control3 = Me.Control2*Me.Control1

I multiplied the two controls but you can do any number of simple calculations this way. If you have a long, complex calculation then do it in a Function and call the function from the controls.

hth,
Jack

 

[bobfin]

What is the calculation that you are doing with the two controls? You should be able to do your caculation in the form similar to this in the After Update event of the two controls:

Me.Control3 = Me.Control2*Me.Control1

I multiplied the two controls but you can do any number of simple calculations this way. If you have a long, complex calculation then do it in a Function and call the function from the controls.

hth,
Jack

I had to make a custom function to calculate the value. A query uses this function and specifies that the control for 1 of the field values must equal the table value. This query is the Row Source for the calculated control (a listbox). The After Update event of each of the 2 field controls calls a macro that runs the query. But when I update a value, I get a message that the query is not available for automation. Changing the macro to do a SetValue didn't help.

 

[Jack Cowley]

I am not sure how your query/function works together but if you use the values in the two controls as criteria for the query then a simple requery of the Listbox in the After Update of the control(s) should produce the results you want. I am really only guessing so this suggestion may not be of any help...

Jack

 

[bobfin]

I am not sure how your query/function works together but if you use the values in the two controls as criteria for the query then a simple requery of the Listbox in the After Update of the control(s) should produce the results you want. I am really only guessing so this suggestion may not be of any help...

Jack

After reading your replies and rethinking the whole structure, I realized it didn't have to be so complicated. The solution was to just use a simple textbox whose Control Source is the custom function. Only one calculated value was needed. thanks for your time.

 

[Jack Cowley]

You are welcome and I am glad you got it sorted...

Jack