Refrech after delete recordset

[Guest]

Hello,
There is a form with a subform.
In the AfterUpdate event of the form's FieldA I wrote some code to filter
and delete some records of the subForm.
The result is OK but after the deletion, the deleted lines remain in the
subForm between the non deleted (I can select but I can't edit them) and the
only way to overcome this (have only the not deleted records in the subform)
is to go to another record of the main form and return back.
Here is the code I use:

Private Sub FieldA_AfterUpdate()
Dim db As Database
Dim rst As Recordset
Dim strSql As String
Dim strField As String

Set db = CurrentDb()

strField = [FieldA] ‘FieldA is in mainForm and tblMyTable is the table used
by the Subform

strSql = "SELECT tblMyTable.FieldB, tblMyTable.FieldC FROM tblMyTable WHERE
((tblMyTable.FieldB)= " & "'" & strFiedA & "'" & ")"

Set rst = db.OpenRecordset(strSql, dbOpenDynaset)


If rst.RecordCount <> 0 Then
rst.Delete

End If

End Sub

 

[Neil]

Hello,

I think your only problem here is that you need to requery the subform after
you have deleted the records:

If rst.RecordCount <> 0 Then
rst.Delete
' Requery the sub form
Me.SubFormControlName.Requery
End If

Replace SubFormControlName with the name of the sub form control used on the
main form (this may not be the same as the subform's name). Use [] if the
name contains spaces (eg Me.[Sub Form Control Name].Requery).

HTH,

Neil.