Appending 2 tables to another tables using db.execute

[mybers]

Hello everyone,

My database is about employee records. The process is I encode applicants records and then later I browse them and search which one will be hired from the records. Now the records will be transfered to the other table [tbl_emp].


I have 4 tables and 2 forms namely:

emp 'applicants record table
education
tbl_emp 'destination table
tbl_education

frm_name
frm_educ

on the form, When I press the the Hire button, the records now will transfered from tbl_applicant to tbl_emp along with the tbl_app_education to tbl_educ.

It's like this, but Im sureif this is the right way to do it...the Event of HIRED button

dim sql as sqlstring


db.execute

SQL INSERT INTO tbl_emp ( [Series-id], Employee_id, salutation, lname, fname, mname )
SELECT emp.[Series-id], emp.Employee_id, emp.salutation, emp.lname, emp.fname, emp.mname
FROM emp;

INSERT INTO tbl_education ( EO, [from], [end], school, degree, EducLevel, honors )
SELECT education.EO, education.from, education.end, education.school, education.degree, education.EducLevel, education.honors
FROM education;

Can someone redirect me on this since Im still a bit new to this..


Thanks

mybers

 

[KenReay]

Hi

Essentially the code you are proposing

db.execute "INSERT INTO tbl_emp ( [Series-id], Employee_id, salutation, lname, fname, mname )
SELECT emp.[Series-id], emp.Employee_id, emp.salutation, emp.lname, emp.fname, emp.mname
FROM emp;"

will work

you could consider a few alternative/different ways to do it

Use of docmd.runsql

use of transactions to ensure both 'sides' of transaction are executed or neither (ie Commit/rollback)

instead of have two tables, why not have a single table where each row contains an indicator to denote Employee/Applicant, then you do not need to move record between tables





Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mybers]

Thanks for an early reply!

In addition to the situation, this is a one-to-many relationship. where Employee & EO is the unique ID

Now the applicants table is like a temporary storage of applicants which eventually be hired and be put in the actualy employee records or be take out from applicant records.

Im having difficulties executing 2 - 8 SQLstrings triggered by 1 button (Hired cmd)

Moreover, I would also like when processed the transfered record/name will now be deleted from the applicants table along with the other tables aligned to it.

I hope Im being cleard on this...its hard to describe the situation over here..so please do ask details if you need to clarify...

Thanks in advance

 

[KenReay]

Hi

You can execute as many sql strings as you like

Db.Execute "INSERT ....etc"
Db.Execute "UPDATE ....etc"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK