Adding All Records for an Employee 1

[Happy2]

Hi everyone,

I need help please. I have a Salary table that stores each employee's weekly payment amount.
I need to always show the total of all payment amounts of each employee on the form
when I search or add a new record for that employee.

For example of a current record on the form:

Employee PayDate Amount YTD
-------- ------- ------- -------
Empl.# 1 1/1/02 $2,000 10,000

Please help me with the solution. Thanks a lot for your help.

 

[Steve101]

Happy,

(a) I'll assume that the Weekly salary table is called tblWeeklySalary, and that this table has (amongst others), the following fields: EmployeeNo (numeric) and WeeklySalary (numeric or currency).

(b) Further assume that the form is called frmEmployee, and includes an EmployeeNo control on it (bound to a table called tblEmployee), and an unbound control named YTD (with a corresponding label of course).

(c) Given the above, all you need to do is set the ControlSource property of the above YTD control to the following:

=DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo])

(d) What this expression will do is sum up the WeeklySalary field for the form's currently selected employee from the tblWeeklySalary table.

Hope that this is clear to you,

Cheers,
Steve

 

[Happy2]

Hi Steve,

Thank you so much for your help with the code. It works great for my searching purpose. I also need the field to work when I click on the add new button. However, the field shows "#Error" when I click on the add new button. I have been working on it but haven't got a solution yet. Can you please help me how to avoid the error problem, too?.

Thank you for your help in advance.

 

[suave2damax]

Hi,

Placing the DSum function given to you inside the nz() function:
=nz(DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]),0)

That should do it.

 

[suave2damax]

Hi,

Place the DSum function given to you inside the nz() function:
=nz(DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]),0)

That should do it.

 

[Happy2]

Hi suave2damax,

Thank you for your prompt response. I have tried your code but somehow it still shows the #Error. I still need your help. Thanks a lot

 

[Steve101]

Happy,

Try this:

=IIf(IsNull([Forms]![frmEmployee]![EmployeeNo]),0,DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]))

This rather ugly expression tests the value of the EmployeeNo control before trying to evaluate the DSum part, and returns zero if the EmployeeId is Null; the value it will be for a new employee. It thus prevents the #Error problem caused by the DSUM function trying to work with a Null criteria.

The solution is adequate, but would be more elegantly and clearly implemented as a user defined function (if you can program).

Hope this helps,
Cheers,
Steve

 

[MapMan]

How about using...

IIf(DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]) = 0,0,DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]))

HTH,
Mapman Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[Steve101]

Mapman,

I Dont think your method will work. The trouble is that the DSUM expression is not evaluating to zero, its evaluating to #Error because of the Null EmployeeNo value. Thats why the test is on EmployeeNo.

Regarding your approach, I did actually try something like

IIf(IsError(DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo])),0,DSum("WeeklySalary","tblWeeklySalary","EmployeeNo=" & [Forms]![frmEmployee]![EmployeeNo]))

But could'nt get that to work. This should actually test for the error condition and if detected, set the value to zero. Could'nt get it too work; hense the other approach.

Cheers,
Steve

 

[MapMan]

Hi Steve,

You're right, I didn't "actually" try it out. I stepped through it in my mind an it looked right. I guess I found a land mine in my mind.

MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.