Delete specific record from multiple tables when click btn (1 Viewer)

Steven_Bash

Registered User.
Local time
Today, 09:18
Joined
May 9, 2018
Messages
27
Hello!
So I have this form. You can search records and this one btn is to delete it. the recordsource of where it searches records is "FULLOG". I have vba where once you click the delete btn. I have DoCmd.RunCommand acCmdDeleteRecord and it deletes it off the recordsource table(yay). But I want it to delete that same record from another table("Table2") that has that same record.
How can i do that? Ive tried CurrentDb.Execute "delete * from Table2 where VariableName =" & Me.VariableName but it hasn't worked. Thanks
 

isladogs

MVP / VIP
Local time
Today, 14:18
Joined
Jan 14, 2017
Messages
18,186
If the 2 tables are effectively meant to be 'paired', use referential integrity with cascade delete (and possibly cascade update)

Doing that means deleting a record in one table will automatically delete the record with the same PK value from the other table
 

Steven_Bash

Registered User.
Local time
Today, 09:18
Joined
May 9, 2018
Messages
27
The FULLOG table gets imported first and later appended to the Table2, so once i delete the record on FULLOG, it doesnt get deleted from Table2 bc its automatically appended when it gets imported...So no it doesnt delete the record on both tables...
 

Cronk

Registered User.
Local time
Tomorrow, 01:18
Joined
Jul 4, 2013
Messages
2,770
Firstly, I can't see why a cascading delete would not work.

Anyway,
Code:
CurrentDb.Execute "delete * from Table2 where VariableName =" & Me.VariableName
I presume 'VariableName' is actually the name of a field in Table2 and that the field is numeric. Have you checked that the code is being executed? Does your code have error trapping?
 

Steven_Bash

Registered User.
Local time
Today, 09:18
Joined
May 9, 2018
Messages
27
Firstly, I can't see why a cascading delete would not work.

Anyway,
Code:
CurrentDb.Execute "delete * from Table2 where VariableName =" & Me.VariableName
I presume 'VariableName' is actually the name of a field in Table2 and that the field is numeric. Have you checked that the code is being executed? Does your code have error trapping?

Well yes VariableName is a name field & that field is short text actually. & yes when i execute it i've recieved "Syntax error(missing operator)in query expression 'VariableName ='."
 

Cronk

Registered User.
Local time
Tomorrow, 01:18
Joined
Jul 4, 2013
Messages
2,770
Access is trying then to execute a query
Code:
delete * from Table2 where TextField =abc
It should be
Code:
delete * from Table2 where TextField ="abc"
or
Code:
delete * from Table2 where TextField ='abc'
So in your code you need to insert the quotes in, viz
Code:
currentdb.execute "delete * from Table2 where TextField ='" & me.txtField & "'"
or
Code:
currentdb.execute "delete * from Table2 where TextField =" & """" & me.txtField & """"
 

Users who are viewing this thread

Top Bottom