Deleting Records in subform

[Guest]

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

 

[Guest]

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] & "'"

 

[Guest]

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

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

 

[Guest]

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

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

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

 

[Guest]

thanks for all your help, all works fine.

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

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

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