ACCESS VBA 桁固定の通し番号を採番する方法（VBAコード公開）

ACCESSでは売上伝票、請求伝票などの伝票を多く作ります。

この際、伝票に通し番号を付与したいと思うこともあるでしょう。
ACCESSの基本機能でオートナンバーで番号を付与することは可能ですが、それだと桁数を固定したり特定の番号体系にすることはできません。

今回はACCESS VBAを使って桁固定の通し番号を採番する方法をご紹介します。

---

こんにちは。
はこにわガジェット （@hakoniwagadget） です。

ACCESSを使った売上管理、顧客管理などのデータベース開発を行っています。
ACCESSは使いこなすために少しスキルが必要なものの、うまく活用すればExcelよりも業務の効率化が図れます。
この記事ではACCESSで実際に作成したフォームやレポートを、その作成方法と共にご紹介していきます。

完成形

最初に完成形から示します。

上記は売上伝票を入力するフォームです。
赤枠で囲った伝票番号は、常に4桁の数値で0001から1ずつカウントアップしていくようにしています。

単純な数値で作ると、「0001」にならずに「1」になってしまいます。
これを4桁固定で作成する方法を見ていきましょう。

機能概要

まず、この機能を実装する際の考え方をご説明します。

「0001」と常に4桁で表示するため、伝票番号のデータ型は数値型ではなくテキスト型にする必要があります。
今回もレコードソースになっているテーブルは「TRN_売上」という名前ですが、この伝票番号フィールドはテキスト型で作成しています。

そのうえで、伝票番号を採番する際に「TRN_売上」テーブルの中で伝票番号の数値が最も大きいレコードを探し、その値に+1していきます。

この+1する際は値を数値として扱う必要があります。そのため、単純に+1しただけだと4桁に戻らないのです。
そのため、+1した数値の桁数を調べ、その結果に応じて不足桁数分「0」を足すことで常に4桁を実現できます。

VBAプログラミング

さて、それでは実際のVBAのコードを見てみましょう。

Dim denpyou_bangou As String

If Me!伝票番号 = "" Or IsNull(Me!伝票番号) = True Then
    
        '伝票番号が未設定の場合のみ採番
        If DCount("*", "TRN_売上") = 0 Or IsNull(DMax("伝票番号", "TRN_売上")) = True Then
        
            '売上伝票がない、もしくは伝票番号が1つも設定されていない場合は0001
            Me!伝票番号 = "0001"
        
        Else
        
            '売上伝票がある場合は既存の伝票番号の最大値に+1する
            denpyou_bangou = DMax("伝票番号", "TRN_売上") + 1
            
            Select Case Len(denpyou_bangou)
            
                Case 1
                
                    '数値部分が1桁の場合
                    Me!伝票番号 = "000" & denpyou_bangou
                
                Case 2
                
                    '数値部分が2桁の場合
                    Me!伝票番号 = "00" & denpyou_bangou
                    
                Case 3
                
                    '数値部分が3桁の場合
                    Me!伝票番号 = "0" & denpyou_bangou
            
                Case 4
                
                    '数値部分が4桁の場合
                    Me!伝票番号 = denpyou_bangou
                    
            End Select
        
        End If
    
End If
 

以下、解説です。

最初の

 If Me!伝票番号 = "" Or IsNull(Me!伝票番号) = True Then
 

では、すでに伝票番号が入力されているか否かを判定しています。
伝票番号がすでに入力されているのに採番プロセスを動かすと、どんどん番号が変わってしまうのでそれを防ぐための条件です。

普通に考えれば =””かIsNullのどちらかだけでよい気がするのですが、どうもユーザーの入力内容によって片方だけではうまく判別できないケースがあるようなので念のため両方の条件を記載するようにしています。

そして、伝票番号が未入力の場合に、

 If DCount("*", "TRN_売上") = 0 Or IsNull(DMax("伝票番号", "TRN_売上")) = True Then
 

でTRN_売上に伝票番号が入力されたレコードがあるか否かを判定します。
これは、最初の1レコード目、もしくはレコードはあるが伝票番号が1つも入力されていない場合に、伝票番号に「0001」を入れるための判定条件です。

「0001」だけはルールで入れるのが難しいのでこの判定を行っています。
そのため、この条件に合致した場合は、

 '売上伝票がない、もしくは伝票番号が1つも設定されていない場合は0001
  Me!伝票番号 = "0001"
 

となります。

逆に、この条件に合致しないElse側は、すでに何らかの伝票番号が入力されているケースです。

 '売上伝票がある場合は既存の伝票番号の最大値に+1する
 denpyou_bangou = DMax("伝票番号", "TRN_売上") + 1
 

この場合は事前に定義した文字列型の変数「denpyou_bangou」にDmax関数を使って「TRN_売上」テーブルの伝票番号の最大値に+1した値を入れます。

本当は伝票番号フィールドはテキスト型なので+1するという処理ができなそうに見えるのですが、この辺がACCESSやVBAのファジーなところでして、テキスト型でも数値に見えるものに対して+1することができてしまいます。

気になる場合はVal関数等でテキストを数値に変換して+1することも可能です。

ただし、このままだと数値計算をしたので最初の「0」が消えてしまっています。
そこで、

 Select Case Len(denpyou_bangou)
 

を使って、「denpyou_bangou」の桁数をLen関数で算出し、その桁数に応じて「0」をテキストで足すという処理をしています。

例えばdenpyou_bangouが1桁であれば、

 Case 1

 '数値部分が1桁の場合
 Me!伝票番号 = "000" & denpyou_bangou
 

となり、「000」を足すことで4桁の値にしています。

この処理を行う際、「+」ではなく「&」を使うことにご注意ください。
「+」をしてしまうと数値的に加算するので値が変わりません。テキストとして付与したい場合は「&」を使用しましょう。

この処理を1桁の場合から4桁の場合まで作成して伝票番号を採番できます。

以上、桁固定の通し番号を採番する方法をご紹介しました。

---

ACCESSを使いこなせば、業務の効率化や自動化が実現できます。
しかし、自分でACCESSを学ぶには時間がない、難しそうで不安、という方も多いでしょう。

そんな時は、ACCESS開発歴20年以上、過去に100以上のACCESSデータベースを開発してきた私（@hakoniwagadget）にお任せください。

ACCESSの新規開発、既存のACCESSの修正、ACCESSの操作レッスンなど様々なサービスをご提供しています。
ご興味のある方は下記のサービス一覧をご覧ください。

最後までお読みいただき、ありがとうございました。