Requery causes Error 3246

What code are you using to Delete records on S1?

You might try adding this code in SF1 at the end of your Delete code:

Me.Requery
Me.Parent.SF2.Form.Refresh

mx

My guess is that you need to requery SF1 and set the focus to a specific record in that subform before you requery SF2.

If the two subforms are both child forms of the main form, then the records in one subform should not need a requery when something in the other subform changes.

Again, if both form are child forms of the main form, then the main form will dictate what child record each subform is synchronized to.

For example:
Customers is the main form (CustID is the PK)
Orders is child form1, linked on CustID
Complaints is child form 2, also linked to the Customer form on CustID
(One Customer can have many Orders, One Customer can have many Complaints)
In this scenario, no requery of either subform is required.

In other words, the two subforms are not related to each other directly.
They are only linked to the main form.
So it is irrelevant what you do in either subform.

...Or are these forms Linked Hierarchically?
Customers-->Orders-->OrderDetails

In this case if you are deleting a record in "Orders" when you still have associated OrderDetail records, then this may be part of the issue.
(A violation of Referential integrity)

So can you:
1. Back up and first explain how each of these forms relates to the others.
2. Post the code you are using.
3. Explain how the Overall form is designed/structured.

JeffCoachman

Hi mx,
<What code are you using to Delete records on S1?>
I'm talking about manual deletions.

Hi fyed,
<you need to requery SF1>
When I tried that I got the same error.

Hi Jeff,
<If the two subforms are both child forms of the main form, then the records in one subform should not need a requery when something in the other subform changes. Again, if both form are child forms of the main form, then the main form will dictate what child record each subform is synchronized to.>
The main form is unbound.

"I'm talking about manual deletions."

OK ... You basically need these four pieces to implement deleting a record. When you 'manually' delete a record, these events will fire:

Optional: Create a Button on the form to Delete:

Private Sub btnDelete    
    On Error Resume Next    'To trap case where user cancels Delete
    DoCmd.RunCommand acCmdDeleteRecord
    If Err.Number = 0 Or Err.Number = 2501 Then
        'no action needed.  0 means no error occurred; 2501 means Delete was cancelled in the Delete event below.
    Else
        MsgBox "An unexpected error has occurred when attempting to Delete this record:" & vbCrLf & vbCrLf & _
                  Err.Number & "  " & Err.Description, vbOKOnly, "DELETE RECORD"
    End If
End Sub
-------------------------

Then,put this code in the following three Form events:

Private Sub Form_Delete(Cancel As Integer)
    Beep
   'This message or something similar
    If MsgBox("Are you SURE you want to DELETE this record?." & Chr(13) & _
          "This operation cannot be undone", _
           vbExclamation + vbYesNo + vbDefaultButton2, "Delete Record") <> vbYes Then
        Cancel = True
    End If
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
  Response = acDataErrContinue
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)    
    Me.Requery
    DoEvents
End Sub
---------------
mx

Hi all,
I solved the problem using this the attached code.

Hi mx,
I developed this before I saw your last post. Looks like you had the same idea.
What I can't explain is that when I moved the Me.Requery from the AfterDelConfirm event to the Current event (which is the very next event that fires), I get the Error 3246.

Private Sub Form_AfterDelConfirm(Status As Integer): If SkipEvents Then Exit Sub Else If TraceEvents Then Stop
   Dim bm As String
   Dim rs As DAO.Recordset
   
   'Initialize
   Set rs = Me.Recordset
   bm = Me.Bookmark
   
   Me.Requery
   Me.Bookmark = bm
   rs.MovePrevious
   
   Set rs = Nothing

End Sub

Select allOpen in new window

Dim bm As String


A Bookmark variable must be a Variant.

And once you Requery, the initial bookmark is no longer valid.  So, if you are trying to reposition, you need to capture an ID or some other unique value, save that, do the requery then use the Recordsetclone to FindFirst that ID, then set the Form Bookmark to the Clone bookmark.

mx

Hi mx,
Thanks. There are actually a few bugs with my 'solution':
- i get errors when I single step.
- if I filter or sort before I delete, the bookmark will point to the wrong record after deletion
- the bookmark will point to the worng record after i delete the last record.

I will try your last post and see if it solves these problems.

ok

Hi mx,
Your last post worked like a charm. Thanks!

You are welcome.

mx