i need help about moving records from a table to another one

[taco]

hi everybody...

i'm a new user in access. i'm trying to make an employee program. in this
database there is a table named 'employees". i've put one command button to
the form for unemploying. when i press this button, current record must be
transferred to another table which named "unemployed" with some supplementary
datas (date of unmployment, reason for leaving and re-employ? yes/no) is
there any way to do that? i'm waiting for your helps.

thanks in advance.

 

[Allen Browne]

The simplest and most flexible solution would be to put the extra fields
into the original Employee table, and add some kind of status field to
indicate whether the person is currently active or not.

That saves all the trouble, and avoids problems with related fields (e.g. if
you still need the records of the person's sales). It is dead easy to select
just the current employees (simple query), sort non-current employees to the
bottom of a report or drop-down list, compare this year's data with last
year's, and heaps more.

If you want to do it anyway, the "move" consists of a copy followed by a
delete. You probably want do do that in a transaction, so you get an
all-or-nothing result. Details and sample code in this article:
Archive: Move records to another table
at:
http://allenbrowne.com/ser-37.html

BTW, if you are coping records back and forth between tables in Access 2003,
there is a bug where the AutoNumber fields get messed up, even with the
latest service packs required. Details:
http://support.microsoft.com/?id=884185

 

[David C. Holley]

Performing actions like that is a big-no-no within Database design. The
only times when you store similar data between tables is when your
dealing with temp tables or archives. You are better off creating a new
field txtEmpolymentStatus and using that field to capture the employees
current employment status.

 

[taco]

thank you very much for your incredibly good answer.

but there is still problem to solve... your answer is too professionel for
me. i'm a beginner in access and vba codes are too heavy for me.
let me tell you more about situation.
table name is employees. in this table 20 detail information for each worker.
in same database, there is another table which named as unemployed. this
table has 3 more rows which employees table does not have. (date of
unemployment, reason for leaving and re-employ (yes or no))
in employees form, i've added one command button for unemploying.

what i want to do exatly, when i press that button, program should ask me
the date of unemployment, after i enter this data second question should be
the reason for leaving. after entering that data, next question is re-emloy
(yes or no). after i answer that last question should be "are you sure to
unemploy this worker?" if the answer is yes, the current employee record
should be copied with those supplementary three datas to the table
"unemployed" and be deleted from the table "employees".

i'm shamed to ask you more and that much. sorry. and if you can help me
about this i'll be so glad.
thank you very much for your time.

sincerely yours.

taco

 

[Allen Browne]

If the append/delete queries in transaction sound too complicated, here is a
simple solution that requires no code.

1. Open your Employee table in design view, and add these extra fields:
Inactive Yes/No
DateUnemployeed Date/Time
ReasonForLeaving Text
ReEmploy Yes/No
Save.

2. Expose these fields on your form, so they are easy to enter and update.
This will save you having to write any VBA code to open another unbound form
to collect these values, or use InputBox() or any other programmatic
approach.

3. Create a query into the Employee table. In the Criteria row beneath the
new Inactive field, enter:
False
Save the query.
Use this query as the RecordSource for any form or report that should show
active employees only.

 

[David C. Holley]

Basically, the design of the database does not follow generally accepted
guideliness. If you are not that familar with Access and Database
design, I would *FIRST* go to a bookstore and browse around for books on
Access. There is a plethoral of them out there. If you do not have a
firm grasp of the foundational issues, you will waste a good amount of
time here posting, waiting, reposting, waitiner, etc. Given the
terminology that you've used, if you do *not* grab some books and do
some homework, its going to be like pulling teeth for you and those of
us here to walk you through the challenges that you're having with your DB.