Full Version: Setting a calculated field on a form equal to a bound field in a
UtterAccess Forums > Microsoft® Access > Access Forms
Could somebody help me understand something.
On my form I have a couple of text boxes:
'Num_Measured' = unbound calculated textbox which is the number of fish measured for size from data entered in a subform. The user types in the lengths of the fish and the number sized is tallyed.
'Species_Not_Measured' = the number of fish counted but not measured. This value the user types in and is a bound text box to the table
'Total' = a calculated unbound textbox that is equal to the 'num_measured' plus 'species_not_measured'
'Total_Species' = a bound text box assoicated with the table which is the equal to the value in 'Total'
I have VBA code in the "On-Change" event for the 'total' text box that says
me.total_species = me.total
But this isnt working. Do I really need to do ADO to set a bound text box equal to a calculated unbound text box???
It seems like this should be a simple thing so I dont understand why it isnt working. I have code other things in this same form like:
if me.field1 = 5 then me.feild2 = "Sand"
and that updates in the table just fine.
Any suggestions??
Try putting the code in a different event. I think but am not 100% sure that the change event only "fires" as a result of keystrokes into the text box.
You might want to try the On Exit event of the Species_Not_Measured text box, for instance.
Well that would explain it.
There will not always be data entered in 'Species_Not_Measured'. Will this affect the 'on Exit' event?
The reference to "me.total" will not help you in the onChange event of "total".

Try using me.total_species = me.total.text

And, as Frank pointed out, "Change" only fires with keystrokes. (but not referencing the .text will also not work, even if there are keys being input)

That should work,


Edited by: adamsherring on Thu May 4 14:51:24 EDT 2006.
I have cases where no fish were caught at a station therefor nothing would be entered for either 'Species_Not_Measured' or 'Num_Measured' . In these cases, 'Total" is being calculated correctly as 0.
But what event would I have to use to make 'Total_species' = 'Total' when there are no key strokes at all??
Events fire in response to certain activities generated by the user.
HANGE fires when the contents of a control ... change. If the contents don't change, the event doesn't fire.
Similarly, EXIT fires when the user EXITs the control. If the user uses the mouse to bypass that control (never entering it, and thus never exiting it), EXIT won't fire.
Your best bet here might be to NOT have TOTAL_SPECIES be bound to anything. It can then have, as it's recordsource, a calculated expression, so you don't need to be updating its value in code AT ALL. Then, in the BEFOREUPDATE event of the form (which fires before a record is saved), update the bound field in the underlying recordset (not the control on the form) to store the proper value.
HOWEVER, as a general rule, you don't store calculated values in a table. You store the component parts, and can always re-do the calculation when needed.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.