ACCESS 連番を付与する機能（VBAコード公開）
ACCESSでは連番を付与するシーンが多くあります。
もっとも単純に実現するのであればテーブルのデータ型をオートナンバーにすることで連番を付与できます。
ただ、オートナンバーの単純な数値ではなく、特定の文字列と組み合わせたり年度ごとに連番を付与したいケースがあります。
今回は、ACCESSで連番を付与する機能をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
連番を付与する機能の完成形
最初に連番を付与する機能の完成形から示します。
上記のフォームは受注伝票を入力するフォームです。
受注ナンバー欄に5桁の数値が入っています。
この数値が新しいレコードを登録するたびに、自動で1ずつカウントアップして採番されるようになっています。
数値を上げていくだけなら簡単そうに見えますが、実は常に桁数を指定して表示するというのはACCESSのオートナンバーだけではできません。
この受注ナンバーを連番で取得する処理を今回は作成します。
テーブルの準備
まず初めにテーブルを準備します。
今回は、下記のTRN_受注サンプルテーブルを作成しました。
2行目の受注ナンバーに連番を付与していきます。
桁数を固定させたい場合は、受注ナンバーは数値型ではなく短いテキストにすることに注意してください。
数値型にしてしまうと最初の0が表示されません。
フォームの作成
次に、TRN_受注サンプルをレコードソースとしたフォームを準備します。
フォームデザインから作成します。
テーブルのフィールドを並べていき、下記のように作成します。
今回は、受注ナンバー付与というボタンを作成しました。
このボタンを押すと受注ナンバーを連番で付与するようにします。
実際にはこうしたボタンで付与するのではなく、別のテキストボックスに値が入力された後の更新後イベント等で番号を付与することが多いでしょう。
連番を付与するVBAコード
では、連番を付与する機能を作成していきましょう。
受注ナンバー付与ボタンのクリック時イベントにイベントプロシージャを設定します。
ここで設定したイベントプロシージャは以下の通りです。
Private Sub 受注ナンバー付与_ボタン_Click()
If IsNull(DMax("受注ナンバー", "TRN_受注サンプル")) = True Then
Me!受注ナンバー = "00001"
Else
If Len(DMax("受注ナンバー", "TRN_受注サンプル") + 1) = 1 Then
'1桁の場合
Me!受注ナンバー = "0000" & DMax("受注ナンバー", "TRN_受注サンプル") + 1
ElseIf Len(DMax("受注ナンバー", "TRN_受注サンプル") + 1) = 2 Then
'2桁の場合
Me!受注ナンバー = "000" & DMax("受注ナンバー", "TRN_受注サンプル") + 1
ElseIf Len(DMax("受注ナンバー", "TRN_受注サンプル") + 1) = 3 Then
'3桁の場合
Me!受注ナンバー = "00" & DMax("受注ナンバー", "TRN_受注サンプル") + 1
ElseIf Len(DMax("受注ナンバー", "TRN_受注サンプル") + 1) = 4 Then
'4桁の場合
Me!受注ナンバー = "0" & DMax("受注ナンバー", "TRN_受注サンプル") + 1
Else
'5桁の場合
Me!受注ナンバー = DMax("受注ナンバー", "TRN_受注サンプル") + 1
End If
End If
End Sub
それではイベントプロシージャの内容を解説していきます。
まず最初の部分です。
If IsNull(DMax("受注ナンバー", "TRN_受注サンプル")) = True Then
Me!受注ナンバー = "00001"
は、DMax関数でTRN_受注サンプルテーブルの受注ナンバーフィールドの最大値を求めています。
それがNullだった場合、つまりTRN_受注サンプルテーブルに受注ナンバーの入ったレコードがない場合の処理です。
この場合はフォームの受注ナンバーに00001を入力するようにしています。
次に、Else側、つまり受注ナンバーが過去に入力されている場合です。
こちらの方がメインの処理になります。
If Len(DMax("受注ナンバー", "TRN_受注サンプル") + 1) = 1 Then
'1桁の場合
Me!受注ナンバー = "0000" & DMax("受注ナンバー", "TRN_受注サンプル") + 1
普通に考えれば、DMax関数で取得した受注ナンバーの最大値に+1すればよいのですが、これだけでは問題があります。
単純に+1しただけでは、桁数が5桁に足りない場合の「0」が消えてしまうのです。
そこで、5桁に足りない分だけ「0」を足すように桁数ごとに条件を分岐させています。
If文の条件ではLen関数を使って桁数を調べています。
ここでは、DMax関数で取得した最大値に+1した値に対してLen関数で桁数を調べていることに注意してください。
+1する前に桁数を調べてしまうと1の位が9だったときに繰上りが生じて、0が一つ多くなってしまうためです。
そして桁数が1だった場合は、Dmax関数でで取得した受注ナンバーの最大値に+1した値に、テキストで「0000」を付与します。
これと同じ処理を5桁の場合まで条件分岐させて、「0」の数を変えながら作ってあります。
こうすることで常に5桁で連番を表示することが可能です。
テキスト型と数値型
ここまで読んで、ACCESSのデータ型に詳しい方は、受注ナンバーフィールドはテキスト型で作っているのに、数値として+1の処理をして問題ないのか？と疑問に思うかもしれません。
その通りです。
普通に考えるとテキストデータに+1という算術計算をするのはおかしいですよね。
ただ、ACCESSではそこまで厳密にデータ型を運用されていないため、テキスト型でも数値に見えるものに対しては算術処理ができてしまうのです。
ちょっと気持ち悪いかもしれませんが、今回はこの特性を利用して処理を簡略化しました。
本来であればテキストデータから頭の「0」を除外して数値部分のみを取り出し、テキスト⇒数値変換を行って+1処理し、さらにそれを数値⇒テキスト変換をしてから足りない桁数分の「0」を足す、というのが正しい処理です。
以上、連番を付与する機能をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
