Change table info

[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

 

[Allen Browne]

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.)

 

[Al Campagna]

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."