Delete a record from a form and its subform

  • Thread starter Thread starter John B
  • Start date Start date
J

John B

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
 
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
 
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
 
strSQL = "Delete * From qryReidAmendedProductDrawings Where FormerDWGNo="
&
Me.FormerDWGNo
Click to expand...

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.
 
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
 
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.
 
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
 
Yes, after doing the delete you need to requery the combo boxes.

Me.cboMyCombo.Requery
 
Wayne,

I used your code example, but when it gets to the line

CurrentDb.Execute strSQL, dbFailOnError

I get an error that states "Run-tim error '3061':
Too few parameters. Expected1.

Do you know what this is?

Thanks!
 
strSQL = "Delete * From qryOrderDetailsSource Where PurchaseOrderID=23062"

20362 is the current purchase order that is being created that I want to
cancel. Any ideas?

Thanks!
 
It's unusual to be deleting from a query: why not delete from the table?

What happens when you try to run qryOrderDetailsSource: are you prompted for
a parameter?
 
I have also tried deleting straight from the table. I tried the query
because the other person that was using this had a query. When I tryo to
delete from the tabe, I have the same error message.

When I run the query, it brings up all Purchase Orders that have ever been
created and I don't get prompted for anything. Ideas?

Thanks!
 
Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.
 
PurchaseOrderID is a number field.

Douglas J Steele said:
Is PurchaseOrderId a text field?

If so, you'll need strSQL to contain

Delete * From qryOrderDetailsSource Where PurchaseOrderID='23062'

as Wayne indicated some time ago.
Click to expand...
 
The only thing I can thing of is that your query is too complex to allow it
to be used in a delete statement (although you say you can't delete directly
from the table either).

Sorry.
 
Thank you so much for looking into this and helping me out. I think I'll
just scrap the cancel button. It's not a huge deal, just a convience for the
user. The query I am using is the one Access automatically creates when you
base a form on a table. I just actually saved it to the query section in
Access for testing this out. I never created the query myself. That could
also be the problem. Thanks again!
 
Back
Top