Delete Record and Subform Records

[vb9063]

Hi Guys,
I have searched the forum for the answer to this question but have not found the solution yet.
I would like to get rid of as many macros in my database as possible and rely on the VBA. I have a button to delete a record on my mainform, which I have put the following code:

Private Sub cmdDeleteSupplier_Click()
    If (MsgBox("Deleting a supplier record will permanently delete it.  Are you sure you want to delete?", vbYesNo, "Delete Confirmation")) = vbYes Then
        If Me.Dirty Then
            Me.Undo
            DoCmd.SetWarnings False
        If Not Me.NewRecord Then
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
        End If
        End If
     
        MsgBox "The supplier was successfully deleted.", , "Delete Confirmation"
        
    End If
End Sub

I have tried this code with a normal form and it does seem to work but my form has 3 subforms in it on tabbed pages.
My question is how do I get the code to work and delete the record including the other information referenced in the subforms? How do I select this info in code??
Thanks v much!
VB

 

[JamesMcS]

Are the subforms based on tables related to the main form? If they are, does enforcing referential integrity and cascade deleting records work?

If they're all based on the same table a requery in the afterupdate of your delete button might work.

 

[vb9063]

Hi James,
Thanks my cascading is all ticked but it doesnt work, the delete button doesnt seem to have an afterupdate event..?

 

[JamesMcS]

Sorry, I meant your command button on_click

 

[JamesMcS]

So when you delete a record in the main table, the related tables don't delete accordingly? That's strange... Try it in the tables themselves rather than through the form, see if that works

 

[vb9063]

Yep deleting them in the main table works..but nothing is deleted using the form button....not even the main record...

 

[JamesMcS]

Hmmm instead of acdeleterecord, try using an SQL string:

docmd.runsql "DELETE * from [tablename] WHERE [supplier]=" & me.supplierno

Or something

 

[vbaInet]

Depending on what you're doing, Me.Refresh (the subforms in concern) if you're deleting from the current recordset or Me.Requery to requery the recordset.

 

[JamesMcS]

Hi VBA! It seems the cascade delete isn't working in the form scenario. I'm not sure about the runcommand method so let's see what happens with the delete query... do you know of a difference?

 

[vbaInet]

If it's deleted from the main record, the relationships have been set-up correctly and the cascade delete is enforced then it would work. I think it's just a visual thing on the subform, the record is being deleted but it "appears" to still be there so a quick refresh will sort it out.

When you delete, have a look in the table to see if the record is still there. If it is then delete each associated record using SQL, if it isn't then Refresh/Requery each subform.

 

[vb9063]

Do I need to create a delete query? Am majorly confused now, can u not delete a record using VBA only??
Thanks again, VB

 

[vbaInet]

You can delete a record using vba.

First of all, does the main record get deleted?

 

[JamesMcS]

Well you can, but I was just trying to see if using a query rather than runcommand made any difference (I can't see how, but the articles I read about cascade deletes involved using queries). Give it a go, see what happens.... You don't need to create a query, just have a look at the code I've put in above and consult the help file

 

[vbaInet]

Missed your question James. Greater flexibility (as you know) using SQL/query to delete, whereas the DeleteRecord command deletes the current record. If the cursor is in the subform it will attempt to delete the current record in the SUBFORM's recordset. So to delete the main record using that method, your cursor should be in the main form.

 

[JamesMcS]

Perhaps that's the problem then.... vb9063, have you tried the SQL method?

 

[vb9063]

You can delete a record using vba.

First of all, does the main record get deleted?

Noo, not in the main form...i created a test form without the subforms and it seemed to delete the record eventually after a bit of opening and closing and then it worked quite well...

 

[vb9063]

Perhaps that's the problem then.... vb9063, have you tried the SQL method?

Hi James, no not yet , how do I reference the record that is currently selected? you put me.supplierno ??

 

[JamesMcS]

Yep - if you're using RunSQL in the command button on the main form, all you need to do is tell it which supplier number to look at. So, where I've said me.supplierno, just put me. and your supplier ID's control name. You'll need to requery the subforms too:

[subformname].requery

I think

Make a backup copy of the table though, just in case

 

[vbaInet]

Just throwing out another suggestion, you can create a DELETE query and reference the control in the query using Forms![NameOfForm]![ControlName] and for everytime you want to delete you EXECUTE the query.

I prefer the RUNSQL method anyway.

 

[vb9063]

Hi guys, thanks very much but its driving me a bit mad so I might just go back to the wizard button and I dont really have much time to fiddle with i still have to fix all the other problems I have created!.

Is there a way to stop the warning message coming up with the delete cascading warning message when you press the button???