Delete records displayed in a Form

[G Lam]

Hi, I make a form with subform that displays filtered records from two
tables (1 to many).
The user can edit the records in the many side in the subform. I also
included a Delete button to delete all the records in the subform. The codes
as following. I notice it takes a fraction of a second to delete the
records. Beside, by design, Access 2000 can only delete the many side
records in this manner, the record in the 1 side table is not deleted.
1. Is there a better way to do it?
2. How can I also delete the record in the table of the 1 side, with this
one click?
*****************************************************
Private Sub EditDelBox_Click()
On Error GoTo Err_EditDelBox_Click

Dim i As Integer
i = Forms!frmMainEdit!fsfrSubEdit.Form.RecordsetClone.RecordCount
Do Until i = 0
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
Application.SetOption "Confirm Action Queries", False '
i = i - 1
Loop
Exit_EditDelBox_Click:
Exit Sub

Err_EditDelBox_Click:
MsgBox Err.Description
Resume Exit_EditDelBox_Click

End Sub

 

[Sam]

Not sure why you've coded this delete stuff. If the user selects the records
using the record selectors and hits del (or uses the menu equivalent)
doesn't it just do the same thing???

To get rid of the 1 side records and related records on the many side, just
set cascade delete for the join in the relationships window. The user then
just selects the single 1 side record, hits delete and they're all deleted.

By default Access generates a warning before deleting the records. This
warning is a bit cryptic for your average user, I usually create one, in
code, that's a bit more meaningful for the given situation.

HTH
Sam

 

[G Lam]

Sam,
The menu and the buttons are hitten from the user, because the users don't
know about Access much. So, I have to make these buttons to do the work.
I have C programing knowledge and am learning Access VBA. I found this NG
quite helpful.
Gary

 

[Heiko]

Private Sub EditDelBox_Click()
On Error GoTo Err_EditDelBox_Click
Dim rs As Recordset
Dim strBMark As String
Set rs = Forms!frmMainEdit!fsfrSubEdit.Form.RecordsetClone
With rs
Do Until .EOF
.Delete
.MoveNext
Loop
End With
DoCmd.SetWarnings False
RunCommand acCmdDeleteRecord
Exit_EditDelBox_Click:
DoCmd.SetWarnings True
Exit Sub
Err_EditDelBox_Click:
MsgBox Err.Description
Resume Exit_EditDelBox_Click
End Sub

Heiko

 

[KW]

A QueryDef would be the best approach from a performance
standpoint. The appraoch is as follows:

dim db as database
dim qdf1 as querydef
dim qdf2 as querydef

dim SQLDELStr1 as string
dim SQLDELStr2 as string

set db=CurrentDB

SQLDELStr1="Some Delete Query for subform information"

SQLDELStr2 = "some Delete Query for main form information"

set qdf1=db.CreateQueryDef("",SQLDELStr1)
qdf1.Execute

set qdf2=db.CreateQueryDef("",SQLDELStr2)
qdf2.Execute

db.close

Hope that helps!