delete sub and stay on same parent record

[deb]

access 2003

Main form has a option group
Subform is a continuous form
if user changes the option group the subform data is deleted.
see code...
Private Sub optCTorSTorGEN_AfterUpdate()
If Me.NewRecord = False Then
strMsg = "Are you sure you want to change?" _
& vbCr & vbCr & "This will erase the corresponding data to allow for new
input."
ans = MsgBox(strMsg, vbYesNo, "Confirm Changes")
If ans = vbNo Then
Me.Undo
Me.optCTorSTorGEN.Requery
MsgBox "The data has NOT been changed.", vbExclamation,
"Important"
Me.UnitNo.Requery
Exit Sub
Else

'Prepare for deleting record
Me.f019WarrantyDetails.Form.AllowDeletions = True
Dim strPONumber As String
Dim strSQL As String
strWarrantyUnitID = WarrantyUnitID.Value

strSQL = "DELETE * FROM [t72WarrantyDetails] " _
& "WHERE [t72WarrantyDetails].WarrantyUnitID = " _
& strWarrantyUnitID

CurrentDb.Execute strSQL, dbFailOnError
' Me.Requery
' Me.UnitNo.Requery
End If
End If

If I take out the me.requery then user gets #Deleted in all fields. pretty
ugly
If I leave the me.requery it sets the record back to the first record in the
parent form. User will get confused

How can I delete the subform data and have it remain on the current parent
record with cleared fields?

Please help this is beyond my capabilities.

 

[Dirk Goldgar]

access 2003

Main form has a option group
Subform is a continuous form
if user changes the option group the subform data is deleted.
see code...
Private Sub optCTorSTorGEN_AfterUpdate()
If Me.NewRecord = False Then
strMsg = "Are you sure you want to change?" _
& vbCr & vbCr & "This will erase the corresponding data to allow for
new
input."
ans = MsgBox(strMsg, vbYesNo, "Confirm Changes")
If ans = vbNo Then
Me.Undo
Me.optCTorSTorGEN.Requery
MsgBox "The data has NOT been changed.", vbExclamation,
"Important"
Me.UnitNo.Requery
Exit Sub
Else

'Prepare for deleting record
Me.f019WarrantyDetails.Form.AllowDeletions = True
Dim strPONumber As String
Dim strSQL As String
strWarrantyUnitID = WarrantyUnitID.Value

strSQL = "DELETE * FROM [t72WarrantyDetails] " _
& "WHERE [t72WarrantyDetails].WarrantyUnitID = " _
& strWarrantyUnitID

CurrentDb.Execute strSQL, dbFailOnError
' Me.Requery
' Me.UnitNo.Requery
End If
End If

If I take out the me.requery then user gets #Deleted in all fields.
pretty
ugly
If I leave the me.requery it sets the record back to the first record in
the
parent form. User will get confused

How can I delete the subform data and have it remain on the current parent
record with cleared fields?

Please help this is beyond my capabilities.

Don't requery the main form, requery the subform. Instead of:

' Me.Requery

.... say:

Me.f019WarrantyDetails.Requery

As a side note, I don't think this line:

Me.f019WarrantyDetails.Form.AllowDeletions = True

.... is necessary or desirable. You aren't deleting records through the
subform, but rather via the delete query you are executing. The subform's
AllowDeletions property is irrelevant to that.

 

[Beetle]

Like this;

Private Sub optCTorSTorGEN_AfterUpdate()
If Me.NewRecord = False Then
strMsg = "Are you sure you want to change?" _
& vbCr & vbCr & "This will erase the corresponding data to allow for new
input."
ans = MsgBox(strMsg, vbYesNo, "Confirm Changes")
If ans = vbNo Then
Me.Undo
Me.optCTorSTorGEN.Requery
MsgBox "The data has NOT been changed.", vbExclamation,
"Important"
Me.UnitNo.Requery
Exit Sub
Else

'Grab the pk of the current record on the parent form
Dim intPK As Integer
intPK = Me!PKField

'Prepare for deleting record
Me.f019WarrantyDetails.Form.AllowDeletions = True
Dim strPONumber As String
Dim strSQL As String
strWarrantyUnitID = WarrantyUnitID.Value

strSQL = "DELETE * FROM [t72WarrantyDetails] " _
& "WHERE [t72WarrantyDetails].WarrantyUnitID = " _
& strWarrantyUnitID

CurrentDb.Execute strSQL, dbFailOnError
Me.Requery
'Move back to the record we were on before
With Me.RecordsetClone
.FindFirst "PKField = " & intPK
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

End If
End If


I have assumed that your pk filed is an integer data type. If not
you'll need to modify the variable declaration and the .FindFirst
line in the With statement.

 

[Beetle]

Actually, Dirk has the better answer (not surprising).

 

[deb]

thank you, for knowing where to hit the machine
--
deb

access 2003

Main form has a option group
Subform is a continuous form
if user changes the option group the subform data is deleted.
see code...
Private Sub optCTorSTorGEN_AfterUpdate()
If Me.NewRecord = False Then
strMsg = "Are you sure you want to change?" _
& vbCr & vbCr & "This will erase the corresponding data to allow for
new
input."
ans = MsgBox(strMsg, vbYesNo, "Confirm Changes")
If ans = vbNo Then
Me.Undo
Me.optCTorSTorGEN.Requery
MsgBox "The data has NOT been changed.", vbExclamation,
"Important"
Me.UnitNo.Requery
Exit Sub
Else

'Prepare for deleting record
Me.f019WarrantyDetails.Form.AllowDeletions = True
Dim strPONumber As String
Dim strSQL As String
strWarrantyUnitID = WarrantyUnitID.Value

strSQL = "DELETE * FROM [t72WarrantyDetails] " _
& "WHERE [t72WarrantyDetails].WarrantyUnitID = " _
& strWarrantyUnitID

CurrentDb.Execute strSQL, dbFailOnError
' Me.Requery
' Me.UnitNo.Requery
End If
End If

If I take out the me.requery then user gets #Deleted in all fields.
pretty
ugly
If I leave the me.requery it sets the record back to the first record in
the
parent form. User will get confused

How can I delete the subform data and have it remain on the current parent
record with cleared fields?

Please help this is beyond my capabilities.

Don't requery the main form, requery the subform. Instead of:

' Me.Requery

... say:

Me.f019WarrantyDetails.Requery

As a side note, I don't think this line:

Me.f019WarrantyDetails.Form.AllowDeletions = True

... is necessary or desirable. You aren't deleting records through the
subform, but rather via the delete query you are executing. The subform's
AllowDeletions property is irrelevant to that.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)