Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

placing a calculated field result in a table

placing a calculated field result in a table

placing a calculated field result in a table

Hi all,

this is probably a forest for the trees question, but......

on our main form I have added a calculated field that is mainly for the users convenience. it returns 80% of another field so the user can tell some our customers what their discount would be.

it works great, but sometimes we need a quote on paper, and the mail merge that uses this database now needs to be able to use this result.  problem is , the result is not placed in the table, only on the form.

tried diff events, but none seem to help.


RE: placing a calculated field result in a table

1)You may make this calculated field in a query,
2)Or you may put this value in your table "Add a field in table, and add this field to your form but make it invisble then you can set its value by VBA code"

Hope this would help YOU...

Mohamed Aly

RE: placing a calculated field result in a table

The trick is using code to calculate the value for the dependent field and setting the datasource to the desire table field (of course that needs to exist); place the code in the "AfterUpdate" event of the box where the value for calculation is entered, e.g.,

Sub Mytext_AfterUpdate
Dim SourceValue as Integer, NewValue as Integer

SourceValue = txt1
NewValue = (SourceValue X 10 / 3.14 )/360

*txtCalculated = NewValue

*Using the default text property which is text.value

This gives you the calculation without making the calculation the control source for the txtbox--then you set the control source as the field from the table where the calculated value should be stored.  You'll get a calculated value in the form that changes whenever the source value is changed and store it as well.  Caveat:  Don't allow datasheet access to the table as this circumvents the event procedure.

RE: placing a calculated field result in a table

thanks quehay;

tried it but i'm doing something wrong. (not a first)
let me give you ID's that i'm using and maybe you could show me where i went off the road.

80% discount field on form =  80percent
source to base calc. on    =  amount_listed

thus i did this;

Sub 80percent_AfterUpdate
Dim SourceValue as Integer, NewValue as Integer

SourceValue = [amount_listed]
NewValue = ((amount_listed)*0.8))
[80percent]= NewValue

i bet it is really obvious what i did, but it has been one of those days.....


RE: placing a calculated field result in a table

If I may but in here. Seems, not only a forest and tree thing, but a chicken and the egg problem here as well.

As you mentioned in the origional post you have the calculation on the form in a text box. Yo just need to get that piece of information in the table so that when the mail-merge executes, it will be available to that process.
Samara kinda had it except that the calculated value does not exist until after the forms fields are filled with the records data. And the record still has to be saved to the table. So the 'added' field that was mentioned would contain nothing (even though the text box on the form might).

The record would need to be updateed either with a query or by saving the (changed) record before the mail merge occoured in order for the information to be in the record (and available for printing).

Atttach/add some 'save' code prior to the print action.
Let me know ...


RE: placing a calculated field result in a table

thanks Amiel;

you have the idea, but no matter what i try --- ¦:{

i have tried to use events, create a hidden field and link the data, ( [xxx.xx]=[yyy.yyy], etc and on ......)

the help from MS brings me right to the point.. then says oh by the way this result does not end up in the table....


i did use a query to do it once and ended up with more work cause i had to run the jobs myself - staff would not get it right somehow.  (users -bah) ;)

-- can i do an expression in a reformat query that reproduces a table with only the needed #'s ? i can make that run via login MsgBox... just a thought...

RE: placing a calculated field result in a table


1. Add a field in the table to hold the '80% calculation'.
2. Add that new field to the form. Set the visible property to false if you choose.

3. Use one or more of the following.

Create 80% value after input of amount_listed on form.
Private Sub amount_listed_Exit()
  [80Percent]= CDbl((amount_listed)*0.8))
End Sub

Calculate 80% as record is displayed.
Private Sub Form_Current()
  [80Percent]= CDbl((amount_listed)*0.8))
End Sub

' update all of the records at one time. Perhaps using a buttone to execute it.
DoCmd.RunSQL "UPDATE [Table1] Set [80Percent] = ((amount_listed)*0.8)"


RE: placing a calculated field result in a table

Hi Amiel;

got it to work, used:

Private Sub Form_Current()
End Sub

it got ugly if i tried to use the "double expression"
not sure why, but atleast it works!


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close