Ms-access – Update the table with the calculated field of a form in Access

ms-access

I am very new to the Acces and i am trying to calculate the Earned salary of the employee based on the no of Worked days.
For this i have created a Form to input the no of days present and calculate the earned salary based on the input.Now i want to pass this earned salary value to the table field.
Can you please help me

Best Answer

Firstly some house-keeping; With your field names it is best not to have any spaces in them, makes it much easier to code (from your SQL string it looks like you may have done this but just checking). Also, avoid using reserved words as field names eg don't use Name as a field name, there is a list of Visual Basic reserved words here which will cover everything VBA and a little bit more.

Secondly, it seems you are storing a calculated field - EarnedSalary is a calculation of BasicPay, TotalWorkingDaysInMonth and ActualWorkedDays. Generally storing calculated fields is considered a no-no, ref Allen Browne's page on calculated fields. On this page Allen gives some tips on calculated fields that may help, especially if you decide you do want to store it and use Access 2010 (I'm only on 2007...).

As for your question, you still didn't tell me where your error was so it is hard to pin-point the issue. My guess is that you are calling the update Sub in the wrong place - you want to update EarnedSalary but you call the process in the AfterUpdate of that control, therefore the code won't fire until you update the field the code is meant to update. The code needs to be in the AfterUpdate of ActualWorkedDays.

Further, it looks like you are using a bound form which would mean you don't need to use the SQL update, a solution akin to Allen's example would suffice:

On the form if you have a control for each of the fields involved in the calculation:

Private Sub ActualWorkedDays_AfterUpdate()
     Me.EarnedSalary = Me!BasicPay / Me!TotalWorkingDays * Me!ActualWorkedDays
End Sub
Related Topic