CODEWITHSUNDEEP
sqlvbams-access
Ace16150s
Ace16150s

Reputation: 97

Inserting a record from one table into another

I have in Access VBA code as an on-click button on a userform.

I have two duplicate tables (PrintTable & ManPowerCalculator) and I am trying to insert every item from ManPowerCalculator table into the PrintTable where the EmplID input box on the userform = that within the ManPowerCalculator Table.

CurrentDb.Execute "INSERT INTO PrintTable VALUES (*) SELECT (*)FROM ManPowerCalculator WHERE EmplID # = " & Me.EmplID "

I am not defining any variables, maybe it would make it more efficient.

Upvotes: 2

Views: 8651

Answers (1)

finjo
finjo

Reputation: 376

This should do the job:

CurrentDb.Execute "INSERT INTO PrintTable SELECT * FROM ManPowerCalculator WHERE EmplID = " & Me.EmplID

Note that this will only work if the PrintTable and ManPowerCalculator tables have the exact same fields. If they don't, you will have to specify the field names both in the INSERT and SELECT parts of the query.

This is done in the following format:

CurrentDb.Execute "INSERT INTO PrintTable (x, y, z) SELECT x, y, z FROM ManPowerCalculator WHERE EmplID = " & Me.EmplID

With x, y and z being possible field names.

Upvotes: 2

Related Questions