Run-time error 3075 - Syntax error

[claskew]

I am receiving an error message about a (missing operator) in query expression 'PK_Audience =':


Private Sub DeletePublicationRecord()
delPK_PubData = ComboPubName

[highlight]CurrentDb.Execute "DELETE FROM Tbl_Audience WHERE
PK_Audience = " & DLookup("FK_Audience", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";" [/highlight]
CurrentDb.Execute "DELETE FROM Tbl_Distribution WHERE PK_Distribution = " & DLookup("FK_Distribution", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_Index WHERE PK_Index = " & DLookup("FK_Index", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_Lifecycle WHERE PK_Lifecycle = " & DLookup("FK_Lifecycle", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_Print WHERE PK_Print = " & DLookup("FK_Print", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_ProdLoc WHERE PK_ProdLoc = " & DLookup("FK_ProdLoc", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_PubData WHERE PK_PubData = " & delPK_PubData & ";"
CurrentDb.Execute "DELETE FROM Tbl_PubName WHERE FK_PubData = " & delPK_PubData & ";"
CurrentDb.Execute "DELETE FROM Tbl_PubWeb WHERE PK_PubWeb = " & DLookup("FK_PubWeb", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
CurrentDb.Execute "DELETE FROM Tbl_SourceType WHERE PK_SourceType = " & DLookup("FK_SourceType", "Tbl_PubData", "PK_PubData = " & delPK_PubData) & ";"
MsgBox "Publication deleted.", vbOKOnly + vbInformation, "Delete Publication"
End Sub

I am trying to delete a publication and able to get to the final step of deleting the pub from the database and then I get the error message. When I hit debug it goes to the highlighted code.

Any help would be appreciated. Thanks!!!!!

 

[kermit01de]

Try to move the bracket to the end....

DLookup("FK_Audience", "Tbl_PubData", "PK_PubData = " & delPK_PubData & ";")

Kind regards
Mirko
--------------------------------------
>>>>>> ... I am sure, some supportissues are a matter of PEBKAC ... <<<<<

 

[claskew]

I tried that and it still is highlighted. Do you have any other suggestions?

 

[PHV]

What about this ?
CurrentDb.Execute "DELETE A.* FROM Tbl_Audience A INNER JOIN Tbl_PubData P ON A.PK_Audience=P.FK_Audience WHERE P.PK_PubData=" & delPK_PubData & ";"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[claskew]

It worked for that particular line, but now the Distribution line is highlighted.

 

[PHV]

So, why not changing all the other lines in the same way ????

 

[claskew]

I tried changing the other line and it stays highlighted.

 

[PWise]

throw a nz around each dlookup
nz(dlookup(field,Domain,Where),0)