SQL-DELETE record from multiple tables referencing ctrl box

[siouxsiez]

Hi and advanced thanks...

I am trying to set up a command button to delete a single record at a time from 3 tables. The Unique ID (below) is not the primary key but is populated across the tables when a new record is given a Unique ID. cbodelete refers to a combo box where a user can select a record (using the Unique ID) to delete. My code right now is:


Dim dbs As Database
Set dbs = CurrentDatabase

dbs.Execute "DELETE * FROM Tbl1Descriptive WHERE [Unique ID]=[Forms]![Delete Study]![cbodelete]"
dbs.Execute "DELETE * FROM Tbl2tx details WHERE [Unique ID]=[Forms]![Delete Study]![cbodelete]"
dbs.Execute "DELETE * FROM Tbl3endpoints WHERE [Unique ID]=[Forms]![Delete Study]![cbodelete]"


This isn't working at all and gives an "oject required" error. When I tried WHERE Unique ID = cbodelete, I got a parameter error. Can anyone help steer me the right way?

Cluelessly,
Kate

 

[hmckillop]

Its a quotes problem.

It should be

 Dim dbs As Database
    Set dbs = CurrentDatabase

    dbs.Execute "DELETE * FROM Tbl1Descriptive WHERE [Unique ID]=" + [Forms]![Delete Study]![cbodelete]
    dbs.Execute "DELETE * FROM Tbl2tx details WHERE [Unique ID]=" + [Forms]![Delete Study]![cbodelete]
    dbs.Execute "DELETE * FROM Tbl3endpoints WHERE [Unique ID]=" + [Forms]![Delete Study]![cbodelete]

Also this is a SQL forum not, MS Access

"I'm living so far beyond my income that we may almost be said to be living apart