Deleting Records in a subform after change in field on main form.

[Bretona10]

Hi everyone, I have a data entry form [RMA Entry Form]with one subform
[RMADetail Form]. There is a field on the mainform [combo32] that when its
value changes I want to delete any existing records in the subfor.

Thanks in advance
Bret

 

[banem2]

Hi everyone, I have a data entry form [RMA Entry Form]with one subform
[RMADetail Form]. There is a field on the mainform [combo32] that when its
value changes I want to delete any existing records in the subfor.

Thanks in advance
Bret

To delete all records in subform, use AfterUpdate event of combo32.
This will delete "any existing records" (you didn't mention is the
subform is linked with main form and you want to delete only the
records you see):

Sub combo32_AfterUpdate()
On Error GoTo ErrHandler
Dim strSQL As String
strSQL = "DELETE * FROM RMADetail"
CurrentDb.Execute strSQL, dbFailOnError
[RMADetail Form].Requery
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Bretona10]

Hi everyone, I have a data entry form [RMA Entry Form]with one subform
[RMADetail Form]. There is a field on the mainform [combo32] that when its
value changes I want to delete any existing records in the subfor.

Thanks in advance
Bret

To delete all records in subform, use AfterUpdate event of combo32.
This will delete "any existing records" (you didn't mention is the
subform is linked with main form and you want to delete only the
records you see):

Sub combo32_AfterUpdate()
On Error GoTo ErrHandler
Dim strSQL As String
strSQL = "DELETE * FROM RMADetail"
CurrentDb.Execute strSQL, dbFailOnError
[RMADetail Form].Requery
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP

Yes it is linked and I only want to delete the visible records.
My form is Data Entry Mode with no record movement keys visible.

Thanks

 

[banem2]

Hi everyone, I have a data entry form [RMA Entry Form]with one subform
[RMADetail Form]. There is a field on the mainform [combo32] that when its
value changes I want to delete any existing records in the subfor.
Thanks in advance
Bret

To delete all records in subform, use AfterUpdate event of combo32.
This will delete "any existing records" (you didn't mention is the
subform is linked with main form and you want to delete only the
records you see):

Sub combo32_AfterUpdate()
On Error GoTo ErrHandler
Dim strSQL As String
strSQL = "DELETE * FROM RMADetail"
CurrentDb.Execute strSQL, dbFailOnError
[RMADetail Form].Requery
Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub

Regards,
Branislav Mihaljev
Microsoft Access MVP

Yes it is linked and I only want to delete the visible records.
My form is Data Entry Mode with no record movement keys visible.

Thanks

In this case you will need to change this line

strSQL = "DELETE * FROM RMADetail"

to

strSQL = "DELETE * FROM RMADetail " & _
"WHERE IDField = " & Me.IDField

(change "IDField" to your ID field name).

Regards,
Branislav Mihaljev
Microsoft Access MVP