Open SubForm, Run CommandButton, All Records

R

rarmont

I'm at a loss at how to do this, so any help is appreciated.

The subform I use to update my employees' insurance deductions is great, but
I have to update their deductions individually (their deductions change after
1 year of service). This is very tedious as we have hundreds of employees
and selecting their name from a drop-down list on the main form, clicking on
the insurance deduction form button, then clicking on the calculate
deductions button, then closing the subform takes a lot of time.

Is there a way to create a command button on the main form that will loop
through the records and execute the command button that calculates the
deductions (that will notify me when it's complete)?
 
J

June7 via AccessMonster.com

Short answer - Yes, there is always a way to do something, probably more than
one. How many employees need to be updated at any one time?

1 - run an Update query in code that modifies the fields. Have the formulas
for the new values as part of the query or refer to custom functions.
2 - Loop structure that moves to each record on the form and examines to see
if needs update and if so execute the deduction button click event (control
events can be called just as any procedure). Might need to utilize DAO
RecordsetClone object.

Option 1 probably fastest to run and maybe less complicated to build.

As for notification of completion, a message box will serve. Be sure to
refresh the form after the procedure completes (Me.Refresh).
 
D

Damon Heron

I can't see the code that calculates the new deduction, but if it is simple,
it sounds like you need a query to update each employee's deduction rate
based on time of service. First, I would just do a select query to
determine its working properly to list only those employees who need the
deduction changed. If it is working okay, the change to an update query
(use a copy of your db to experiment so you don't screw up any real data).
If you want, then you can execute the finished update query from a command
button on a form.
Damon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top