Automatically updating forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to calculate accruals based on a date of hire. I've set up a form to
do this and it works great, but I don't want the H.R. director to have to
open a form for each employee to update it. The update uses an if-then query.
Maybe there was a better way to do this than through a form and I'm open to
that as well.
 
Hi, okschlaps.

It's sound like the form performs a calculation and then you've added code
to *store* this result in a field in the underlying RecordSource. You don't
need nor should do this. In virtually all cases, you can simply calculate it
on the fly.

Can't you simply execute the query, or base a report on the query at any
arbitrary time, to calculate the accruals for each employee?

If this doesn't make sense to your situation, post the nature of the
accrual, and someone should be able to help.

Sprinks
 
I calculate years and months of service in a query using sql, but the
accruals are determined by the VBA if-then statement, if yrs of service
between 1 and 2 vacation = n, etc. My first inclination was to do this in a
query, could you point me in the right direction as to how to do that? Thanks.
 
Hi.

I think the cleanest way is to write a Public Function that you can call in
a calculated field in your query, passing the start date as a parameter.
Something like:

Public Function FxnAccrual(dteStartDate As Date) As Integer
Dim intYearsofService as Integer

' Calculate Years of Service Here
' Choose proper accrual
Select intYearsofService
Case 1:
FxnAccrual = x
Case 2:
FxnAccrual = y
etc.
End Select

End Function

Then call the function in your query. In SQL,

.....fieldlist, FxnAccrual([StartDate]) AS Accrual ...

Hope that helps.
Sprinks
 
Back
Top