MS Access Tips/Sample and VBA and Blog customize etc...

DMaxを使った自動採番で複数行コピーにも対応

オートナンバー型ではなく自前で自動採番したい時、フォームの更新前処理でDMax関数を利用して次の連番を取得して代入するという方法はあちこちで紹介されています。

ただ、この方法は、既存レコードの複数レコードを選択してコピー、あるいはエクセル等から複数行コピーして、追加貼り付けをした場合、追加されたレコードはすべて同じ番号になってしまいます。
もし、主キーフィールドだとエラーが出て最初の1件以外は貼り付けに失敗します。

複数レコードを追加貼り付けした場合でも、連番になる方法を紹介します。

複数レコード追加貼り付けで自動採番NG

難易度:

DMaxを使った自動採番

まずは、よく見かけるコードです。
テーブル名は Tbl1、自動採番する連番フィールド名は「SEQ」とします。

これで、複数レコードの貼り付けで同じ番号になってしまう原因は、
Form_BeforeUpdate で DMax で最大値を取得するとき、クリップボードから貼り付けられたレコードはまだテーブルには保存されていないからです。画面上では複数レコードが貼り付けられた状態ですが、
「○件のレコードを貼り付けようとしています。
貼り付けますか?」
との確認が出て「はい」を選択したときに初めてテーブルに保存されるという仕様なのでしょう。

複数レコード貼り付けに対応した自動採番

考え方としては、
更新前処理で、新規レコードのとき、モジュールレベルの変数に1を加算します。
それをDMax関数の結果に加算して連番に代入します。

レコード移動時で、パブリック変数を 0 にリセットします。

手入力で1件ずつ入力するときは、新規レコードを保存後、新規行に移動するとレコード移動イベントが発生します。
ところが、複数行を追加コピーすると、レコード移動は発生せずに追加されます。最後のレコードが追加された後でみレコード移動イベントが発生します。(Debug.Printをレコード移動イベントに埋め込んで確認しました。)この特性を利用してます。

フォームモジュール

これで追加されたレコードに問題なく連番が付与されます。SEQが主キーでも問題がありません。

複数レコード追加貼り付けで自動採番OK

グループ毎に連番になるように自動採番

グループのキーフィールドは 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)

10 Comments

ほいっと says..."自動採番と保存ボタンの共存"

いつも読ませて参考にさせて頂いています。

この自動採番の処理では、Form_BeforeUpdateの処理で、Cacnel=trueにしています。
その場合、保存ボタンの同じイベントでの処理が実行されず、両方の機能を共存させることが出きません。
何か良い方法はありますでしょうか。

よろしくお願いします。

2014.11.15 21:20 | URL | #- [edit]
hatena says..."re:自動採番と保存ボタンの共存"

> この自動採番の処理では、Form_BeforeUpdateの処理で、Cacnel=trueにしています。
> その場合、保存ボタンの同じイベントでの処理が実行されず、両方の機能を共存させることが出きません。

このページに掲載しているコードでは、Cacnel=true にしてませんが?


2014.11.16 00:18 | URL | #5uE6dEgY [edit]
ほいっと says...""

ご返信ありがとうございます。
>このページに掲載しているコードでは、Cacnel=true にしてませんが?

ということは、
Form_BeforeUpdate()
 If me.NewRecord then
   DMax関数の処理
 Else
  Cancel = true →保存ボタン処理用
 End if
------------
というようにすれば良いのでしょうか?

【追記】11/16 23:00
すみませんでした。私の説明の仕方がきちんとしてませんで。
つまり、hatenaさんが書かれた保存ボタンと取消ボタンの処理と、このページの自動採番処理を一緒に使いたいのです。
その時に、Form_BeforeUpdateの処理関係がどうもうまくいかないのです。
これでわかりますでしょうか。
よろしくお願いします。

2014.11.16 10:49 | URL | #mQop/nM. [edit]
hatena says...""

> というようにすれば良いのでしょうか?

と言われましても、

> その場合、保存ボタンの同じイベントでの処理が実行されず、両方の機能を共存させることが出きません。

この保存ボタンのイベントに記述されいてるコードや、どのような機能を実現したいのか、
こちらからはまったくわかりませんので、良いのか悪いのか判断しようがありませんが。

現状、どのような機能を実現しようとして、どのようなコードを書いて、どのようにうまくいかないのか、
具体的に説明してください。

2014.11.16 12:14 | URL | #5uE6dEgY [edit]
ほいっと says..."re:[1172/1173] re:自動採番と保存ボタンの共存"

もしかするとお気づきでなかったかもしれせんので、改めて下に書かせていただきます。
【追記】11/16 23:00
すみませんでした。私の説明の仕方がきちんとしてませんで。
つまり、hatenaさんが書かれた「保存ボタンと取消ボタンの処理」と、このページの「自動採番処理」を一緒に使いたいのです。
その時に、Form_BeforeUpdateがらみの処理関係がどうもうまくいかないのです。
ソース自体は、hatenaさんが書かれていますので、それをがっちゅゃんこしたわけです。
これでわかりますでしょうか。
よろしくお願いします。


> ご返信ありがとうございます。
> >このページに掲載しているコードでは、Cacnel=true にしてませんが?
>
> ということは、
> Form_BeforeUpdate()
>  If me.NewRecord then
>    DMax関数の処理
>  Else
>   Cancel = true →保存ボタン処理用
>  End if
> ------------
> というようにすれば良いのでしょうか?
>
> 【追記】11/16 23:00
> すみませんでした。私の説明の仕方がきちんとしてませんで。
> つまり、hatenaさんが書かれた保存ボタンと取消ボタンの処理と、このページの自動採番処理を一緒に使いたいのです。
> その時に、Form_BeforeUpdateの処理関係がどうもうまくいかないのです。
> これでわかりますでしょうか。
> よろしくお願いします。
>

2014.11.17 08:06 | URL | #mQop/nM. [edit]
hatena says..."re:自動採番と保存ボタンの共存"

マウスホイールでレコード移動しないようにする - hatena chips
http://hatenachips.blog34.fc2.com/blog-entry-162.html

上記の記事の保存ボタンのコードを採用しているということでしょうか。

このコードは、アクセスの通常の操作による更新・追加を抑制して、保存ボタンを押したときのみレコードの更新や追加ができるようにするのが目的のものです。

このページのコードは、通常の操作による追加(追加貼り付けも含む)のときに自動採番するものです。

そもそも、目的が背反するものです。

Form_BeforeUpdate()
 If me.NewRecord then
   DMax関数の処理
 Else
  Cancel = true →保存ボタン処理用
 End if

とすれば、既存レコードの編集のときは保存ボタンのみで更新可能、新規レコードの追加のときは通常の操作でも更新可能になります。これでは、操作の統一性が損なわれると思いますが、そのことはどうお考えでしょうか。

もう少し、どのような仕様にしたいのか、整理して、操作に矛盾がなく統一性のあるものを検討する必要があると思います。

2014.11.17 09:47 | URL | #5uE6dEgY [edit]
ほいっと says..."re:[1176/1177] re:自動採番と保存ボタンの共存"

ご指摘していただき、ありがとうございます。
私の考えもまとまっていなかったところがありました。

もう一度、構成を良く考え、以下のようにしてみました。
こういう流れだとどうでしょうか。
Private Sub cmd保存_Click()

□入力チェック処理実行

BeforeUpdate = ""

If Me.NewRecord Then
flgNewRec = True

□No = 主キーの最大値の取得処理

AddNum = AddNum + 1
Me.ID = No + AddNum
End If

DoCmd.RunCommand acCmdSaveRecord
BeforeUpdate = "[イベント プロシージャ]"

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Ret As Integer

If Me.Dirty = True Then
Cancel = True
End If

End Sub

よろしくお願いします。

2014.11.17 12:28 | URL | #mQop/nM. [edit]
hatena says..."re:自動採番と保存ボタンの共存"

実際にそのコードで、希望の動作になればいいし、
希望通りにならないのなら、どこが希望と違うのか、
明確にして、それを解決するにはどうするかと、
考えをすすめていけばいいと思います。

2014.11.18 08:25 | URL | #5uE6dEgY [edit]
ほいっと says..."[1181] re:自動採番と保存ボタンの共存"

ありがとうございます。

早速試してみます。もし、うまく行かない時には、具体的にどうしたら良いのか考えながら、作業してみたいです。

いつもありがとうございます。

2014.11.18 22:40 | URL | #mQop/nM. [edit]
ほいっと says..."オートナンバーが続けてあがる"

お世話になります。
この保存ボタン実行ではなく、取消ボタンで何回も取消ボタンを押し続けると、新規作成する時にオートナンバーが取り消した回数分加算された数字から始まるようになりました。
何故でしょうか?

2014.11.21 15:46 | URL | #mQop/nM. [edit]

Leave a reply






Trackbacks

trackback URL
https://hatenachips.blog.fc2.com/tb.php/383-38a086af
該当の記事は見つかりませんでした。