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

Question

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.

Solution

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 & "'"

Comments

[Olivier Jacot-Descombes]

You are missing a & between the string and Me.ItemNumber for the string concatenation.

[Isaac Reefman]

Thanks for that, missed it. Still doesn't work though, gives me syntax error. I tried adding "CurrentDb.Execute" at the start, but it then tells me "data mismatch in criteria expression" - even though the two tables are identically formatted. (I even copied and pasted so that they would be identical)

[Lee Mac]

What is the data type of ItemNumber? If it's a string, then you'll need to use something like: CurrentDb.Execute "INSERT INTO SuspendedProducts SELECT * FROM Products WHERE ItemNumber ='" & Me.ItemNumber & "'"

[June7]

'Moving' records is usually poor design. Better just to set a field that flags the record as inactive and then apply filter criteria.

[Isaac Reefman]

@LeeMac, Thanks! That worked! Is text necessarily a string, or is it probably considered a string because of something that happens to it in the process of making the query?

[Isaac Reefman]

@June7, Thanks, you're probably right - not sure why I didn't think of that. I'll consider redesigning, though probably not super hard for the moment, now that I have it working!

[Isaac Reefman]

I couldn't select a comment as the "chosen" answer, so I hope this is an acceptable solution!

[Isaac Reefman]

This is a clear indication of what needed to be done to make this work/what mistakes I was making, and therefore the answer I selected. Also worth noting, though, that what I was trying to do was actually based on suboptimal design, and what I should have done (as @June7 suggested) was add a Boolean field indicating whether the item was suspended or not, and use that to filter in subsequent queries. This is what I ultimately did.