copy existing record to new record

[Muaz]

I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.

For example

existing record
StaffNo, Site, Name, hiring date, etc
AKA-111, 10, John, 25-10-13

new record
StaffNo, Site, Name, hiring date, etc
ALM-123, 14, John, 25-10-13

Any suggestion Please

Muaz

 

[pr2-eugin]

By the looks of it you want only one information only name and hiring date are the same, the rest varies. Which is not what your description says. How about setting Global variables for a particular session?

Or if that is wrong, explain a bit more what you want to do.

 

[Muaz]

Thanx Paul;

Actually the employee is transfer from one site to another. Each site has its own staffNo like AKA-111 employee is at site 10 while the same employee transfer to site 14 with new staff No ALM-123, when employee transfer. The employee remaining data needs to be updated against staff No ALM-123.

Instead of inserting the Add Record, I want to copy all the record and paste with new StaffNo.

Regards
Muaz

 

[JHB]

Instead of inserting the Add Record, I want to copy all the record and paste with new StaffNo.

For the same employer?
Is all record from one and same table?
If both answer yes, then I would use an append query.

 

[Muaz]

Yes for the same employee but the employee record is divided in different tables. For example Employee Personal Information is in one table, Company data is in another table, Salary Information in another table etc

What would be your suggestion in case of different tables like scenario mentioned above.

Regards
Muaz

 

[JHB]

Create a form and a button in the form, place text boxes for each data which would change between the sites.
Create one query for each table data should be append to and run the queries in the on click event for the button.
Referring in the query to the form's different text boxes, where data differs.
Remember to start inserting data in the right table order, (main keys/tables first).
Something like below:

INSERT INTO TableNameWhereDataAreInsert ( [StaffNo], ... )
SELECT [Forms]![FormName]![TextBoxNameForStaffNo] AS FormValueStaffNo, .... 
FROM TableNameWhereDataComeFrom;

 

[Muaz]

I am trying since but now I am getting this error:

"Too few parameters. Expected 2"

 

[JHB]

Then show you query-string.
Or post your database with some sample data, (zip it) + information about which form/query you have the problem.

 

[Muaz]

File attached.... For example, StaffNo AKA-111 needs to be transfer to Site 12 with new number HO-3

View attachment Database1.zip

 

[JHB]

I see you set it up in a module, (not exactly how I suggested, but never mind about that).
I have also made a solution involving a query in the attached database.

 

[Muaz]

Unable to open the database. It gives error

Unrecognised database format .....Database15.accdb

 

[JHB]

Unable to open the database. It gives error

Unrecognised database format .....Database15.accdb

Ok - (but not be me, I downloaded it and opened it without problem) I've made a MS-Access 2000 format instead, try it else I'll post the change in code.

 

[Muaz]

Thank you very much JHB for your time and support.

Best Regards
Muaz

 

[JHB]

You're welcome - luck with your project.