アクセスVBAでオートナンバーのように連番を振る方法

アクセス担当のまみです。

オートナンバーって便利ですね。自動的に番号を振ってくれる。

データ検索やデータ修正の時には大活躍のオートナンバーです。

とっても便利なオートナンバーなんだけど、例えば1000から始めたいとか、頭に0をつけたいとか（00005みたいな）そんな時には使えないんです・・・

そんな時にはVBAでコードを書いてあげるとオートナンバーのように手動で連番が振れちゃいます。

今回はその方法をお伝えしますね。

[quads id=1]

Contents

オートナンバーの活用

テーブルを作成すると、自動的にフィールド名「ID」、データ型「オートナンバー」って作られます。

必要に応じてフィールド名を変えたり、削除したりして使用します。

オートナンバーってどうして必要なのかわからない

そうだね。最初はどう使うのかわからないかもね。

商品マスターの商品コードや社員マスターの社員番号のように、1つのレコードを特定できるフィールドがある場合は、オートナンバーを使う必要がないんです。

商品コードや社員番号って同じ番号が存在しないので、レコードの特定ができるんですね。

商品マスター	社員マスター
商品コード	社員番号
商品名	氏名

それから、テーブルやクエリの作成のみの簡易なデーターベースしか使わないような場合もオートナンバーを使う必要がないです。

テーブルに存在していても実際には使われていない感じですね。

オートナンバーが大活躍するのは、フォームを作成して、データの検索や修正を行うときです。

スーパーのレジをイメージしてもらうとわかりやすいでしょうか。

はてな君がコーヒーとパンを買いました。

でも店員さんが間違えて紅茶って入力してしまいました。

今間違えた紅茶を修正したいんだけど、他にも紅茶を買った人はいるし。

この時に、番号が振られていると、特定できるんです。

番号	商品	数量
55	紅茶	1
74	紅茶	5
85	紅茶	3
91	紅茶	1

紅茶で検索すると4件あるし、紅茶と数量1でも2件ある。

91って番号があることでレコードの特定ができる。

その番号を手動で振ってみましょうってことです。

手動で連番を振る

では新規にファイルを「連番」で作成しましょう。ファイルの作成方法は下記の記事をどうぞ！

アクセスの使い方！ファイルを開く方法と保存の仕方

2018年9月1日

テーブル名は「T_連番」にして、デザインビューで開きましょう。

「ID」は使用しないので削除し、下記のとおりテーブルを設定しましょう。

フィールド名	データ型
連番	数値型
好きな番号から連番	数値型
頭に0をつける	短いテキスト

面白いフィールド名だねぇ。

実際にはそんなフィールド名にしないだろうけど、これからやろうとしてることがわかりやすいでしょ！

次は、フォームにテキストボックス3つとボタンを配置しましょう。

位置はお任せします。詳細は下記の記事をどうぞ！

ボタンの配置方法はこちら！
テキストボックスの配置方法はこちら！

標題と名前は下記のとおりそろえておきましょう。

ラベルの名前	テキストボックスの名前
連番	tx連番
好きな番号	tx好きな番号
頭に0	tx頭

ボタンの標題	ボタンの名前
登録	btn登録

それから、3つのテキストボックスはデータ入力用ではないので、さわれないようにしておきましょう。

①3つのテキストボックスを選択した状態で、プロパティーシートの②「データ」タブから③編集ロックで「はい」を選択します。

これで準備は整いました。

さっそく連番を振ってみましょう。

[quads id=2]

1からの連番

まずは、フォームの読み込み時にコードを追加しましょう。

①■フォームを選択した状態で、プロパティーシートの②「イベント」タブを選択し、読み込み時を「イベントプロシージャ」にしてコードの画面を開きます。

「Private Sub Form_Load()」と「End Sub」の間に下記のコードを追加しましょう。

    If DCount("連番", "T_連番") = 0 Then
        Me.tx連番.Value = "1"
    Else
        Me.tx連番.Value = Format(DMax("連番", "T_連番") + 1)
    End If

コードをコピーされたい場合は下記の記事を参考にしてください。

コードのコピー

Dcoutって前にも出てきた！えっと「T_連番」テーブルにある「連番」の数を数えてくれるんでしょ！

その通り！「連番」の数が0つまりデータがないときには1を表示してねってことです

DMaxって「T_連番」テーブルの「連番」の中で一番大きい値を教えてくれます。

一番大きい値を教えてくれるので、その値に1を足してあげれば良いってことです。

では、フォームには「F_連番」と名前を付けて一度閉じて、表示を確かめてみましょう。

あっ1が表示された！

では、登録を左クリックしたらテーブルにデータが登録されるようにしていきましょう。

①「btn登録」を選択した状態で、プロパティーシートの②「イベント」タブを選択し、クリック時を「イベントプロシージャ」にしてコードの画面を開きます。

「Private Sub btn登録_Click()」と「End Sub」の間に下記のコードを追加しましょう。

    Dim Rst As DAO.Recordset
    Set Rst = CurrentDb.OpenRecordset("T_連番", dbOpenTable)
    
    With Rst
        .AddNew
        .Fields("連番") = Me.tx連番.Value
        .Fields("好きな番号から連番") = Me.tx好きな番号.Value
        .Fields("頭に0をつける") = Me.tx頭.Value
        .Update
    End With

    Rst.Close
    Set Rst = Nothing
        
    MsgBox "データが登録されました"
    Me.tx連番.Value = Format(DMax("連番", "T_連番") + 1)

データの登録についての詳細は下記の記事にゆずりますね。

【Access】vbaでフォームからレコード追加しよう

2018年9月24日

データ登録後に Me.tx連番.Value = Format(DMax(“連番”, “T_連番”) + 1)を入れることでカウントが1つずつ増えていきます。

フォームビューで登録して確認してみてくださいね！

すご～い！登録したら自動で連番が振られていくよ～！

1000から連番

次は1000から始まるようにしてみましょう！

フォームの読み込み時に下記のコードを追加します。

    If DCount("好きな番号から連番", "T_連番") = 0 Then
        Me.tx好きな番号.Value = "1000"
    Else
        Me.tx好きな番号.Value = Format(DMax("好きな番号から連番", "T_連番") + 1)
    End If

データの登録後に「Me.tx好きな番号.Value = Format(DMax(“好きな番号から連番”, “T_連番”) + 1)」の追加も忘れずに！

フォームビューで動作確認してみると！

へぇ～「1000」から始まるようにできるんだね！

頭に0をつける

最後に頭に0をつける方法をお伝えしますね。

頭に0をつける場合には、データ型を短いテキストにしておきます。

追加するコードは下記のとおりです。

    If DCount("頭に0をつける", "T_連番") = 0 Then
        Me.tx頭.Value = "0001"
    Else
        Me.tx頭.Value = Format(DMax("頭に0をつける", "T_連番") + 1, "0000")
    End If

データ登録後に「Me.tx頭.Value = Format(DMax(“頭に0をつける”, “T_連番”) + 1, “0000”)」の追加も忘れずに！