レコードを複製して複製後のレコードを選択状態にしたい

Question

実現したいこと

Access2013で、メインフォームの中に、テーブルに連結されたデータシート形式のサブフォームと、ボタンを配置します。
サブフォーム内の行が選択された状態でメインフォームのボタンを押すと、選択中の行のレコードを複製し、サブフォームは複製されたレコードも表示し、かつ複製されたレコードが選択された状態になるようにしたいです。少しわかりにくいですね。。。

かみ砕いて言うと、ボタンを押すとサブフォーム内の選択中の行がコピー（複製）され、コピー後の行が選択された状態にしたい、というイメージです。

サブフォームのレコードソースとなるテーブルは以下の通り。

テーブル：t1

• ID : オートナンバー型（Pkey)
• col1, col2, col3 : 短いテキスト型

試したこと

メインフォームに配置した複製用のボタンのイベントとして、以下のようなVBAを記述しました。
（本質部分だけをかいつまんでいるためエラー処理等は省いた最低限のコードです）

Private Sub ボタン_Click()
    Dim ws As DAO.Workspace: Set ws = DBEngine(0)
    Dim db As DAO.Database: Set db = CurrentDb
    
    ' サブフォームの選択中の行を示すIDを取得
    Dim selectedID As Integer: selectedID = Me.subform.Form.ID
    
    ' 選択中の行に相当するテーブル上のデータを複製する
    Dim sql As String: sql = "INSERT INTO t1 (col1, col2, col3) SELECT col1, col2, col3 FROM t1 WHERE ID = " & selectedID & ";"
    ws.BeginTrans
    db.Execute sql
    
    ' 上で挿入したデータの主キーを検索する
    newID = db.OpenRecordset("SELECT @@IDENTITY")(0)
    
    ws.CommitTrans
    db.Close: Set db = Nothing
    ws.Close: Set ws = Nothing
    
    ' サブフォームを描画しなおし、挿入したデータを選択する
    Me.subform.Requery
    Me.subform.Form.Recordset.FindFirst "ID = " & newID
End Sub

これによって、1度ボタンを押下すると

• 選択中の行と同じ（ID列のみ違う）が複製され
• サブフォームが再描画され
• 挿入したデータが表示され
• 挿入したデータが選択状態になる

までは実現できたのですが・・・

困っていること

上記のボタンをもう1度（つまり2回目）押すと、
「実行時エラー '3420': オブジェクトが正しくないか、現在設定されていません。」
となります。これは、ボタンを押す際にどの行を選択していても（SQLで自動で挿入された行でも、元々あった行であっても）2回目以降のボタン押下で必ず発生してしまいます。

デバッガで見る限り、1回目にボタンを押した際にはMe.subForm.Form.Recordsetに異変はおきていないようなのですが、
2回目にボタンを押して Me.subForm.Requery を実行した段階で、Me.subForm.Form.Recordset が Nothingになるようです。

知りたいこと

上記の「実現したいこと」を実現できる、よりスマートな方法があれば教えていただきたいです。
（データの複製・挿入、は単にSQLの話ですので問題ないと思っていますが、どちらかというとACCESS特有の部分である「データシート上でレコードを自動選択する」という部分です。データシートの選択を、Form.Recordset.FindFirst で実施する、というのはネット上のサンプルを探している中で拾ってきたため、本当にこれが正しいのかイマイチわかっていません）

また、可能ならば上記のコードで2回目のRequery（2回目のボタン押下時）で、なぜMe.subForm.Form.RecordsetがNothingになるのか、についても教えていただきたいです。

Answer 1

手作業でするなら、サブフォームのレコードを選択して、コピー、追加貼り付け でできますので、それをそのままVBA化すれば簡単です。

Private Sub ボタン_Click()
    Me.subform.SetFocus                'サブフォームにフォーカス移動
    DoCmd.RunCommand acCmdSelectRecord 'カレントレコードを選択
    DoCmd.RunCommand acCmdCopy         'コピー
    DoCmd.RunCommand acCmdPasteAppend  '追加貼り付け
End Sub

別案

コメントで、サブフォームは更新、追加不可、フィールドによってNullにしたり加工したい場合があるとのことでしたので、その場合でも簡単に対応できる方法のサンプルです。
RecordsetCloneプロパティを使うと、更新の許可、追加の許可プロパティを「いいえ」にしてあっても更新、追加可能なので、それを利用して追加後、最終レコード(=追加したレコード)へ移動。
データの加工は必要に応じて加工してから代入すればいいでしょう。

Private Sub ボタン_Click()
    Dim sf As Form
    Set sf = Me.subform.Form
    With sf.RecordsetClone
        .AddNew
        !col1 = sf.col1
        !col2 = sf.col2
        !col3 = sf.col3
        .Update
    End With
    Me.subform.SetFocus
    DoCmd.GoToRecord acActiveDataObject, , acLast '最終レコードへ移動
End Sub

RecorsetCloneプロパティについては、下記を参考にしてください。
フォームの Recordset, RecorsetClone, RecordSet.Clone の違いとは？ - hatena chips

Comments

[na999]

hatena19さん、返信ありがとうございます。おおー！こんな簡単に書くこともできるんですね！！

実際には質問するにあたり、簡易なサンプルを作成する中で実要件から落としている条件がいくつかあり（サブフォームは追加・削除等の編集不可にしてある、とか、複製の際に一部のフィールドは空にしたりまた一部のフィールドは現在日付を入れたり、とか、サブフォームに挿入したIDを基に、関連する別のテーブルのレコードもそのIDに関連付ける形で複製したい、とか、etc、etc・・・）そのため、教えていただいた方法をそのままパッと適用するというわけにはいかなさそうではありますが、DoCmdについて勉強する価値がありそうですね。
（少なくともDoCmdというクラスの存在を知らなかったので、一通りこのクラスに関して調べてみようと思います）
どうもありがとうございます。

[hatena19]

> サブフォームは追加・削除等の編集不可にしてある

削除の許可、追加の許可プロパティで不可にしているのなら、いったん解除して追加、追加後元に戻すとかすればいいでしょう。
空にしたいフィールドは追加後、Nullを代入すればいいでしょう。

ちなみに、ペーストする場合、オートナンバー型のフィールドは更新対象から外れます。

[na999]

hatena19さん、別案で大変有用な情報を追記いただき、ありがとうございます。
そして、反応が大変遅くなりまして申し訳ありません。
私なりに咀嚼するのにすごく時間がかかってしまいました・・・

何を咀嚼できずにいたのかというと、RecordsetがDAOである、というところにたどり着けず、DBから切り離された単なるフォーム上の表示データの集合かなと思っていたためどうにも理解が進まずにいました。
（JDBCの「ResultSet」という名前が頭にあったので、Recordsetという名前がDAOである、という認識を微塵も持たずにウンウン唸ってました・・・）
hatenaさんのサイトのほうの記事を読んでこれがDAOであると認識し、DAOなのであれば取り扱い方も大きく変わってくるなということで認識を改めた次第です。

ひとまず、教えていただいた方法（RecordsetCloneに対して変更し、Updateをかける）で、無事に目的のことが完全に実現できました。
まだまだ勉強不足なところが多いので、ACCESSでのDAOの使い方からはじまり、Recordset/RecordsetCloneあたり、そしてDoCmdなどについてもう少し勉強してみようと思います。
どうもありがとうございました。

Answer 2

hatena19さんに教えていただいた内容で解決したと思っていましたが、その後他のテーブルのデータもコピーするSQLなどを書き始めた途端、また同じ症状（Recordset/RecordsetCloneがNothingとなる）に遭遇しました。

さらに調査・試行錯誤してみたところ、どうやらトランザクション制御のために取得したWorkspaceを閉じたことが原因だとわかりました。

元々のコード

    Dim ws As DAO.Workspace: Set ws = DBEngine(0)
    Dim db As DAO.Database: Set db = CurrentDb
    ...
    ws.BeginTrans
    ...
    ws.CommitTrans
    db.Close: Set db = Nothing
    ws.Close: Set ws = Nothing

の最後の部分をこんな感じに修正したところ、問題が起きなくなりました。

    ...
    ws.CommitTrans
    db.Close: Set db = Nothing
    Set ws = Nothing

以下がまさにピンポイントな記事ですね。
→　WorkSpaceは閉じなくても問題ないのか

Accessのアプリケーション全体としてプールしているWorkspaceを勝手に閉じてしまったことで、以降のDBアクセスが不可になった、という感じなのでしょうか。