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

 

[jadams0173]

Try and change this:

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

to this:

dbs.Execute "DELETE * FROM Tbl1Descriptive WHERE [Unique ID]=[b]&[/b] [Forms]![Delete Study]![cbodelete]"

Assuming that cbodelete is a number. if it's text then

[Unique ID]='" [b]&[/b] [Forms]![Delete Study]![cbodelete] & "'"

 

[siouxsiez]

Thanks j! Unfortunately, I get the same object required error.

cbodelete uses a 3-column query as its source, of which the Unique ID is only 1... might that be a problem?

Sorry, I'm such a novice with VBA.


Kate