アクセス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”)」の追加も忘れずに!

 

(はてな君)
頭に0をつけるのってどうやるのかと思ってた。文字列(短いテキスト)にすればいいんだ。

 

(あいちゃん)
数値型だと頭に0が入れられないけど文字列(短いテキスト)なら入れられるので!

 

 

Accessクエリで連番を振る!文字列と数値型には注意!

2018年12月1日

 

まとめ

 

手動で連番を振る方法って知っていると便利です。

実際に色々な場面で使用してみてくださいね!

 

スポンサーリンク