Update query on (sub)form exit

[Guest]

Hi all,
I have a form linked to an "invoice header".
It contains a subform linked to an "invoice line" table.
On the subform, I have a couple of calculated fields eg [$ amount].
I need this field to update the invoice line table once the user exits the
form.
So I created a "on form exit" command which refers to a sql update query.
What is the syntax to refer to the form field value in sql?
I have tried the following:
update table [invoice line]
set [$ amount] = forms![invoice line].[$ amount]
where [invoice line].[invoice number]= forms![invoice line].[invoice number]
and [invoice line].[line number]=forms![invoice line].[line number]

but I get prompted to enter a value for "forms![invoice line].[$ amount]" -
which leads me to think that I'm using the wrong syntax.

Similarily, I'm updating the invoice header table based on the invoice
header form. The user gets to hit an icon which triggers a sql update query
similar to the one i mentioned above and that one works fine.

Am I using the wrong syntax or is this the wrong approach to update the table?

Thanks a lot!
Kanga

 

[MGFoster]

Hi all,
I have a form linked to an "invoice header".
It contains a subform linked to an "invoice line" table.
On the subform, I have a couple of calculated fields eg [$ amount].
I need this field to update the invoice line table once the user exits the
form.
So I created a "on form exit" command which refers to a sql update query.
What is the syntax to refer to the form field value in sql?
I have tried the following:
update table [invoice line]
set [$ amount] = forms![invoice line].[$ amount]
where [invoice line].[invoice number]= forms![invoice line].[invoice number]
and [invoice line].[line number]=forms![invoice line].[line number]

but I get prompted to enter a value for "forms![invoice line].[$ amount]" -
which leads me to think that I'm using the wrong syntax.

Similarily, I'm updating the invoice header table based on the invoice
header form. The user gets to hit an icon which triggers a sql update query
similar to the one i mentioned above and that one works fine.

Am I using the wrong syntax or is this the wrong approach to update the table?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You should run the UPDATE from the Form_Close event instead of the
Form_Exit event 'cuz at the Form_Exit event all controls are not
available.

If that doesn't work, just build the UPDATE statement in VBA & run it
from the Form_Close event.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQyCfboechKqOuFEgEQJYHwCeLT2AMSggk8j6V2TZF1CmuMoCTfoAnitk
jyTo+ao7QhkbUWYMmVHZBIYx
=Q8lm
-----END PGP SIGNATURE-----