Want to delete from Access Table when match found on 2 Tables

I have a problem with the following code (I am using VBA in Outlook 2003), where I want to delete a record from an Access 2003 Table (Teams), but only if a variable is also matched to another Table (Leagues).

[b][color=Black]With KA_Com
.ActiveConnection = KA_DB
.CommandText = "DELETE * FROM Teams WHERE Teams.[Team_Name] = '" _
& KA_Team_Delete_ComboBox1.Text & "' " _
[color=Red] & "AND Leagues.[League_Name] = '" _
& Working_League & "'" _ [/color]
& ";"
.CommandType = adCmdText
End With[/color][/b]

The code works fine without the lines in Red, but it deletes all occurrences of the variable, i.e. if a Team_Name occurs in more than one League, they are all deleted ... I need the deletion to be League specific, yet when I add the lines in Red, to check the Leagues Table, I get an error.

I have tried this with and without brackets, but to no avail ... I am sure this must be something really simple, but as usual, can't see it myself !!!
Addendum :

When this code executes, I get the messages :

Run-time error


Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!


In this Discussion