Re: Delete Record from both Main and Subform while maintaining position
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Thu, 06 Jul 2006 23:47:13 -0500
dbmaker wrote:
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
--
Marsh
MVP [MS Access]
.
- Follow-Ups:
- Re: Delete Record from both Main and Subform while maintaining position
- From: dbmaker via AccessMonster.com
- Re: Delete Record from both Main and Subform while maintaining position
- References:
- Prev by Date: Re: Invoking a query using button and displaying on subform
- Next by Date: Re: How Do I Programmatically Append From DBF?
- Previous by thread: Delete Record from both Main and Subform while maintaining position
- Next by thread: Re: Delete Record from both Main and Subform while maintaining position
- Index(es):
Relevant Pages
|
