Calculated Default Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm making an estimate sheet and want to create a default value that is based
on other form/table values.
I know I should just do a query calc and not store the total but I can't
trust the salespeople to do the math properly and ultimately we have to stick
by the price so I have to store 3 values. The math is simple: HorzFt x
HorzPrice = HorzTot when I tab/click over to the HorzTot field on the form I
want it to calcuate the default total (but I still want to be able to change
it if necessary).

I went to the properties area of the TextBox for HorzTot and under default
value put =[horzft]*[horzprice] but nothing happens. Any ideas?
 
Ian,

The Default Value property of a control only applies at the point where
a new record is being first created. So by the time you have data
already in the HorzFt and HorzPrice fields, it's way too late.

Here's an idea... On the Enter event of the HorzTot control, put code
like this...

If IsNull(Me.HorzTot) then
Me.HorzTot = Me.HorzFt * Me.HorzPrice
End If

By the way, your argument for the need to store this calculated value is
not convincing.
 
You might also want to recalculate HorzTot in the after update event of
HorzFt and HorzPrice:

if me!HorzFt > 0 and me![HorzPrice] > 0 then
me![HorzTot] = me!HorzFt * me![HorzPrice]
endif

I'm not sure if I like the idea of being able to change HorzTot "if
necessary" - why would it be? If I were a customer (or an auditor) I
would certainly want any discrepancy explained.

John
 
Thank you so much -- I'll force the total cell and that way they can't make a
mistake on multiplication. I screwed up something horribly though in trying
to correct this. My form has data from two tables ICDATA and ESTIMATES.
Somewhere along the line I've changed controlsources so that if my form
control source is set to ICDATA all my cells from [estimates] show #NAME? and
if I set the from control source to ESTIMATES all my cells from ICDATA show
the same. Any idea what I've done? My relationship is defined by a field
called ID which is one-to-many to the estimates table.

Steve Schapel said:
Ian,

The Default Value property of a control only applies at the point where
a new record is being first created. So by the time you have data
already in the HorzFt and HorzPrice fields, it's way too late.

Here's an idea... On the Enter event of the HorzTot control, put code
like this...

If IsNull(Me.HorzTot) then
Me.HorzTot = Me.HorzFt * Me.HorzPrice
End If

By the way, your argument for the need to store this calculated value is
not convincing.

--
Steve Schapel, Microsoft Access MVP
I'm making an estimate sheet and want to create a default value that is based
on other form/table values.
I know I should just do a query calc and not store the total but I can't
trust the salespeople to do the math properly and ultimately we have to stick
by the price so I have to store 3 values. The math is simple: HorzFt x
HorzPrice = HorzTot when I tab/click over to the HorzTot field on the form I
want it to calcuate the default total (but I still want to be able to change
it if necessary).

I went to the properties area of the TextBox for HorzTot and under default
value put =[horzft]*[horzprice] but nothing happens. Any ideas?
Click to expand...
Click to expand...
 
Ian said:
Thank you so much -- I'll force the total cell and that way they can't make a
mistake on multiplication. I screwed up something horribly though in trying
to correct this. My form has data from two tables ICDATA and ESTIMATES.
Somewhere along the line I've changed controlsources so that if my form
control source is set to ICDATA all my cells from [estimates] show #NAME? and
if I set the from control source to ESTIMATES all my cells from ICDATA show
the same. Any idea what I've done? My relationship is defined by a field
called ID which is one-to-many to the estimates table.

:

Ian,

The Default Value property of a control only applies at the point where
a new record is being first created. So by the time you have data
already in the HorzFt and HorzPrice fields, it's way too late.

Here's an idea... On the Enter event of the HorzTot control, put code
like this...

If IsNull(Me.HorzTot) then
Me.HorzTot = Me.HorzFt * Me.HorzPrice
End If

By the way, your argument for the need to store this calculated value is
not convincing.

--
Steve Schapel, Microsoft Access MVP
I'm making an estimate sheet and want to create a default value that is based
on other form/table values.
I know I should just do a query calc and not store the total but I can't
trust the salespeople to do the math properly and ultimately we have to stick
by the price so I have to store 3 values. The math is simple: HorzFt x
HorzPrice = HorzTot when I tab/click over to the HorzTot field on the form I
want it to calcuate the default total (but I still want to be able to change
it if necessary).

I went to the properties area of the TextBox for HorzTot and under default
value put =[horzft]*[horzprice] but nothing happens. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Hi -

for the form source, create a query which joins the two tables as
required, and make the query the recordsource for the form. I suggest
you try running the query first in the query design window, to make sure
it is updatable.

Another little thing you can do is set the 'locked' property of the
HorzTot control to "Yes", so that is can be calculated by the
multiplication of the other two, but cannot be changed manually.

John

Thank you so much -- I'll force the total cell and that way they can't make a
mistake on multiplication. I screwed up something horribly though in trying
to correct this. My form has data from two tables ICDATA and ESTIMATES.
Somewhere along the line I've changed controlsources so that if my form
control source is set to ICDATA all my cells from [estimates] show #NAME? and
if I set the from control source to ESTIMATES all my cells from ICDATA show
the same. Any idea what I've done? My relationship is defined by a field
called ID which is one-to-many to the estimates table.

:

Ian,

The Default Value property of a control only applies at the point where
a new record is being first created. So by the time you have data
already in the HorzFt and HorzPrice fields, it's way too late.

Here's an idea... On the Enter event of the HorzTot control, put code
like this...

If IsNull(Me.HorzTot) then
Me.HorzTot = Me.HorzFt * Me.HorzPrice
End If

By the way, your argument for the need to store this calculated value is
not convincing.

--
Steve Schapel, Microsoft Access MVP
I'm making an estimate sheet and want to create a default value that is based
on other form/table values.
I know I should just do a query calc and not store the total but I can't
trust the salespeople to do the math properly and ultimately we have to stick
by the price so I have to store 3 values. The math is simple: HorzFt x
HorzPrice = HorzTot when I tab/click over to the HorzTot field on the form I
want it to calcuate the default total (but I still want to be able to change
it if necessary).

I went to the properties area of the TextBox for HorzTot and under default
value put =[horzft]*[horzprice] but nothing happens. Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top