ms access - MSAccess - Requery a Subform After Insert?

Question

Background:

I have a subform (datasheet) I update using a datasheet checkbox afterupdate event:

1. I clone the clicked record & Insert into the referenced table via an Insert Query
2. I modify the original record to differentiate from the Inserted record via an Update Query

To avoid RecordLock complaints, I have inserted: SendKeys "+{Enter}", True after each of the above to save the updates - is there a better way to do this??

Next I need to requery the subform to display the newly inserted record AND use a bookmark (?) to retain the cursor position of the original record (inserted record will be adjacent). Subform datasheet recordset is based on a query.

Question:

1. From within the subform's afterupdate event, using Forms![ParentForm].[SubForm].Form.Requery does not produce an error, but does open the code window with the line highlighted in Yellow.

2. Next attempt, from within the subform's afterupdate event, I have attempted to set focus to a control on the ParentForm BEFORE using Forms![ParentForm].[SubForm].Form.Requery, but I get a similar ~silent error~ as noted above.

3. What is the proper way to REQUERY a subform from within the same subform?

Thanks!

Answer 1

You are making it way too hard for yourself. Here is how to copy a record from a button click. No locks, no queries, and auto update of the form:

Private Sub btnCopy_Click()

  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field

  If Me.NewRecord = True Then Exit Sub

  Set rstInsert = Me.RecordsetClone
  Set rstSource = rstInsert.Clone
  With rstSource
    If .RecordCount > 0 Then
      ' Go to the current record.
      .Bookmark = Me.Bookmark
      With rstInsert
        .AddNew
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "SomeFieldToHandle" Then
                rstInsert.Fields(.Name).Value = SomeSpecialValue
              ElseIf .Name = "SomeOtherFieldToHandle" Then
                rstInsert.Fields(.Name).Value = SomeOtherSpecialValue
              ElseIf .Name = "SomeFieldToSkip" Then
                ' Leave empty or with default value.
              ElseIf .Name = "SomeFieldToBlank" Then
                rstInsert.Fields(.Name).Value = Null
              Else
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
          Next
        .Update
        ' Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
  End With

  Set rstInsert = Nothing
  Set rstSource = Nothing

End Sub

If the button is placed on the parent form, replace Me with:

Me!NameOfYourSubformControl.Form

Between AddNew and Update you can modify and insert code to adjust the value of some fields that should not just be copied.