FORMS

  • Thread starter Thread starter AFKAFB
  • Start date Start date
A

AFKAFB

I've a form bound to a table with a number of fields. A text box
calculates a value and shows it on the form.
However, i need to record this calculated value on the table.
My workaround is to run an update query when the form unloads which
updates the table. Is there a better way of doing this as i'm
concerned that i could have one formula in the text box and a different
formula in the update query.
 
AFKAFB said:
I've a form bound to a table with a number of fields. A text box
calculates a value and shows it on the form.
However, i need to record this calculated value on the table.
My workaround is to run an update query when the form unloads which
updates the table. Is there a better way of doing this as i'm
concerned that i could have one formula in the text box and a different
formula in the update query.
Click to expand...

1. Don't do it. Storing calculated data in the database is poor design.
Recalculate it in your forms, reports and queries whenever you need to
output it.

2. If you insist, do it in the form's BeforeUpdate event, e.g.

[CalculatedField] = txtCalculatedTextBox
 
Hi Baz
Thanks for info.
I'm going to be bloody minded on this one.
I'm a tad confused though.
Say i've three fields: Quantity, Price & Total
These fields are bound to a form.
I can add a formula in the data source property for Total, which would
be Quantity * price.
that part is fine
but in the beforeupdate event how would i write the formula for the
value to update the table.
Am i right in assuming that i cannot have both a formula shown on the
form and to update the table
chris
 
AFKAFB said:
Hi Baz
Thanks for info.
I'm going to be bloody minded on this one.
I'm a tad confused though.
Say i've three fields: Quantity, Price & Total
These fields are bound to a form.
I can add a formula in the data source property for Total, which would
be Quantity * price.
that part is fine
but in the beforeupdate event how would i write the formula for the
value to update the table.
Am i right in assuming that i cannot have both a formula shown on the
form and to update the table
chris
Click to expand...

As soon as you set the data source of a control to be a formula, that
control becomes a calculated control and is unbound.

In your BeforeUpdate event:

[Total] = [Quantity] * [Price]

However, it would be a good idea to rename your calculated control to, say,
txtTotal, in order to avoid confusion with the field named "Total".

Being bloody-minded is of course your privilege, but it doesn't alter the
fact that storing calculated data in your database breaks one of the most
basic rules of database design - and for no good reason, if all it is is a
simple multiplication of two other fields.
 
Baz

Thanks for helping me again. Much appreciated.

I did mean 'bloody-minded' in the nicest possible way...

I'm being really dense here

If I insert the formula =([Quantity] * [Price]) in the Control Source
property on the Data Tab this returns the value on the form as
expected.

However, it's the Before Update Event I'm struggling with.

Do I insert "[Total] = [Quantity] * [Price]" as a formula in the
Expression Builder or is it to appear as code ....

In essence what I'm trying to do is show the results of the
calculation on both the form and to update the table as well.

Is it impossible to do both on the same control...

Can you please tell me exactly what to insert in the Control Source
Property and the BeforeUpdate Event property before i lose the rest of
my hair...

I accept your point re database design rules but for now I would like
to update the table with this result.

Regards

Chris
 
AFKAFB said:
Baz

Thanks for helping me again. Much appreciated.

I did mean 'bloody-minded' in the nicest possible way...

I'm being really dense here

If I insert the formula =([Quantity] * [Price]) in the Control Source
property on the Data Tab this returns the value on the form as
expected.

However, it's the Before Update Event I'm struggling with.

Do I insert "[Total] = [Quantity] * [Price]" as a formula in the
Expression Builder or is it to appear as code ....

In essence what I'm trying to do is show the results of the
calculation on both the form and to update the table as well.

Is it impossible to do both on the same control...
Click to expand...

Yes. You need one control to display the calculated expresssion and another
(hidden) one to "push" the result into with code. The hidden one being bound is
what saves it to your table.

I will reiterate that this is a *really* stupid thing to do though. Imagine a
friend coming to you with the following...

I'm using Excel and I want three columns of numbers where the third column is
the product of the values in the first two columns. Which would you advise him
to do...

1) Enter =[A1] * [B1] into cell [C1] and then copy that down the column

or

2) Create a macro that loops through all of the rows, does the math and then
stores the hard value result into column C. Then inform him that every time he
changes a value in any column that he has to remember to re-run the macro.

The second option sounds pretty absurd doesn't it? It is functionally
equivalent to storing your result into the table.
 
Rick
I feel suitably chastised now.
But can you please tell me where and what exactly i need to type in
control for the value to get bound back to the table
i know what you're saying but i want to see it work first.
its driving me up the wall
i've bound the data control source to the total field but i cannot seem
to write a formula in the before update event property for this to work
chis
 
AFKAFB said:
Rick
I feel suitably chastised now.
But can you please tell me where and what exactly i need to type in
control for the value to get bound back to the table
i know what you're saying but i want to see it work first.
its driving me up the wall
i've bound the data control source to the total field but i cannot seem
to write a formula in the before update event property for this to work
chis
Click to expand...

If the name of the control were txtTotal then in the BeforeUpdate event...

Me.txtTotal = Me.Quantity * Me.Price
 
Rick
I musat be either blind or mad - so before you lose total patience with
me
this is what i've done

i've bound the control source to the data field 'total' in the table

On the Other tab i've called the name property 'txttotal'

in the event tab for 'Beforeupdate' i've inserted Me.txtTotal =
Me.Quantity * Me.Price

the control has been set to invisible on the form

i've saved the form

i run the form

i populate the quantity fierld and price field and i can see the total
field on the form updating as usual but the table does not update.

any idea what i'm doing wrong

chris
 
A text box calculates a value and shows it on the form. However, i need
to record this calculated value on the table.
Click to expand...

take that textbox control and remove the calculation from the ControlSource
property. set the ControlSource property to the name of the table field
where you want the calculated value to be stored. (make sure that field is
included in the form's RecordSource.) suggest you set the control's Locked
property to Yes, so the user can't manually change the calculated value.

in the form's BeforeUpdate event, add code to assign the calculated value to
the textbox control, as

Me!MyTextboxControlName = (Me!Quantity * Me!Price)

if you want the value to be recalculated and assigned as soon as a change is
entered in Quantity or Price, then add the code to the AfterUpdate events of
both of those controls (instead of using the form's BeforeUpdate event).

btw, and just for the record, i do concur with the other opinions re storing
calculated values as hard data in tables.

hth
 
tina
if the text control name is 'txttotal' should the code then read as

Me!txttotal = (Me!Quantity * Me!Price)

and this goes in the Forms Before update event

chris
 
tina
i'm now getting a message
'MS Office can't find the macro 'Me!txttotal=(Me!Quantity * Me!Price)
am i just useless......
chris
 
AFKAFB said:
Rick
I musat be either blind or mad - so before you lose total patience with
me
this is what i've done

i've bound the control source to the data field 'total' in the table

On the Other tab i've called the name property 'txttotal'

in the event tab for 'Beforeupdate' i've inserted Me.txtTotal =
Me.Quantity * Me.Price
Click to expand...

Whoa, there's the problem. You don't enter code directly into the event
property box. From the list of drop-down choices in that box choose "[Event
Procedure]", then press the build button [...] to the right. That will open the
code window and that is where you enter the code.
 
"Whoa, there's the problem. You don't enter code directly into the event
property box. From the list of drop-down choices in that box choose "[Event
Procedure]", then press the build button [...] to the right. That will open
the
code window and that is where you enter the code."

from Rick's earlier reply in this thread. and to tack a bit onto it: when
the code window opens, your cursor will already be at the spot where you
need to insert the code - so don't move it unless/until you are sure you can
find your way back to the same spot!
am i just useless......
Click to expand...

no, not at all. you're not too familiar with the Access software, i think,
and perhaps a bit timid about digging through Access Help for clues.
(although i will say that Access Help is very frustrating to work with
unless you already know a lot about what you're trying to do - and even
then...!) and we're all trying to give you solutions that require a certain
skill level or at least familiarity with both the software and the
nomenclature. this is a common situation here in the newsgroups, because
Access is way too complex to actually teach someone to use it in this
forum - so we all have to settle for trying to communicate specifics.
suggest you get a good basic book on Access, if you haven't got one already.
you can learn basic techniques while at the same time learning the meanings
and proper use of terms - fields versus controls in a form, for instance
(they're not the same thing). the result will be a solid foundation on which
to build more advanced techniques, and a common vocabulary which will allow
you to communicate database issues clearly, when you need to, and understand
the responses better.

meanwhile, hopefully we've all helped you with a solution to your current
question (though we all agree that it's not a good setup you're building).
:)

hth
 
AFKAFB said:
tina
i'm now getting a message
'MS Office can't find the macro 'Me!txttotal=(Me!Quantity * Me!Price)
am i just useless......
chris
Click to expand...

Hi Chris,

Maybe you've got it sorted by now with Rick and Tina's help, but, just in
case you haven't, here is my step-by-step guide to what to do:

1. Create a text box control on your form and name it "txtTotal"
2. In the properties window for txtTotal, set it's control source to the
following:

=[Quantity] * [price]

3. With the form still in design view, open the code window (View
menu-->Code)
4. At the top of the code window, there are two drop-down lists. In the
left hand one, choose "Form", and then in the right-hand one, choose
"BeforeUpdate"
5. At the insertion point, type the following:

[Total] = [Quantity] * [price]

i.e. you should finish up with an event procedure looking like this,
although Access provides the first and last lines for you, you only need to
enter the middle one:

Private Sub Form_BeforeUpdate(Cancel As Integer)
[Total] = [Quantity] * [price]
End Sub

6. Note that there is no need to create a hidden text box.
7. Observe that it now works, congratulate yourself on everything you
have learned, and then remind yourself that this is STILL a bad idea!
 
All
that worked
thank you
i can see now why its not perfect and far from ideal
i've posted another question earlier today called 'Database Design'
which is concerning me more - if you're able to could you please take a
look at it.
thank you
chris
 
Back
Top