OnChange Event Changes all records?!

[Simon Harris]

Hi All,

I have an on change event on a text box which does the following:

If Me.GasMeterType.Value = "m²" Then
Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
DLookup("Gas_Per_Mtr3", "tbl_constants")
ElseIf Me.GasMeterType.Value = "ft²" Then
Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
DLookup("Gas_Per_Ft3", "tbl_constants")
Else
MsgBox ("Warning: No gas meter type recorded for this pitch")
End If

Trouble is, when fired, this updates the GasAmount field for all
records...not just the current record as I expected. The form is normally in
data sheet mode, although I have tried continuous form, which causes the
same problem.

Odd? Or am I not understanding something here?

Thanks,
Simon.

--
-
* Please reply to group for the benefit of all
* Found the answer to your own question? Post it!
* Get a useful reply to one of your posts?...post an answer to another one
* Search first, post later : http://www.google.co.uk/groups
* Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!



--

 

[Guest]

Is it the textbox that is changed for all fields an unbound control? Unbound
controls are controls that aren't linked to an actual field in your table or
query.

 

[Simon Harris]

Yes, it is an unbound control - I want to display a calculated amount (For
display purposes only).

Graham - I have tried as you suggested, setting the controlsource property
to the output of the function only works on load of the form, it does not
update when I change the values.

I've also tried changing the afterUpdate event to call the function - This
causes the original problem of all the records being updated with the
calculated value.

Help!?

 

[Simon Harris]

Graham - I have tried as you suggested, setting the controlsource property

to the output of the function only works on load of the form, it does not
update when I change the values.

Ok, its working now. What I did was write the function as Graham suggested.
Set the control source of the field displaying the calculated value to
=CalcGasAmount, then set the AfterUpdate event of the new Gas reading field
to requery the field displaying the calculated value.

Thanks again for your help guys.

Simon.

 

[Simon Harris]

Graham - I have tried as you suggested, setting the controlsource property

to the output of the function only works on load of the form, it does not
update when I change the values.

Ok, its working now. What I did was write the function as Graham suggested.
Set the control source of the field displaying the calculated value to
=CalcGasAmount, then set the AfterUpdate event of the new Gas reading field
to requery the field displaying the calculated value.

Thanks again for your help guys.

Simon.



--

 

[Graham Mandeno]

Hi Simon

Glad you got it working. You can possibly trick Access into doing the
requery for you if you pass [Gas_Reading] to your function as an argument.

=CalcGasAmount([Gas_Reading])

You don't have to actually use it, but you must declare it as a dummy
argument:

Private Function CalcGasAmount(vJunk as Variant)

However, my main question is, have you tried or even considered setting up a
related table of units and conversion factors as I suggested?