ACCESSで管理するデータには連番を付与することが良くあります。
この際、連番の番号を年度が変わったら1から新たに振り直したいというケースがあります。
今回は、年度毎に連番を付与する機能をご紹介します。
こんにちは。
はこにわガジェット （@hakoniwagadget） です。
ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。
完成形
最初に完成形をお見せします。
このフォームでは、顧客名を入力すると自動的に受注番号が入力されます。
受注番号は、年度+連番という構成になっており、年度が変わると連番の数値が変わるようにしてあります。
このように、年度ごとに連番を付与する機能の作り方を見ていきましょう。
テーブルの準備
今回の機能を作成するにあたり、以下のテーブルを準備しました。
フォームはこのテーブルをもとに作成しています。
テーブルをデザインビューで見てみましょう。
受注番号を一番下に数値型で作成しています。
受注年度と年度内連番はフォーム上は表示する必要が無い項目ですが、途中の計算結果を保持するために作成したフィールドです。
フォームの準備
次にフォームを準備します。
先ほど作成したテーブルをレコードソースとしてフォームを作成します。
フォームにはいったんテーブルにあるフィールドをすべて表示していますが、受注年度と年度内連番は途中計算用なので非表示にしても構いません。
VBAプログラミング
では、連番を取得するプロシージャをVBAで作成しましょう。
顧客名の更新後処理にイベントプロシージャを設定します。
プロシージャの内容は以下の通りです。
Private Sub 顧客名_AfterUpdate()
If Me!受注番号 = 0 Or IsNull(Me!受注番号) = True Then
If Month(Me!受注日) <= 3 Then
'3月以前であれば年度は年に-1
Me!受注年度 = Year(Me!受注日) - 1
Else
'4月以降であれば年度は年
Me!受注年度 = Year(Me!受注日)
End If
'年度内連番をカウントアップ（年度内はじめての場合は1）
Me!年度内連番 = Nz(DMax("年度内連番", "test_受注", "受注年度= " & Me!受注年度)) + 1
'受注年度と年度内連番を組み合わせて受注番号を生成
Me!受注番号 = Me!受注年度 & Me!年度内連番
End If
End Sub
解説します。
まず最初の
If Me!受注番号 = 0 Or IsNull(Me!受注番号) = True Then
で受注番号が既に入力されているか否かを判定しています。
受注番号を上書きしてはいけないので、受注番号が既定値である0か空白になっている場合に番号の生成処理を行います。
次に、年度の判定をしています。
ここでは、3月を年度の区切りとして計算しています。
Month(Me!受注日)で受注日の月の値を取り出し、それが3以下なら実際の年よりも年度の数値が1少なく、4以上なら実際の年と年度が同一と計算し、Me!受注年度に結果を入力します。
次に、以下で年度内連番をカウントアップします。
Me!年度内連番 = Nz(DMax("年度内連番", "test_受注", "受注年度= " & Me!受注年度)) + 1
DMax関数を使って、test_受注テーブルの中で先ほど計算した受注年度と同一受注年度のレコードの中から、年度内連番が最大の値を抽出し、+1した上でフォームの年度内連番に入力します。
これによって年度を識別した上で連番の値を+1できます。
最後に受注年度と年度内連番から受注番号を生成します。
Me!受注番号 = Me!受注年度 & Me!年度内連番
このプロセスで年度ごとに受注番号をカウントアップして入力することができます。
以上、年度毎に連番を付与する機能をご紹介しました。
ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。
そんな時は、ACCESS開発歴20年以上、過去に300以上のACCESSデータベースの開発・修正実績のあるはこにわガジェット（@hakoniwagadget）にお任せください。
ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。
最後までお読みいただき、ありがとうございました。
