CODEWITHSUNDEEP
sqldatabasevbams-access
Isaac Reefman
Isaac Reefman

Reputation: 597

Copy records from one table to another with MS Access form control

I have an MS Access database with a form based on a query with simplified data taken from a couple of tables. The primary Key (ItemNumber) in both the relevant tables is present in the query that the form is based on.

I want to be able to use a button to move a record from the table Products to SuspendedProducts - so three part process:

  1. Select the record in Products with the same ItemNumber as the one currently selected in the form

  2. Copy this record to SuspendedProducts

  3. Delete it from Products

Just to get it to copy the record over I've tried a few things, none of which seem to work. In VBA I've written

"INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber =" Me.ItemNumber

but it's not copying anything into SuspendedProducts. Slight changes have had it do nothing, or had it complain about syntax errors.

Upvotes: 3

Views: 23501

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Since this has been resolved in the comments, I'll post my suggestion as an answer so that this question may be marked as resolved.

There were essentially three issues with your current code:

  • Either CurrentDb.Execute or DoCmd.RunSQL methods are required to execute the SQL expression you have constructed - the SQL expression is otherwise just a string.

  • You were missing the concatenation operator (&) when constructing your SQL string:

    Your code:

    "INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber =" Me.ItemNumber

    Should have read:

    "INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber =" & Me.ItemNumber

  • Since the inclusion of the concatenation operator did not prove successful, I suggested that your ItemNumber field may be of string datatype, and the criteria value would therefore need to be quoted within the SQL expression:

    "INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber ='" & Me.ItemNumber & "'"

    This could also be written:

    "INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber =""" & Me.ItemNumber & """"

    Since "" results in a literal " in VBA; however, this is slightly less readable.

Taking the above into consideration, the final code could be:

CurrentDb.Execute _
"INSERT INTO SuspendedProducts " & _
"SELECT * FROM Products WHERE ItemNumber ='" & Me.ItemNumber & "'"

Upvotes: 5

Isaac Reefman
Isaac Reefman

Reputation: 597

LeeMac commented that Me.ItemNumber may be String data type, and proposed this solution, which worked:

CurrentDb.Execute "INSERT INTO SuspendedProducts 
SELECT * FROM Products 
WHERE ItemNumber ='" & Me.ItemNumber & "'"

June7 Suggested that better design may be to add a field in the Products table designating whether an item is suspended or not, to avoid having to move records.

Upvotes: 2

Related Questions