calculation in form and subform AfterUpdate

  • Thread starter Thread starter Frankie
  • Start date Start date
F

Frankie

I am trying to perform calculations in an imbedded sub form using a field in
the main form.
Both forms are built on queries.
Main form=frmEmployees
Built on query=qryEmployees
Built on table=tblEmployees (key) Field lngempEmployeeNo
Imbedded sub form=frmEmpHours
Built on sub query=qryEmpHours
Built on sub table=tblEmpHours (key) Field lngempEmployeeNo (key) Field
AutoNumber
I am in the sub forms lngEmphHoursRg field and (private Sub
lngEmphHoursRg_AfterUpdate)
I am simply trying to do the following:
(In the sub form frmEmpHours.lngEmphHoursRg field ) Sub form
frmEmpHours.lngEmphTest =
main form frmEmployees.lngEmpHoursRgPay * sub form frmEmpHours.lngEmphHoursRg
I would like to use VBA not a query.
I know totals should not be in tables, but this is the beginning of
deducting payroll taxes and when it comes to the IRS I would like it written
in stone so to speak.
If anyone can help in layman terms it would be greatly Appreciated
Thank You
Frustrated
 
Sorry I am a little confused by this

Can you put it in a simpler way

ie. Are you trying to subtract the contents of a subform control from that
of a control in your main form and then store this calculated result
(time/date) somewhere into a table ?
 
Frankie,

Instead of trying to look up the value from the sub form just calculate it
in the main form.
(this is the beauty of data, it's the same anywhere you find it in the
database)
so...
you want employee hours multiplied by employee pay rate
in your after update sub
me.lngEmpTest =
CCur(DLookup("[qryEmpHours]","[lngEmpHoursRg]","[lngempEmployeeNo] = " &
me.lngempemployeeno)) * CCur(DLookup("[table pay resides in]","[pay
field]","[lngempEmployeeNo] = " & me.lngempemployeeno))

look at this and make it match your fields.

What's happening?
1) use Dlookup to find the values in each table respectively:
DLookup function has three parameters
a. table/query to look in
b. field to return value
c. filter criteria
2) CCur function returns that value as currency instead of a string (which
is what Dlookup returns)

you retreave the same data your sub form is reflecting.
 
Back
Top