By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,329 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Deleting record from bound form

100+
P: 122
In Access 2007, I am trying to add a button to my sata entry split form that will delete the current record, after prompting, but I am running into a bizarre issue.

Here is the relevant code. Pressing the button triggers the btnDelete_Click() subroutine, which calls DeleteRecord() which in turn can call MoveLine().

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnDelete_Click()
  2.   L = txtLine.Value
  3.   If Form.NewRecord Then
  4.     'The current record has never been saved, so simply clear the form
  5.     Form.Undo
  6.     txtItem.SetFocus
  7.   Else
  8.     'Prompt user with confirmation dialog
  9.     rVal = MsgBox("Are you sure you want to delete this record?", vbYesNo)
  10.     If rVal = vbNo Then Exit Sub
  11.  
  12.     'Delete the current record and update datasheet
  13.     R = Form.CurrentRecord
  14.     DeleteRecord DetailID
  15.     Me.Requery
  16.  
  17.     'Go to the record after the one we just deleted, or go to the end if we deleted the last record
  18.     If R > Form.Recordset.RecordCount Then
  19.       DoCmd.GoToRecord acActiveDataObject, , acLast
  20.     Else
  21.       DoCmd.GoToRecord acActiveDataObject, , acGoTo, R
  22.     End If
  23.   End If
  24. End Sub
  25.  
  26. Private Sub DeleteRecord(ID)
  27.   'First, count the number of records in the order
  28.   Ord = cmbOrder.Value
  29.   Cnt = DCount(1, "OrderDetail", "OrderNumber = " & Ord)
  30.  
  31.   'Delete the specified record
  32.   DoCmd.SetWarnings False
  33.   Line = DLookup("LineID", "OrderDetail", "DetailID = " & ID)
  34.  
  35.   RunCommand acCmdDeleteRecord
  36.   DoCmd.SetWarnings True
  37.  
  38.   'If the record we just deleted was not the last record, shift any records after it down by one
  39.   If Line < Cnt Then MoveLine Line, Cnt, Ord
  40. End Sub
  41.  
  42. Private Sub MoveLine(Start, Finish, Ord)
  43.   'Don't do anything if start and finish are the same, or if there are no records to move
  44.   If Finish < 1 Then Finish = 1
  45.   If Start = Finish Then Exit Sub
  46.   Cnt = DMax("LineID", "OrderDetail", "OrderNumber = " & Ord)
  47.   If Cnt < 1 Then Exit Sub
  48.   If Finish > Cnt Then Finish = Cnt
  49.  
  50.   'If we're moving a record down, adjust the intervening records up and vice versa
  51.   If Start > Finish Then Adj = "+ 1" Else Adj = "- 1"
  52.  
  53.   'Renumber the affected lines
  54.   DoCmd.SetWarnings False
  55.   Query = "UPDATE [OrderDetail] SET [LineID] = [LineID] " & Adj & ", [RenumberLine] = [LineID] " & Adj & " WHERE [LineID] BETWEEN " & _
  56.           Start & " AND " & Finish & " AND [OrderNumber] = " & Ord & ";"
  57.   DoCmd.RunSQL Query, False
  58.   DoCmd.SetWarnings True
  59. End Sub
The issue is this: deleting the item itself works fine, so long as they don't then try to add another item before closing the form. If they delete and item and then add a new item, a really bizarre thing happens. The line appears twice in the split form portion, though the second one is unselectable. Selecting or scrolling the records near the phantom record causes them to sometimes display the incorrect information for that line. There are not two records in the underlying table, and closing the form and reopening it makes everything fine again.

I added the requery line specifically to try to get the form back in sync with the table, but it doesn't seem to help. Any suggestions as to how best to resolve this are greatly appreciated.
Apr 1 '10 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.