Move access record form one table to another

I work in a school and the school database as two tables - one for staff and one for contacts. When a member of staff leaves i want to move the staff details from the form into the contacts table

Please see VBA code enclosed - however I think that I am going about this the wrong way I suspect that I should be finding the record in the staff table and then copying the data across and deleting the staff table data

Can anyone help ?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
change the dots (.) with bang (!)


nreg.cindex  > change to nreg!cindex

do this for all of the fields in your code
It looks like Rey solved your problem so I'll just make a comment for future reference and to help others.

It may be a little late to implement this suggestion but in most applications it is better to create an entity table and all people/companies get added there along with their contact info, etc.  Then because different people can have different roles at different times, you can add a role table that tracks what role that entity can play.  So you might have Student, Teacher, Contact, Vendor, Administrator, Guardian, etc. as potential roles and it is conceivable that a single individual might participate in all roles at once.  Each role an entity plays, generates a row in the EntityRole table (this is a junction table because it joins entities with roles).  In queries where you are looking for a specific entity type, you would join first to the EntityRole table and through that to the entity table.

Keeping all entities in a single table means that you never have to move them and in the cases where a single person/company might need to be in more than one table, you don't have to worry about data anomalies caused by updating one table but not the others.  The single table solution also simplifies your queries since you only have a single table to join to (except in the search scenario above).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.