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

  • Thread starter Thread starter Bretona10
  • Start date Start date
B

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
 
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
Click to expand...

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
 
[email protected] said:
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
Click to expand...

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
Click to expand...
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
 
[email protected] said:
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
Click to expand...
Click to expand...
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):
Click to expand...
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
Click to expand...
Regards,
Branislav Mihaljev
Microsoft Access MVP
Click to expand...

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
Click to expand...

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
 
Back
Top