Change table info

  • Thread starter Thread starter Naz
  • Start date Start date
N

Naz

Hi all

I have a employee database that i have created. Each employee record has a
reports field with the name of their manager.
But if a manager changes i have to manually go into the employee table and
do a find and replace, changing all the old managers name to the new manager
name.

So i would like to create a form with 2 drop down lists, both showing list
of all employees from the same table. From the first field i would select the
current (find) manager and the second one i would select the replacement
manager. Click on a button and Access would do a find and replace type
function.

Would i do this as a macro trying to do a find/replace in the table, or am i
suppose to use a different method. I am ok with forms, dropdown lists and can
do basic VBA, but if someone could point me in the right direction that would
be fab.

Regards

____________________________
Naz
London
 
Naz, why do all the records change when the manager changes?

Is it because a manager looks after a department, so when the manager
changes, all the employees in that department now have a new manager?

If that's the case, it might be best to store the DepartmentID with each
employee instead of the ManagerID. The department table would look like
this:
- DepartmentID primary key
- DepartentName Text
- ManagerID matches an EmployeeID from your Employee table.

The Employee table would have fields like this:
- EmployeeID primary key
- Surname Text
- FirstName Text
- DepartmentID matches a DepartmentID from your Department table.

Now you only have one change to make when the manager changes (in the
Department table.)
 
Naz,
Using my object names... you use your own...

Using names can cause problems, so do this by a unique key value.
Create an Update query, against the [MgrID] key field in your table.

Given frmMgrSwitch, with cboMgrIDOld and cbo MgrIDNew combos.

The criteria against MgrName in the Update query...
UpdateTo: Forms!frmMgrSwitch!cboMgrIDNew
Criteria: Forms!frmMgrSwitch!cboMgrIDOld

Be sure to Refresh the form before running the Update query.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Back
Top