Deleting Records in a subform after change in field on main form. Bretona10 12/10/2008 5:31:00 AM 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

Re: Deleting Records in a subform after change in field on main form. banem2[ at ]gmail.com 12/10/2008 7:05:33 AM On Dec 10, 6:31 am, Bretona10 <Breton...[ at ]discussions.microsoft.com> wrote: [Quoted Text] > 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

Re: Deleting Records in a subform after change in field on main fo Bretona10 12/11/2008 4:08:00 AM "banem2[ at ]gmail.com" wrote: [Quoted Text] > On Dec 10, 6:31 am, Bretona10 <Breton...[ at ]discussions.microsoft.com> > wrote: > > 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

Re: Deleting Records in a subform after change in field on main fo banem2[ at ]gmail.com 12/11/2008 6:45:39 AM On Dec 11, 5:08 am, Bretona10 <Breton...[ at ]discussions.microsoft.com> wrote: [Quoted Text] > "ban...[ at ]gmail.com" wrote: > > On Dec 10, 6:31 am, Bretona10 <Breton...[ at ]discussions.microsoft.com> > > wrote: > > > 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