Run a Command on all Records

[rarmont]

I'm running a HRIS on Access for the time being (cumbersome but necessary).
The database runs a sub-form opened from a drop-down list allowing the user
to select which employee record to update. Once in the sub-form, there is a
command button that runs on the selected record only. The button, once
clicked, updates insurance deductions based on years of service and coverage
amounts (EE only, EE & Spouse, EE & Children, Family, Waived). The deductions
are retrieved from a second table, with the parameters based off of less than
one year of service or more than one year of service and coverage selected.
The deductions change after one year of service (on the first of the
following month). Age comes in to play while calculating voluntary life
insurance, but in an effort to keep this simple, I'll try to briefly explain
what I need.

I'm trying to create a button that will run the calculations on ALL of our
employees at the same time. Is there a way to do this?? A loop starting at
the first record then ending with a pop-up telling me it worked??

 

[Jeff Boyce]

It sounds like you are approaching this as requiring a record-by-record
processing routine, looping through each, one-by-one.

Have you looked into the possibility of using a query to do the entire set
as a set, rather than as individual records?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jonathan]

I'm running a HRIS on Access for the time being (cumbersome but necessary).
The database runs a sub-form opened from a drop-down list allowing the user
to select which employee record to update. Once in the sub-form, there is a
command button that runs on the selected record only. The button, once
clicked, updates insurance deductions based on years of service and coverage
amounts (EE only, EE & Spouse, EE & Children, Family, Waived). The deductions
are retrieved from a second table, with the parameters based off of less than
one year of service or more than one year of service and coverage selected.
The deductions change after one year of service (on the first of the
following month). Age comes in to play while calculating voluntary life
insurance, but in an effort to keep this simple, I'll try to briefly explain
what I need.

I'm trying to create a button that will run the calculations on ALL of our
employees at the same time. Is there a way to do this?? A loop starting at
the first record then ending with a pop-up telling me it worked??

Hi Rarmont,

You could have a button that creates a recordset of employees and then move
through this recordset. Use a message box at the end.
Online help has a description and example of using recordsets. Alternatively
search this user group for posts about recordsets for examples.

Luck,
Jonathan

 

[Tom van Stiphout]

On Wed, 13 Jan 2010 16:25:01 -0800, rarmont

Operations on a set are one of the strong suits of a set-based
language like SQL:
update tblEmployees set myField = myValue
(replace myObjectNames with yours)
This affects all records in the set.
You can restrict the set by using a where-clause. For example:
update tblEmployees set myField = myValue
where DateFired is null

If your calculation is too complex for an expression, you can call a
Public function in a Standard module. For example:
update tblEmployees set myField = myPublicFunction(myField1, myField2)

public function myPublicFunction(x as Integer, y as integer) as double
myPublicFunction = sin(x) + tan(y)
end function

-Tom.
Microsoft Access MVP

 

[rarmont]

Tom:

This is close to what I'm talking about, but not quite there. Here's my
problem:

The form (named "Employee_Main") containing the command button that
calculates the deductions refers to a function that defines the current
record by the value in two drop-down lists using a SQL command from another
form named "Employee_Home". The drop-down lists run macros; the first list
contains our four locations and runs a query for all records for the location
selected, the second list contains the Employee IDs for the location
selected. The user would then select the employee they wish to edit from the
drop-down list and click on a command button to edit the record. Here's my
code:

In the Employee_Home form the following applies to open the form so the user
can edit the employee record (which contains the command button to update
their deductions):

Private Sub Update_Employee_Button_Click()
On Error GoTo Update_Employee_Button_Click_Err


'MsgBox "Selected value is " & testchooseemp

DoCmd.SetWarnings WarningsOff

strSQLArchive = "INSERT INTO [Employee_Archive]" & _
"SELECT [Employee_Main].* FROM [Employee_Main]" & _
"WHERE (([Employee_Main].[Emp_ID]) = Chooseemp);"

DoCmd.RunSQL (strSQLArchive)

DoCmd.OpenForm "Employee_Main", acNormal, , "([Employee_Main].[Emp_ID])
= Forms!Employee_Home!Chooseemp", acFormEdit

Forms![Employee_Main]![ChooseEmp] = Forms!Employee_Home!ChooseEmp


Update_Employee_Button_Click_Exit:
Exit Sub

Update_Employee_Button_Click_Err:
MsgBox Error$
Resume Update_Employee_Button_Click_Exit
End Sub

In the form Employee_Main, the user then clicks on the command button to
calculate deductions, which contains the following code:

Public Sub Calculate_Deductions_Click()
On Error GoTo Calculate_Deductions_Click_Err

Last_Upd_Dt = Date
'Me.SaveRecord
DoCmd.RunCommand (acCmdSaveRecord)
DoCmd.SetWarnings Off


strSQL_Last_Upd_Dt = "UPDATE Employee_Main " _
& "SET Employee_Main.Last_Upd_Dt = Date() " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.SetWarnings Off

strSQL_Birth_Month = "UPDATE Employee_Main " _
& "SET Employee_Main.Birth_Month = Month([Employee_Main.DOB]) " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.RunSQL (strSQL_Birth_Month)

strSQL_Birth_Day = "UPDATE Employee_Main " _
& "SET Employee_Main.Birth_Day = Day([Employee_Main.DOB]) " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.RunSQL (strSQL_Birth_Day)

strSQL_Birth_Year = "UPDATE Employee_Main " _
& "SET Employee_Main.Birth_Year = Year([Employee_Main.DOB]) " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.RunSQL (strSQL_Birth_Year)



If (Emp_ID = ChooseEmp) Then

If (Month(Now()) <= Month(DOB)) Then

strSQL_Age = "UPDATE Employee_Main " _
& "SET Employee_Main.Age = Year(Now()) - Year([Employee_Main.DOB]) - 1 " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.RunSQL (strSQL_Age)

Else

strSQL_Age = "UPDATE Employee_Main " _
& "SET Employee_Main.Age = Year(Now()) - Year([Employee_Main.DOB]) " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"


DoCmd.RunSQL (strSQL_Age)

End If
End If


If (Emp_ID = ChooseEmp) Then

If (Month(Now()) < Month(DOH)) Then

strSQL_YOS = "UPDATE Employee_Main " _
& "SET Employee_Main.YOS = Year(Now()) - Year([Employee_Main].[DOH]) - 1 " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_YOS)

Else

strSQL_YOS = "UPDATE Employee_Main " _
& "SET Employee_Main.YOS = Year(Now()) - Year([Employee_Main].[DOH])" _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_YOS)


End If
End If



strSQL_Med_RT = "UPDATE Employee_Main, Deduction_Rates " _
& "SET Employee_Main.Med_Deduction_Amt = [Deduction_Rates].[Rate]" _
& "WHERE ( ((Employee_Main.[Medical
Enrollment])=[Deduction_Rates].[Enrollment_Selection]) " _
& "AND ((Deduction_Rates.Type)= 'Medical') " _
& "AND ((Employee_Main.[YOS]) >= [Deduction_Rates].[Start_Factor]) " _
& "AND ((Employee_Main.[YOS]) <= [Deduction_Rates].[End_Factor])" _
& "AND (([Employee_Main].[Emp_ID]) = Chooseemp) );"


DoCmd.RunSQL (strSQL_Med_RT)

strSQL_Dental_RT = "UPDATE Employee_Main, Deduction_Rates " _
& "SET Employee_Main.Dental_Deduction_Amt = [Deduction_Rates].[Rate]" _
& "WHERE ( ((Employee_Main.[Dental
Enrollment])=[Deduction_Rates].[Enrollment_Selection]) " _
& "AND ((Deduction_Rates.Type)= 'Dental') " _
& "AND ((Employee_Main.[YOS]) >= [Deduction_Rates].[Start_Factor]) " _
& "AND ((Employee_Main.[YOS]) <= [Deduction_Rates].[End_Factor])" _
& "AND (([Employee_Main].[Emp_ID]) = Chooseemp) );"


DoCmd.RunSQL (strSQL_Dental_RT)

strSQL_Vol_RT = "UPDATE Employee_Main, Deduction_Rates " _
& "SET Employee_Main.Vol_Rt = [Deduction_Rates].[Rate]" _
& "WHERE ( (([Employee_Main].[Emp_ID]) = Chooseemp) " _
& "AND ((Deduction_Rates.Type)= 'Voluntary') " _
& "AND ((Employee_Main.[Age]) >= [Deduction_Rates].[Start_Factor]) " _
& "AND ((Employee_Main.[Age]) <= [Deduction_Rates].[End_Factor])" _
& "AND ((Employee_Main.[Voluntary])= 'Yes') );"

DoCmd.RunSQL (strSQL_Vol_RT)

strSQL_Child_RT = "UPDATE Employee_Main, Deduction_Rates " _
& "SET Employee_Main.Child_Rt = [Deduction_Rates].[Rate]" _
& "WHERE ( (([Employee_Main].[Emp_ID]) = Chooseemp) " _
& "AND ((Deduction_Rates.Type)= 'Child') );"

DoCmd.RunSQL (strSQL_Child_RT)

strSQL_Med_Dental_Deduction_Amt = "UPDATE Employee_Main " _
& "SET Employee_Main.Med_Dental_Deduction_Amt =
Employee_Main.Med_Deduction_Amt + Employee_Main.Dental_Deduction_Amt " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Med_Dental_Deduction_Amt)

strSQL_Med_Dental_Deduction_Amt = "UPDATE Employee_Main " _
& "SET Employee_Main.Med_Dental_Deduction_Amt =
Employee_Main.Med_Deduction_Amt + Employee_Main.Dental_Deduction_Amt " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Med_Dental_Deduction_Amt)

strSQL_EE_Cost = "UPDATE Employee_Main " _
& "SET Employee_Main.EE_Cost = Employee_Main.Vol_Rt *
Employee_Main.Vol_EE_Units " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_EE_Cost)

strSQL_Spouse_Cost = "UPDATE Employee_Main " _
& "SET Employee_Main.Spouse_Cost = Employee_Main.Vol_Rt *
Employee_Main.Vol_Spouse_Units " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Spouse_Cost)

strSQL_Child_Cost = "UPDATE Employee_Main " _
& "SET Employee_Main.Child_Cost = Employee_Main.Child_Rt *
Employee_Main.Vol_Child_Units " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Child_Cost)

strSQL_Vol_Ded_Amt = "UPDATE Employee_Main " _
& "SET Employee_Main.Voluntary_Deduction_Amt = Employee_Main.EE_Cost +
Employee_Main.Spouse_Cost + Employee_Main.Child_Cost " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Vol_Ded_Amt)

strSQL_Vol_Ded_Bi_Amt = "UPDATE Employee_Main " _
& "SET Employee_Main.Voluntary_Deduction_BiWeek_Amt =
((Employee_Main.EE_Cost + Employee_Main.Spouse_Cost +
Employee_Main.Child_Cost) *12 / 26) " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Vol_Ded_Bi_Amt)

strSQL_Tot_Ded = "UPDATE Employee_Main " _
& "SET Employee_Main.Total_Deductions =
Employee_Main.Med_Dental_Deduction_Amt +
Employee_Main.Voluntary_Deduction_BiWeek_Amt " _
& "WHERE (([Employee_Main].[Emp_ID]) = Chooseemp) ;"

DoCmd.RunSQL (strSQL_Tot_Ded)


Me.Refresh

Calculate_Deductions_Click_Exit:
Exit Sub

Calculate_Deductions_Click_Err:
MsgBox Error$
Resume Calculate_Deductions_Click_Exit

End Sub

The deduction rates are based off of age bands for voluntary life and years
of service (YOS) for medical and dental deductions. The values are stored in
a table called "Deduction_Rates" which contains the type of deduction
(Medical, Dental, Voluntary) along with Start and End factors (less than 1
YOS, less than or equal to a certain age, etc.) and finally the deduction
amount for each type and factor.

Given all of this information, how do I alter this code to loop through all
of my records and perform the functions listed with the click of one button?
I've considered creating a module which creates a recordsetclone, calls the
Calculate_Deductions function the Employee_Main form, and using a loop, but
would a Do Until .EOF Loop work? The function enters into the record defined
by the drop-down lists. Wouldn't I need to remove or change that?

Note to anyone who makes it through reading my code and answering my question:

I appreciate the time you took!!!

Thank you!

-Rachel

 

[rarmont]

I can't use an update query because I'm using calculated fields, which are
the fields I'm trying to update. These fields are performing the calculations
and appending the calculations to the records in my tables.

Unless you're suggesting I perform the calculations in an update query?

I've been searching for a solution to my problem for WEEKS!!!!

HELP!!!!!

 

[Jeff Boyce]

I may not be following your description ...

I have often created a query that includes calculations, then converted it
to an update query and used it to update existing data.

A couple potential wrinkles ...

You've describe "how", but not clarified "what". "How" to do something
depends on "what" ... understanding your current table structure is critical
to folks here offering relevant advice.

.... and "what", as in "what are you trying to accomplish"? Once you've
changed the values, what will you be able to do that you can't now? The
underlying business need is also fairly important, if only because sometimes
folks are trying to solve one problem with an approach that's better suited
to another problem!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.