Delete a record from a form and its subform

Hello to everybody.

How may I cancel a record with a button and all the relative informations in
the subform.

Many thanks
Regards
John

You need to delete the information in the subform first. You should have
Referential Integrity enforced on the link between the tables for the main
and sub forms, so it won't let you delete the record from the main form as
long as there are associated records in the subform (unless you also set the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the main
form
'and ID is the field in the subform's table that matches the Child Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative informations
in
the subform.

Many thanks
Regards
John

Thanks for your help.
Regards
John

I presume that you don't want to use referention integrity and then the
cascade deletion option?
Well I (almost) never prefer it...

You can trigger the "On Delete" event from the mainform. There you put the
code to delete the records from the subform.
e.g.

s = "Delete * from subtable where field=" & me.id
currentdb.execute s

--

Kind Regards
Damiaan
info @t dampee d0t be


"John B" wrote in message
...
Thanks for your help.
Regards
John

Hi Wayne,
I took your example for my application because I happen to have the same
need as John. But I guess I have made an error somewhere as an error message
says "Data type mismatch in criteria expression". Could you please kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from subform
' where the Me.FormerDWGNo is a textbox that has the Master Link field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo=" &
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line!

' Now that the relative record in subform is deleted, we now delete the record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should have
Referential Integrity enforced on the link between the tables for the main
and sub forms, so it won't let you delete the record from the main form as
long as there are associated records in the subform (unless you also set the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the main
form
'and ID is the field in the subform's table that matches the Child Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative informations
in
the subform.

Many thanks
Regards
John

strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
&
Me.FormerDWGNo

What data type is the field FormerDWGNo? The way you have this written, the
field should be a number data type. If the field is a string, the syntax
would be:

strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" &
Me.FormerDWGNo & "'"

If there is a possibility of apostrophes in the data (i.e. O'Hare), then
this will need to be modified more. Also, if the value is actually a date or
some other data type, modification may be needed.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Hi Wayne,
I took your example for my application because I happen to have the same
need as John. But I guess I have made an error somewhere as an error
message
says "Data type mismatch in criteria expression". Could you please kindly
tell why such error is occuring in my case? Many thanks!

Private Sub cbDelete_Click()
' Delete relative record in qryReidAmendedProductDrawings from subform
' where the Me.FormerDWGNo is a textbox that has the Master Link field
' in the main form
' and (another) FormerDWGNo is the field in the subform's query that
' matches the Child Link field.
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
&
Me.FormerDWGNo
CurrentDb.Execute strSQL, dbFailOnError '-- this's the problem line!

' Now that the relative record in subform is deleted, we now delete the
record
' in the query from main form
RunCommand acCmdDeleteRecord
End Sub
--
Regards,
Sam


"Wayne Morgan" wrote:

You need to delete the information in the subform first. You should have
Referential Integrity enforced on the link between the tables for the
main
and sub forms, so it won't let you delete the record from the main form
as
long as there are associated records in the subform (unless you also set
the
link for Cascade Deletes).

To do the delete using a button, run a delete query on the subform's
table
then delete the record from the main form.

Example:
strSQL = "Delete * From SubformTable Where ID=" & Me.txtID
'The Me.txtID above is a textbox that has the Master Link field in the
main
form
'and ID is the field in the subform's table that matches the Child Link
field.
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete * From MainformTable Where ID=" & Me.txtId
CurrentDb.Execute strSQL, dbFailOnError
'This is similar to the previous one, only now we are deleting from
'the table for the main form. In this case, instead of the query, you
'could also have used
'RunCommand acCmdDeleteRecord

--
Wayne Morgan
MS Access MVP


"John B" wrote in message
...
Hello to everybody.

How may I cancel a record with a button and all the relative
informations
in
the subform.

Many thanks
Regards
John

Thanks Wayne, your were right.
FormerDWGNo field is text (with no apostrophes), so I've now changed the
syntax as you pointed out. But now the next line "RunCommand
acCmdDeleteRecord" attracts another error which says "The command or action
'DeleteRecord' isn't available now". What does that mean?

Private Sub cbDelete_Click()
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" &
Me.FormerDWGNo & "'"
CurrentDb.Execute strSQL, dbFailOnError

' Now that the relative record in subform is deleted, we now delete the record
' in the query from main form
RunCommand acCmdDeleteRecord '-- this line now attracts another error!
End Sub

--
Regards,
Sam

Using a command such as the RunCommand that you have will perform the action
on the item that has the focus. It may be that the form you're trying to
delete a record from isn't the one with the focus. Depending on what record
your delete query just deleted, it is also possible that the RunCommand is
trying to delete the same record you just deleted, which can't be done.

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Thanks Wayne, your were right.
FormerDWGNo field is text (with no apostrophes), so I've now changed the
syntax as you pointed out. But now the next line "RunCommand
acCmdDeleteRecord" attracts another error which says "The command or
action
'DeleteRecord' isn't available now". What does that mean?

Private Sub cbDelete_Click()
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='"
&
Me.FormerDWGNo & "'"
CurrentDb.Execute strSQL, dbFailOnError

' Now that the relative record in subform is deleted, we now delete the
record
' in the query from main form
RunCommand acCmdDeleteRecord '-- this line now attracts another error!
End Sub

Thanks Wayne,
Instead of using the acCmdDeleteRecord, I tried using the alternative line
you suggested as below and it works. However, the combo boxes that I use to
filter records still contains the deleted record in its list. Can I also
update the combo box lists after the record is deleted? The RowSource of some
combo boxes is the same as the main form's RecordSource, but some ain't.

Regards,
Sam

Private Sub cbDelete_Click()
' Delete record from the query for the subform
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'"
CurrentDb.Execute strSQL, dbFailOnError

'This is similar to the previous one, only now we are deleting from
'the query for the main form.
strSQL = "Delete * From qryReidProductDrawings Where FormerDWGNo='" & Me.FormerDWGNo & "'"
CurrentDb.Execute strSQL, dbFailOnError
End Sub


"Wayne Morgan" wrote:

Using a command such as the RunCommand that you have will perform the action
on the item that has the focus. It may be that the form you're trying to
delete a record from isn't the one with the focus. Depending on what record
your delete query just deleted, it is also possible that the RunCommand is
trying to delete the same record you just deleted, which can't be done.

--
Wayne Morgan
MS Access MVP

Yes, after doing the delete you need to requery the combo boxes.

Me.cboMyCombo.Requery

--
Wayne Morgan
MS Access MVP


"Sam Kuo" .(donotspam) wrote in message
...
Thanks Wayne,
Instead of using the acCmdDeleteRecord, I tried using the alternative line
you suggested as below and it works. However, the combo boxes that I use
to
filter records still contains the deleted record in its list. Can I also
update the combo box lists after the record is deleted? The RowSource of
some
combo boxes is the same as the main form's RecordSource, but some ain't.