Solved: Updating a table from a form

[lostin_space]

hi all... if i had hair, i'd have torn it out, long ago...

basically, i've got a form which records (amongst other things) no. of days worked, each month...

in my form, i've got calculating fields, which multiply a) the number of days worked, by a day rate b) quarterly balances c) yearly...

now, i've tried an update query, i've tried an append query..

can someone please give me the '101' on updating data in a table, from a form, or, update / append query 101 stuff??

i'm loosing the will to live, and i'm sure it's basic stuff...

Cheers

Russ

[Norie]

Russ

You shouldn't store calculated values in a table, that's what queries are for.

If you do store them in a table then you would need to run an update/append query whenever you added/changed records.

[lostin_space]

i tried an update query, and that does'nt seem to work... it just prompts me to enter the value that should be picked up from the field...

i also tried an append query, and that does similar...

help! :dunno

[OBP]

Russ, what Norie meant was it is not good practice to store the calculated data in the table unless it has some real benefit.
The calculations should be carried out in the Query and then presented in a Form or Report not put in the table.
If you really must have them in the table then the fields can be updated on the Form using VBA.

From the description of the type of calculation that you are trying to do they should definitely not be saved, only claculated and presented.

[lostin_space]

ok, many thanks for that. Can you give me some idea of how to do this in VBA?

i've tried using an update query, using a calculated field & an also an append query, and they've just gone screwey & dumped fresh piles of crap data all over the shop...

an opening gambit & a point in the right direction would be much appreciated.

[OBP]

Russ, do you need to update the table?
If so, we can do that, if you just want to show the calculated values in your form then that is easy.
Lets take the case of the Days Worked * Day Rate.
To display it on the form you would need a text box on the form.
In the Form's "On Currrent" Event Procedure you need to enter
me.textboxname = me.DaysWorked * me.DayRate

Where textboxname is the name of your text box.
Each time that you move to a record it will calculate the value and put it in the text box. If you want it to update if you change the value in the Days worked or day rate fields then you put the same code in the "After Update" event procedure of each of those two fields.
If you want me to do it for you let me know

[GaryB]

OBP,

Will putting that code, you mentioned above, as an event proceedure rather that a formula in a text box, say =[daysworked]*[dayrate], allow the table to accept the final caculation. I know that any caluclations I have on a form does not write to the table.

Gary

[OBP]

Gary, the post above is only to display the Calculations on a Form.

Gary and Russ, the attached database shows how to
1. Calculate a value in a Form's On current event VB and store it in a Table's field (Total Pay).
2. Recalculate the same field if either of the other 2 fields values change, this vba is identical but in the in 2 field's After Update events.
3. Do all of the above for just displaying the data in a text box called "Display Total Pay"
4. Show how to do the calculation in a query called Running Calcs and the use it in a form and report.
5. How to requery the Running Calcs form if either of the 2 field values change.

I hope you find this helpful.

[lostin_space]

so, am i right in saying, the data to be displayed on your form is the result of a query to your table? and buy then changing the data on your form & re-querying, you change that?

Yours, confused,

Berkshire.

[OBP]

Berkshire, there are 2 seperate methods used in the database that I posted.
Have you looked at the database?
The first method uses Visual Basic to either just display the Calculation or actually put the calculated value in to the table.
The second method uses a Query to do the calculation, but it only displays the calculation in the form or report. IT DOESN'T PUT IT IN THE TABLE.

If you would like me to do it for you just let me know.

[lostin_space]

is this the code you refer to?

[VBA][Private Sub Days_Worked_AfterUpdate()
Me.Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
Me.Display_Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
End Sub
Private Sub Form_Current()
Me.Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
Me.Display_Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
End Sub
Private Sub Pay_Per_Day_AfterUpdate()
Me.Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
Me.Display_Total_Pay = Me.Days_Worked * Me.Pay_Per_Day
End Sub]

[OBP]

Russ, yes that is the code.
This line

Me.Total_Pay = Me.Days_Worked * Me.Pay_Per_Day

is the one that updates the Table field called Total Pay.

This line

Me.Display_Total_Pay = Me.Days_Worked * Me.Pay_Per_Day

just shows the calculation in the form's text box called Display Total Pay.

The code is in the 3 different positions so that the value is calculated when you open the form, update the Days worked or update the Pay Per Day.


This level of VBA is very easy isn't it?

[lostin_space]

it is easy - if you're familiar with Access & the VBA syntax it uses... i've never used access 'in anger', until a colleague asked me to create something for him - hence the 101 level questions...

i've got it working now - thanks for your help (and patience)

[OBP]

Russ, you are right, you do have to get familiar with any language, I was just pointing out that there aren't lot's of complex coding when you want to do the simpler things. That is why these forums are so good, they get you an introduction, hopefully at the correct level for you. (but not always unfortunately, we all forget how hard it was to start with).

If you need any more help just let us know.

[lostin_space]

Cheers OBP