Delete Record Not Working when Subform Present

[Jason1971]

Hi,
I have a Delete Record button on a Main Form which works fine, however, if I insert a subform into this Main Form, the Delete Record no longer deletes? (The subform contains another subform). Perhaps the problem lies with the record source select statement within the Main Form?? Any clues anyone? Thanks in Advance.
Jason.

 

[dcb]

I would think that you need to delete the related records in the subform first

Referential Integrity

 

[vbaInet]

Hi,
I have a Delete Record button on a Main Form which works fine, however, if I insert a subform into this Main Form, the Delete Record no longer deletes? (The subform contains another subform). Perhaps the problem lies with the record source select statement within the Main Form?? Any clues anyone? Thanks in Advance.
Jason.

Does it throw up an error? If it worked prior to adding the subform, it could be that you added a record using the subform, which could fire referential integrity validation as dcb pointed. Did you just add a record in the subform?

 

[Jason1971]

I have cascading delete switched on for all subforms.
When I click on the delete button I do not get an error and the data does disappear from the MainForm, however, when I hit refresh the deleted record reappears on the form (the underlying MainTable remains populated with the "deleted" record and hence it repopulates the MainForm.)
I have delete record buttons on the subform and the subsubform and they both work fine. Its just the delete button on the Mainform thats not deleting?? I've tried redoing the delete button on the MainForm but it still doesn't work. Thank you for your responses, I am most grateful. Anyone have any ideas?

 

[vbaInet]

I have cascading delete switched on for all subforms.
When I click on the delete button I do not get an error and the data does disappear from the MainForm, however, when I hit refresh the deleted record reappears on the form (the underlying MainTable remains populated with the "deleted" record and hence it repopulates the MainForm.)
I have delete record buttons on the subform and the subsubform and they both work fine. Its just the delete button on the Mainform thats not deleting?? I've tried redoing the delete button on the MainForm but it still doesn't work. Thank you for your responses, I am most grateful. Anyone have any ideas?

Check the code of your delete button.

 

[Jason1971]

Check the code of your delete button.

The Delete Button was created by the Access Wizard feature so its Macro code looks ok. The same Macro code works on the delete buttons in the subform and subsubform.

 

[vbaInet]

What are the names of your fields that you would like to delete?

Also in place of the macro (don't delete it yet), use vba code. Put this on the On Click event of the button:

Docmd.RunCommand acCmdDeleteRecord

 

[Jason1971]

DoCmd.RunCommand acCmdDeleteRecord doesn't work; my mainform is really up the creek. Me.DeleteRecord doesn't work either. So I've had to switch to sql delete query in vba. Interesting...Me.Refresh doesn't work in the code below, however, the code does delete the record now ok. Trouble is, because the Me.refresh doesn't work my Main Form is left with "#deleted" in every field
I really need to get my Main Form fixed up so my Me.etc commands start working again.


Private Sub DeletePatient1_Click()

Dim CurrentRecordPatientID As Integer

If Me.NewRecord And Not Me.Dirty Then
Beep
Else
If Me.NewRecord And Me.Dirty Then
Me.Undo
Else
If Not Me.NewRecord Then
CurrentRecordPatientID = Me.ID
CurrentDb.Execute "DELETE FROM Tbl_Patient WHERE ID = " & CurrentRecordPatientID
Me.Refresh
Else 'Do Nothing
End If
End If
End If

End Sub

 

[dcb]

I really need to get my Main Form fixed up so my Me.etc commands start working again.

This normally happens when you have a duplicate decleration on the form

ie.

Private Sub cmd_Click()


End Sub


Other stuff.......

Private Sub cmd_Click()


End Sub

 

[Jason1971]

Hooray! Finally solved it! The Record Source was incorrect. It was referencing some weird, long select statement; when all that was required was a simple, correct reference to the underlying table which the Main Form was based on i.e. Table1. Now the docmd's and Me.'s are working. Thank you to all those that had some input.
Jason.