Update table on (sub)form exit

[Kanga]

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

 

[Donald King]

Kanga,
Here's the way I display a total on the master form of a calc. field
that's on a subform of the master. The subform has a text box called
txtbxBalance that has a calculation let's say of ItemsIn minus ItemsOut, i.e.
Sum(nz(ItemsIn) - nz(ItemsOut)). On the master form I put a text box to
display the total balance and in the Control Source input: =[Invoices
Subform].Form!txtbxBalance
You could then in the AfterUpdate event of the txtbxTTLBalance you could
code:
Me.fieldTTLBalance = Me.txtbxTTLBalance. If you don't want the
txtbxTTLBalance to be seen, make it's Visible property = No.
Hope that helps.

Don

 

[Kanga]

Hi Don,

Thanks for your feedback. I have already created this total column on the
subform which then updates the header table with a command. My problem is
that the line table also need to be updated based on the subform calculated
field. My subform is in the form of a datasheet view so I would like it to be
that when people are exiting the subform all the related lines update the
line table. I just don't know how to refer to the subform calculated fields
in my sql update query.

Thanks!
Kanga

Kanga,
Here's the way I display a total on the master form of a calc. field
that's on a subform of the master. The subform has a text box called
txtbxBalance that has a calculation let's say of ItemsIn minus ItemsOut, i.e.
Sum(nz(ItemsIn) - nz(ItemsOut)). On the master form I put a text box to
display the total balance and in the Control Source input: =[Invoices
Subform].Form!txtbxBalance
You could then in the AfterUpdate event of the txtbxTTLBalance you could
code:
Me.fieldTTLBalance = Me.txtbxTTLBalance. If you don't want the
txtbxTTLBalance to be seen, make it's Visible property = No.
Hope that helps.

Don

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