Deleting Records in subform

R

rico

I am trying to create a button on a form which deletes the current record but
ALSO the records linked to it the froms subform. If some one could suggest
some code to do this that would be great.

TIA

Rico
 
O

Ofer

Try this code

' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName =" & Me.[KeyFieldThatJoinBothTables]

' Delete current record on main form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
=============================================
If the key field is text field then change the delete sql to
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName ='" & Me.[KeyFieldThatJoinBothTables] & "'"
 
R

rico

Tankyou Ofer, worked perfectly!

Few related questions. D'you know if its possible to suppress the "are you
sure you want to delete records" messages.

Also if you repsond no to the yes/no prompts, i get a 'Menu item action
cancelled' message, even with error handling code.

Any ideas on how to suppress both of these?

TIA

Rico

Ofer said:
Try this code

' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName =" & Me.[KeyFieldThatJoinBothTables]

' Delete current record on main form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
=============================================
If the key field is text field then change the delete sql to
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName ='" & Me.[KeyFieldThatJoinBothTables] & "'"

--
I hope that helped
Good luck


rico said:
I am trying to create a button on a form which deletes the current record but
ALSO the records linked to it the froms subform. If some one could suggest
some code to do this that would be great.

TIA

Rico
Click to expand...
Click to expand...
 
O

Ofer

Use the setwarnings to false

Docmd.SetWarnings False
Run query here
Docmd.SetWanings True

' Don't forget to set it back to true
--
I hope that helped
Good luck


rico said:
Tankyou Ofer, worked perfectly!

Few related questions. D'you know if its possible to suppress the "are you
sure you want to delete records" messages.

Also if you repsond no to the yes/no prompts, i get a 'Menu item action
cancelled' message, even with error handling code.

Any ideas on how to suppress both of these?

TIA

Rico

Ofer said:
Try this code

' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName =" & Me.[KeyFieldThatJoinBothTables]

' Delete current record on main form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
=============================================
If the key field is text field then change the delete sql to
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName ='" & Me.[KeyFieldThatJoinBothTables] & "'"

--
I hope that helped
Good luck


rico said:
I am trying to create a button on a form which deletes the current record but
ALSO the records linked to it the froms subform. If some one could suggest
some code to do this that would be great.

TIA

Rico
Click to expand...
Click to expand...
Click to expand...
 
R

rico

thanks for all your help, all works fine.

Ofer said:
Use the setwarnings to false

Docmd.SetWarnings False
Run query here
Docmd.SetWanings True

' Don't forget to set it back to true
--
I hope that helped
Good luck


rico said:
Tankyou Ofer, worked perfectly!

Few related questions. D'you know if its possible to suppress the "are you
sure you want to delete records" messages.

Also if you repsond no to the yes/no prompts, i get a 'Menu item action
cancelled' message, even with error handling code.

Any ideas on how to suppress both of these?

TIA

Rico

Ofer said:
Try this code

' run a delete query to delete the related records from the subform
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName =" & Me.[KeyFieldThatJoinBothTables]

' Delete current record on main form
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
=============================================
If the key field is text field then change the delete sql to
DoCmd.RunSQL "DELETE TableName.* FROM TableName Where
TableName.FieldName ='" & Me.[KeyFieldThatJoinBothTables] & "'"

--
I hope that helped
Good luck


:

I am trying to create a button on a form which deletes the current record but
ALSO the records linked to it the froms subform. If some one could suggest
some code to do this that would be great.

TIA

Rico
Click to expand...
Click to expand...
Click to expand...
 
Top