placing a calculated field result in a table

[ray436]

Hi all,<br><br>this is probably a forest for the trees question, but......<br><br>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.<br><br>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.&nbsp;&nbsp;problem is , the result is not placed in the table, only on the form.<br><br>tried diff events, but none seem to help.<br><br>thanks!

 

[samara]

1)You may make this calculated field in a query, <br>2)Or you may put this value in your table &quot;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&quot;<br><br>Hope this would help YOU... <p>Mohamed Aly<br><a href=mailto:samara_79@hotmail.com>samara_79@hotmail.com</a><br><a href= > </a><br>

 

[Quehay]

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 &quot;AfterUpdate&quot; 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.

 

[ray436]

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.....

thanks,

 

[Amiel]

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 ...

 

[ray436]

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....

aaaaarrrrggggg.

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...

 

[Amiel]

Ray436,

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 &quot;UPDATE [Table1] Set [80Percent] = ((amount_listed)*0.8)&quot;

 

[ray436]

Hi Amiel;

got it to work, used:

Private Sub Form_Current()
[80Percent]=([amount_listed]*0.8))
End Sub


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

thanks.....