Search code examples
sqlvbams-accessinsertsql-delete

DELETE and INSERT records from one ACCESS table to another table with VBA and SQL

Scenario: a database with 2 tables. Both have identical fields. They are not connected, nor is there a query, nor should there be one. Table1 is inserted to a form and is constantly updated, table2 none of them! Table1 has a checkbox in the record: if it is set in the form, the record should be copied to table2. If it is removed, the record in table2 should be deleted again. In this way, the selected record should remain in Table2 even after Table1 has been updated. I'm having trouble with the SQL code...it works halfway, but when removing a record I get all sorts of ERRORS, and the record is not removed!

the VBA code is attached as code (on click) in a form to the checkbox-element "chBOX"

Here is my failed code...does anyone have a solution?

If chBOX.Value = True Then
 CurrentDb.Execute _
 "INSERT INTO tblSaveSelection " & _
 "SELECT * FROM tblMain WHERE chBOX =TRUE"
  'maybe an idea here too? Do not insert all records but only this 
  'one with the activated chBOX?
ElseIf chBOX.Value = False Then
 CurrentDb.Execute _
 "DELETE FROM tblSaveSelection " & _
 "SELECT * FROM tblMain WHERE chBOX.value =false"
   '!!!!! Here ist the problem...it should be: delete the one record from tblSaveSelection 
   'where ID = tblMain.ID AND tblMain.chBOX = FALSE !!!!!!
End If
Solution

  • Options:

    1. "DELETE FROM tblSaveSelection WHERE ID = " & Me.ID

    2. "DELETE FROM tblSaveSelection WHERE ID IN (SELECT ID FROM tblMain WHERE chBox = False)"