Update a field in a form

[Steve Walker]

Access 2000 on Win XP. I have a form that I have based on a query from three
tables. Three of the fields are [StyleCharge], [StylistCharge] and
[TotalCharge]. When the query is run both Style and Stylist prices are shown
after they have been selected from two combo boxes.

I am trying to update [TotalCharge]. Can't do it in the query because the
style and stylist are chosen on the form. I need some method of updating the
TotalCharge field.

Created a macro and put it in the beforeupdate event. The macro is a
setvalue to set the value of the TotalCharge field by adding StyleCharge +
StylistCharge together.

The expression is: [Forms]![frmAppointment]![StylistCharge] +
[Forms]![frmAppointment]![StyleCharge]

The item is: [Forms]![frmAppointment]![TotalCharge]

This doesn't seem to be successful. I would have thought that the expresson
would add the two fields together which would then update the TotalCharge
field. Any ideas or help on this would be appreciated. I think I've
misunderstood the expression needed.

Steve

 

[Steve Schapel]

Steve,

A SetValue action in the macro should do what you want.
Item: [TotalCharge]
Expression: [StylistCharge]+[StyleCharge]

However, to do so appears to be flouting a basic principle of database
design. It seems to me that there should not even be a TotalCharge
field at all. All it is doing is storing a vlaue which can be derived
from existing data. This is the type of thing that you sometimes see in
spreadsheets and stuff like that, but is not really applicable in a
database. A better approach would be to scrap the TotalCharge field,
and to put an unbound textbox on the form, with its Control Source
property set to...
=Nz([StylistCharge],0)+Nz([StyleCharge],0)
Similarly, you can calculate this value whenever you need it for your
purposes on form or report, either in the control source of a textbox on
the form or report itself, or within the query that the form or report
is based on.

 

[Steve Walker]

Some good sound advice here Steve - wasn't in thinking mode. Nice one.
Ta very much

Steve

Steve,

A SetValue action in the macro should do what you want.
Item: [TotalCharge]
Expression: [StylistCharge]+[StyleCharge]

However, to do so appears to be flouting a basic principle of database
design. It seems to me that there should not even be a TotalCharge
field at all. All it is doing is storing a vlaue which can be derived
from existing data. This is the type of thing that you sometimes see in
spreadsheets and stuff like that, but is not really applicable in a
database. A better approach would be to scrap the TotalCharge field,
and to put an unbound textbox on the form, with its Control Source
property set to...
=Nz([StylistCharge],0)+Nz([StyleCharge],0)
Similarly, you can calculate this value whenever you need it for your
purposes on form or report, either in the control source of a textbox on
the form or report itself, or within the query that the form or report
is based on.

--
Steve Schapel, Microsoft Access MVP

Access 2000 on Win XP. I have a form that I have based on a query from three
tables. Three of the fields are [StyleCharge], [StylistCharge] and
[TotalCharge]. When the query is run both Style and Stylist prices are shown
after they have been selected from two combo boxes.

I am trying to update [TotalCharge]. Can't do it in the query because the
style and stylist are chosen on the form. I need some method of updating the
TotalCharge field.

Created a macro and put it in the beforeupdate event. The macro is a
setvalue to set the value of the TotalCharge field by adding StyleCharge +
StylistCharge together.

The expression is: [Forms]![frmAppointment]![StylistCharge] +
[Forms]![frmAppointment]![StyleCharge]

The item is: [Forms]![frmAppointment]![TotalCharge]

This doesn't seem to be successful. I would have thought that the expresson
would add the two fields together which would then update the TotalCharge
field. Any ideas or help on this would be appreciated. I think I've
misunderstood the expression needed.

Steve