Delete data in 2 tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I have 2 tables linked 1-to-many. The link field is called [AVP_No].
My MainTable.[AVP_No] cannot have duplicates, but SubTable.[AVP_No] does
have duplicates. I use a form+subform to enter data.

Now I want to create a button which will delete data from both tables where
the [AVP_No] is the same.
How can I do that? Can somebody advise?

Thank you.
Lana
 
Before trying that, please back up your data
============================================
'To Delete the record in the main form, you can use
DoCmd.RunCommand acCmdDeleteRecord

'And run a delete Sql to delete the records in the table that the SubFrom
is bounded to
Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " & [AVP_No]
 
One more thing
The sql delete should come first

Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " &
Me.[AVP_No]
DoCmd.RunCommand acCmdDeleteRecord
 
Doesnt work :(

I put this code into my button:
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = " &
Me.[PREQ_No1]
DoCmd.RunCommand acCmdDeleteRecord

When i press it the window asks for some "PREQ" to be entered and then the
entry in the main table is deleted, but all entries in sub-table still remain.

What is wrong here?

Lana


Ofer said:
One more thing
The sql delete should come first

Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " &
Me.[AVP_No]
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


Lana said:
Hi all,

I have 2 tables linked 1-to-many. The link field is called [AVP_No].
My MainTable.[AVP_No] cannot have duplicates, but SubTable.[AVP_No] does
have duplicates. I use a form+subform to enter data.

Now I want to create a button which will delete data from both tables where
the [AVP_No] is the same.
How can I do that? Can somebody advise?

Thank you.
Lana
Click to expand...
Click to expand...
 
Hi Lana,

try this (in order):
CurrentDb.Execute "DELETE * FROM T_PREQ WHERE T_PREQ.PREQ_No = " &
Me.[PREQ_No1], DbFailOnError

DoCmd.RunCommand acCmdDeleteRecord

HTH,
Brian


Lana said:
Doesnt work :(

I put this code into my button:
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = " &
Me.[PREQ_No1]
DoCmd.RunCommand acCmdDeleteRecord

When i press it the window asks for some "PREQ" to be entered and then the
entry in the main table is deleted, but all entries in sub-table still remain.

What is wrong here?

Lana


Ofer said:
One more thing
The sql delete should come first

Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " &
Me.[AVP_No]
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


Lana said:
Hi all,

I have 2 tables linked 1-to-many. The link field is called [AVP_No].
My MainTable.[AVP_No] cannot have duplicates, but SubTable.[AVP_No] does
have duplicates. I use a form+subform to enter data.

Now I want to create a button which will delete data from both tables where
the [AVP_No] is the same.
How can I do that? Can somebody advise?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Is the T_PREQ it's the name of the table that the sub form is bounded to?

Is the PREQ_No1 is string type, or number?

If it string use
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = '" &
Me.[PREQ_No1] & "'"
Doevents
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


Lana said:
Doesnt work :(

I put this code into my button:
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = " &
Me.[PREQ_No1]
DoCmd.RunCommand acCmdDeleteRecord

When i press it the window asks for some "PREQ" to be entered and then the
entry in the main table is deleted, but all entries in sub-table still remain.

What is wrong here?

Lana


Ofer said:
One more thing
The sql delete should come first

Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " &
Me.[AVP_No]
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


Lana said:
Hi all,

I have 2 tables linked 1-to-many. The link field is called [AVP_No].
My MainTable.[AVP_No] cannot have duplicates, but SubTable.[AVP_No] does
have duplicates. I use a form+subform to enter data.

Now I want to create a button which will delete data from both tables where
the [AVP_No] is the same.
How can I do that? Can somebody advise?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Thank you Ofer, it worked.
(it was string type)
Lana


Ofer said:
Is the T_PREQ it's the name of the table that the sub form is bounded to?

Is the PREQ_No1 is string type, or number?

If it string use
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = '" &
Me.[PREQ_No1] & "'"
Doevents
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


Lana said:
Doesnt work :(

I put this code into my button:
DoCmd.RunSQL "DELETE T_PREQ.* FROM T_PREQ WHERE T_PREQ.PREQ_No = " &
Me.[PREQ_No1]
DoCmd.RunCommand acCmdDeleteRecord

When i press it the window asks for some "PREQ" to be entered and then the
entry in the main table is deleted, but all entries in sub-table still remain.

What is wrong here?

Lana


Ofer said:
One more thing
The sql delete should come first

Docmd.RunSql "DELETE TableName.* FROM TableName WHERE FieldName = " &
Me.[AVP_No]
DoCmd.RunCommand acCmdDeleteRecord

--
\\// Live Long and Prosper \\//


:

Hi all,

I have 2 tables linked 1-to-many. The link field is called [AVP_No].
My MainTable.[AVP_No] cannot have duplicates, but SubTable.[AVP_No] does
have duplicates. I use a form+subform to enter data.

Now I want to create a button which will delete data from both tables where
the [AVP_No] is the same.
How can I do that? Can somebody advise?

Thank you.
Lana
Click to expand...
Click to expand...
Click to expand...
 
Back
Top