How to populate a table from a calulated field in a form

[wm2006]

HI, I need help,
I am trying to populate a table using calculating fields in a form.
here is an example using only two of my text boxes:

frmRAINSP has several calculating fields, but, lets use only one to make it easy to understad.

TotalE textbox
Control Source = 5.5+([E1]*0.5)+([E2]*2)+([E3]*0.5)+([E4]*0.5)+([E5])+([EM])

E1, E2...EM are check boxes that the end user will select
and TotalE will have the total points.

The tricky part for me is how can I send the values to a form.
I named the form frmGrandTotals, the idea is to create a graph in a separate form.

I hope you guys can help me.

Thank you in advance.

[GolferGuy]

Your calculated field is named TotalE as I read your request. In order to get this calculated value into a table field, you would "bind" your table field to a hidden field on your form. Then you will need the following code:
Me.tablefieldname = TotalE
This will need to be done within an event on your form, best done in the AfterUpdate event of each field used in your calculated field. By doing this within each field's AfterUpdate event, then you will always get the total value after it is completly calculated.
HTH,
Vic

[hgeron]

Let's suppose that your table has a field named [answer], and the other variables are as you listed. On your form, you have a cell for [answer].
Then in the code for your form, include this sub...

Sub updateanswer()
answer = 5.5 + ([e1] * 0.5) + ([e2] * 2) + ([e3] * 0.5) + ([e4] * 0.5) + ([e5]) + ([em])
'then to refresh the form with the answer....
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

Then for the e1 "inputs", for the after update event put this...

Private Sub e1_AfterUpdate()
updateanswer
End Sub

(use same, but substute e2... to ...em, for the other input boxes.)


This will fix you up.

[hgeron]

If E1, E2 and EM are really check boxes, then the values would be either 0 or -1 for each, right?

[GolferGuy]

hgeron,

I don't know what
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
is doing, but I would think there is a docmd.RunCommand available to replace it.

Vic

[hgeron]

Maybe, but if you compile the macro command for REFRESH the query, that is what you get.

[hgeron]

or maybe, it was the code behind a "Refresh" the form button.

[wm2006]

I had that already, but still not updating.
Thank You for your responce.
thank you.

[wm2006]

Yes, the check boxes are returning -1, and 0s, and according to that is that I had put the calculations...

I am going to try putting a sub and and a code behind the after_update as you suggested.


Thank you hgeron and GolferGuy.