Delete Record from both Main and Subform while maintaining position

[dbmaker]

Hello All, first time posting here!

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't
delete the record from both tables.

// start of code for the delete button

Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError

Me.Requery
Me.sfmOrderDetails.Form.Requery

Me.Bookmark = rst.Bookmark
Set rst = Nothing

// end of code for delete button

Thanks!

 

[Marshall Barton]

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the
same postion relative to the other records. For example, if I am scrolling
through the records and want to delete record #45, After I delete it I would
like to be able to have record #44 visible to the user.

I am currently using (2) SQL statements to delete the records from the main
Order table and the Order Details table which are linked without Cascade
Delete Related Records being on.

I had set-up a recordset procedure to try and find the bookmark set before
deletion but the only way I can get the sub-form to not show a blank form (no
controls visible for the record just deleted and the #deleted in controls on
the main form) is to requery after the delete, which loses the bookmark. If
i place the ReQuery at the end it displays the records properly, (without the
blank record) but returns the record to the first record. I have tried
turning off any sorting references to OrderBy, etc. but it still returns to
the first record.

I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't
delete the record from both tables.

// start of code for the delete button

Dim rst As Recordset
Dim strSearchName As String

Me.AllowDeletions = True
Me.sfmOrderDetails.Form.AllowDeletions = True

Set rst = Me.RecordsetClone
strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value)
rst.FindFirst "Order_No = " & strSearchName

CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " &
Order_ID,dbFailOnError
CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " &
Order_ID, dbFailOnError

Me.Requery
Me.sfmOrderDetails.Form.Requery

Me.Bookmark = rst.Bookmark
Set rst = Nothing

// end of code for delete button

You need to do several things here.

First, make sure that the record is not dirty. If it is,
clear the changes by using Me.Undo.

Second, make sure that you are not on a new record. A new
record has not been saved so it can't be deleted. Just skip
any further processing.

Third, you need to save the primary key of the record, so
you can search for the previous record after the Requery.

Here's some air code that provides a general outline of the
code:

Dim varKey As Variant
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then
varKey = Me.primarykeyfield
CurrentDb.Execute "DELETE * FROM tblOrderDetails . . ."
CurrentDb.Execute "DELETE * FROM tblOrders . . ."
Me.Requery
With Me.RecordsetClone
.FindLast "Order_ID < " & varKey
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If

 

[dbmaker via AccessMonster.com]

Thanks,

Your reply worked perfect!

I have been playing around with this for quite some time. I truly appreciate
your time as well as your approach to the code. I Was not familiar with the
".FindLast "Order_ID < " & varKey" statement.

Thanks, again

I have an ordering db that utilyzes the typical Form / Subform layout. I
would like to be able to delete a record from the Main form and maintain the

[quoted text clipped - 42 lines]

// end of code for delete button

You need to do several things here.

First, make sure that the record is not dirty. If it is,
clear the changes by using Me.Undo.

Second, make sure that you are not on a new record. A new
record has not been saved so it can't be deleted. Just skip
any further processing.

Third, you need to save the primary key of the record, so
you can search for the previous record after the Requery.

Here's some air code that provides a general outline of the
code:

Dim varKey As Variant
If Me.Dirty Then Me.Undo
If Not Me.NewRecord Then
varKey = Me.primarykeyfield
CurrentDb.Execute "DELETE * FROM tblOrderDetails . . ."
CurrentDb.Execute "DELETE * FROM tblOrders . . ."
Me.Requery
With Me.RecordsetClone
.FindLast "Order_ID < " & varKey
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With
End If