DMaxを使った自動採番で複数行コピーにも対応
オートナンバー型ではなく自前で自動採番したい時、フォームの更新前処理でDMax関数を利用して次の連番を取得して代入するという方法はあちこちで紹介されています。
ただ、この方法は、既存レコードの複数レコードを選択してコピー、あるいはエクセル等から複数行コピーして、追加貼り付けをした場合、追加されたレコードはすべて同じ番号になってしまいます。
もし、主キーフィールドだとエラーが出て最初の1件以外は貼り付けに失敗します。
複数レコードを追加貼り付けした場合でも、連番になる方法を紹介します。
難易度:
DMaxを使った自動採番
まずは、よく見かけるコードです。
テーブル名は Tbl1、自動採番する連番フィールド名は「SEQ」とします。
これで、複数レコードの貼り付けで同じ番号になってしまう原因は、
Form_BeforeUpdate で DMax で最大値を取得するとき、クリップボードから貼り付けられたレコードはまだテーブルには保存されていないからです。画面上では複数レコードが貼り付けられた状態ですが、
「○件のレコードを貼り付けようとしています。
貼り付けますか?」
との確認が出て「はい」を選択したときに初めてテーブルに保存されるという仕様なのでしょう。
複数レコード貼り付けに対応した自動採番
考え方としては、
更新前処理で、新規レコードのとき、モジュールレベルの変数に1を加算します。
それをDMax関数の結果に加算して連番に代入します。
レコード移動時で、パブリック変数を 0 にリセットします。
手入力で1件ずつ入力するときは、新規レコードを保存後、新規行に移動するとレコード移動イベントが発生します。
ところが、複数行を追加コピーすると、レコード移動は発生せずに追加されます。最後のレコードが追加された後でみレコード移動イベントが発生します。(Debug.Printをレコード移動イベントに埋め込んで確認しました。)この特性を利用してます。
フォームモジュール
これで追加されたレコードに問題なく連番が付与されます。SEQが主キーでも問題がありません。
グループ毎に連番になるように自動採番
グループのキーフィールドは GroupCD、グループ毎の連番フィールドは GroupSeq とします。1件ずつ入力するなら下記のコードでOKです。
グループ毎自動採番で複数レコード貼り付けに対応する
この場合も当然、複数行を追加貼り付けするとグループ内では同じ番号になってしまいます。
同じグループだけ貼り付けるなら、上記のモジュールレベルの変数でカウントアップする方法でいいのですが、複数のグループが混在しているデータを追加貼り付けする場合は、グループ毎にカウントアップする必要があります。
Dictionary を使ってグループのキーフィールドをキーにして、カウントアップを格納するようにしてみました。
これで複数レコードを追加貼り付けしてもグループ毎に自動採番されます。 GroupCD、GroupSeq で複合インデックスが設定されていてもエラーなく追加されます。
サンプルファイルが下記からダウンロードできます。
FrmAutoNumbering_07.zip (Access 2007-2010 形式 - 32kb)
FrmAutoNumbering.zip (Access 2002-2003 形式 - 28kb)
FrmAutoNumbering_2k.zip (Access 2000 形式 - 28kb)