Subform text field

[Sharon Hague]

Hi All

I have a form with a subform created through a tab control. The subform displays salary information for that employee from when their employment started. This subform has six fields which are Employee ID, Date From, Date To, Salary, Position & Department.

I want to be able to create a text box which calculates the percentage increase/decrease for each time the salary changes.

Is this possible?

I would appreciate any help on this.

Thanks

 

[WayneRyan]

Sharon,

I don't know if you want to track their ENTIRE history of
salary actions. That will require a new table:

You can put the following into the AfterUpdate event of
the Salary field and it will update the current SalaryChange:

If IsNull(Me.Salary.OldValue) Then
   MsgBox("This is a new employee, No change required.")
   Exit Sub
End If

Me.SalaryChange = (Me.Salary / Me.Salary.OldValue) * 100

If you were saving all changes to a new table:

Dim dbs As DataBase
Dim sql As String

If IsNull(Me.Salary.OldValue) Then
   MsgBox("This is a new employee, No change required.")
   Exit Sub
End If

Set dbs = CurrentDb
Me.SalaryChange = (Me.Salary / Me.Salary.OldValue) * 100
sql = "Insert Into SalaryHistory(EmployeeID, SalaryDate, Salary) " & _
      "Values (" & Me.EmployeeID & ", #" & Now() & "#, " & Me.SalaryChange & ");"
dbs.Execute(sql)

hth,
Wayne

 

[Sharon Hague]

Wayne

Thanks for your reply.

Quick question, my salary histoy is entered in a seperate table which has a one-to-many relationship with my main table. This is joined through EmployeeID. So in my main form I have Employee name etc in the header, then in the details part I have tab controls which has my salary history subform which lists various records when the salary changes for that employee. At present this subform is datasheet.

Do I enter one of your codes into the new text box I have created where I want it to show the Percentage increase/decrease?

 

[Sharon Hague]

Wayne

I have now tried both of your codes in myAfterupdate event of my Salary field.

When I enter new salary details into my subform in my tab control on my main form for that employee, after I have entered the new salary figure the message box appears stating this is a new employee etc. and does not enter any figure in my SalaryChange field.

This is the same with both codes.

Any ideas?