連番を振ろう!

スポンサーリンク
この記事は約7分で読めます。

マイクロソフト・アクセス(Microsoft・Access)で地味に難しいことの一つが、レコードごとに連番を振ることです。

一応、テーブルビューで上から1、2と入力していけば自動的に連番を入力してくれる機能はありますが、何百、何千とあるレコードに入力するにはしんどいですね。

今日は、VBAでレコードに連番を振ってみたいと思います。

スポンサーリンク

テーブル「名簿」を作成します

まず、テーブルを用意しましょう。テーブル名は、「名簿」とします。

並び替えを行いますので、インデックスを設定しましょう。レコードが大量にある場合、並び替えのスピードに大きな影響があります。

項目番号フィールド名説明データ型最大長インデックス
1出席番号シメイを50音順に並び替え、1から連番を付けます。長整数型はい(重複あり)
2氏名漢字氏名を入力します。短いテキスト(String型)20いいえ
3シメイカナ氏名を入力します。短いテキスト(String型)40はい(重複あり)
スポンサーリンク

サンプルデータはこちら

テーブルが出来上がりましたら、サンプルデータを流し込みましょう。

出席番号氏名シメイ
0磯野 波平イソノ ナミヘイ
0磯野 フネイソノ フネ
0磯野 カツオイソノ カツオ
0磯野 ワカメイソノ ワカメ
0フグ田 マスオフグタ マスオ
0フグ田 サザエフグタ サザエ
0フグ田 タラオフグタ タラオ
0波野 ノリスケナミノ ノリスケ
0波野 タイ子ナミノ タイコ
0波野 イクラナミノ イクラ
0伊佐坂 難物イササカ ナンブツ
0伊佐坂 軽イササカ カル
0伊佐坂 甚六イササカ ジンロク
0伊佐坂 浮江イササカ ウキエ
スポンサーリンク

「名簿」テーブルにデータを貼り付けます

「名簿」テーブルに、データを貼り付けた状態がこちらです。

スポンサーリンク

VBAのコードを実行します

「シメイ」を昇順に並び替え、出席番号を1番から振っていきます。

VBAのコードはこちら。標準モジュールに貼り付けてください。

Sub renban()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim syussekiNO As Long

Set db = CurrentDb
strSQL = "select * from 名簿 order by シメイ"

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

If rst.RecordCount <> 0 Then

    syussekiNO = 1
    
        Do
            rst.Edit
            rst!出席番号 = syussekiNO
            rst.Update
            
            syussekiNO = syussekiNO + 1
            rst.MoveNext
        Loop Until rst.EOF = True
End If
rst.Close

End Sub

このコードを実行した結果が、こちらです。

氏名の読み仮名順に、出席番号を振ることができました。

スポンサーリンク

解説です

「シメイ」を昇順に並び替えを行った結果を取得し、一番上のレコードに1番から番号を入力しています。

まず、コードを実行する前に、変数等を宣言します。

Dim db As DAO.Database

「db」はDao.Databaseであることを宣言しています。DAOについては、過去記事をご覧ください。

Dim rst As DAO.Recordset

「rst」はレコードセットであることを宣言しています。レコードセットとは、今回の例では、氏名の読み仮名順に並び替えを行った結果を格納するところです。

Dim strSQL As String

「strSQL」は、文字列であることを宣言しています。今回の例では、strSQLに「SQL」というデーターベースを操作する言語を格納しています。

Dim syussekiNO As Long

「syussekiNO」は、出席番号として入力する数値を格納しています。

Set db = CurrentDb

「db」はカレント、つまり、今編集しているこのファイルにあるデーターベースを操作対象とします、と設定しています。

strSQL = "select * from 名簿 order by シメイ"

「strSQL」にSQL文と呼ばれる、データーベースを操作する言語を入力しました。

クエリで表現すると、選択クエリになります。「名簿テーブルのレコードを、シメイを昇順にして表示しなさい。」という意味です。

Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

先ほどの「strSQL」で得られる、「シメイを昇順に並び替えた名簿テーブルの結果」を「rst」に格納しました。

後ろにある「 dbOpenDynaset」とは、その結果を選択クエリのように編集できる状態で格納しなさい、という意味です。

他に和休がよく使うものとしては、「dbOpenSnapshot」があり、これはレコードセットを取得した瞬間の状態を読み取り専用として格納しなさい、という意味です

If rst.RecordCount <> 0 Then

エラーの発生を防ぐため、IF構文を使いました。strSQLの結果が0件になるようなものであったら、次以降の処理がエラーになります。

syussekiNO = 1

「syussekiNO」には、初期値である「1」を設定しました。この一文がないと、0からスタートします。

Do

Loop Until rst.EOF = True

レコードセットの最初から最後まで繰り返し処理を行いますので、「DO~LOOP」という繰り返し構文を使用します。

DOからLOOPの間で処理を繰り返します。LOOPまで処理が進んだら、DOに戻って処理が行われます。

ここから抜け出すための条件が、「Until rst.EOF = True」です。

つまり、レコードセットの状態が「EOF」(エンド・オブ・ファイル)、つまり末端の状態になったときに、抜け出すように設定しています。

では、DOからLOOPの間の解説です。

rst.Edit

「rst.edit」は、今からこのレコードセットを編集します、という命令文です。

Accessのテーブルに直接文字などを入力すると、レコードの左端に鉛筆のアイコンが表示されます。これは、レコードを編集している、という状態を表しているのですが、「rst.edit」はまさにこの状態を作っているのです。

rst!出席番号 = syussekiNO

「rst」の「出席番号」フィールドに、変数「syussekiNO」の数値を入力しなさい、という命令です。

rst.Update

入力が終わったので、レコードセットに反映せよ、という命令です。

先ほどの鉛筆のアイコンの例でいうと、レコードの編集が終わり、次のレコードなどにカーソルを動かすと、鉛筆のアイコンが消えます。このときAccessは、画面に表示されている内容をテーブルに書き込んでいるのです。

syussekiNO = syussekiNO + 1

変数「syussekiNO」に次の番号を格納しました。

rst.MoveNext

次のレコードに移りなさい、という命令です。

最終レコードにあるときにこの命令を実行すると、「レコードセットの末端ですよ」と返事があります。これが先に出てきた「EOF」という状態であり、これを使ってLOOPから抜けるきっかけとしているのです。

End If

IF構文はここまでですよ、という意味です。

rst.Close

レコードセットを閉じます、という意味です。これで、「rst」に格納したクエリの結果は消されることになります。

コメントをどうぞ!