マイクロソフト・アクセス（Microsoft・Access）で地味に難しいことの一つが、レコードごとに連番を振ることです。
以前、連番を振る方法を記事にしたのですが、今回はこれを関数化して、より汎用的に使用できるようにしてみました。
テーブル「名簿」を作成します
使用するテーブルは、前回の記事通りですので、そちらをご覧ください。
VBAのコード
VBAのコードはこちら。標準モジュールに貼り付けてください。
Function renban(FieldName As String, TableName As String, SortOrder As String)
On Error GoTo ErrHNDL
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim Narabi As String
Dim LpCNT As String
Narabi = IIf(SortOrder = "", "", " order by " & SortOrder)
Set db = CurrentDb
strSQL = "update " & TableName & " set " & FieldName & "=0"
db.Execute strSQL
strSQL = "select * from " & TableName & Narabi
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
If rst.RecordCount <> 0 Then
LpCNT = 1
Do
rst.Edit
rst.Fields(FieldName) = LpCNT
rst.Update
LpCNT = LpCNT + 1
rst.MoveNext
Loop Until rst.EOF = True
End If
rst.Close
MsgBox "正常終了しました。", vbInformation
ErrHNDL_Exit:
Exit Function
ErrHNDL:
MsgBox Error$, vbCritical
Resume ErrHNDL_Exit
End Function
続いて、この関数を実行するためのマクロを用意します。VBAのコードについては、後ほど記述します。
今回の例では、氏名の読み仮名で並び替えて、1番から連番を振ります。
関数の設定
関数の設定は次の通りです。
renban（“連番を振るフィールド名”,“テーブル名”,“並び替える順番”）
今回の例では、「名簿」テーブルの「出席番号」フィールドに連番を設定します。並び順は氏名の読み仮名なので「シメイ」フィールドを指定します。
renban（“出席番号”,“名簿”,“シメイ”）
マクロで実行するとき
マクロの「プロシージャの実行」を選択します。
プロシージャ名に、renban（”出席番号”,”名簿”,”シメイ”）と入力してください。
保存して、実行します。
正常終了のメッセージが出現したら、処理成功です。
読み仮名で並び替えが完了しました。
VBAで実行するとき
次のコードを実行してください。
Sub test()
Call renban("出席番号", "名簿", "シメイ")
End Sub
並び順を変更できます
先ほどの例では、氏名の読み仮名である「シメイ」フィールドを基準に並び替えを行いましたが、漢字氏名である「氏名」フィールドを基準に並び替えを行いたいと思います。
次のように関数を指定し、実行してください。
renban（“出席番号”,“名簿”,“氏名”）
漢字氏名で並び替えが完了しました。
複数のフィールドで並び替えができます
今回作成した関数「renban」の3つ目の引数（緑字の部分）は、SQL文のORDER BY句を指定するので、カンマで区切ることで複数のフィールドに基づいて並び替えをすることができます。
では、名簿テーブルに、性別フィールドを追加して、性別、氏名の読み仮名順で連番を振ってみたいと思います。
性別フィールドを追加
| 項目番号 | フィールド名 | 説明 | データ型 | 最大長 | インデックス |
|---|---|---|---|---|---|
| 1 | 出席番号 | シメイを50音順に並び替え、1から連番を付けます。 | 長整数型 | － | はい（重複あり） |
| 2 | 氏名 | 漢字氏名を入力します。 | 短いテキスト(String型) | 20 | いいえ |
| 3 | シメイ | カナ氏名を入力します。 | 短いテキスト(String型) | 40 | はい（重複あり） |
| 4 | 性別 | 性別を入力します。1＝男、2=女とします。 | 長整数型 | － | はい（重複あり） |
サンプルデータ
| 出席番号 | 氏名 | シメイ | 性別 |
|---|---|---|---|
| 0 | 磯野 波平 | イソノ ナミヘイ | 1 |
| 0 | 磯野 フネ | イソノ フネ | 2 |
| 0 | 磯野 カツオ | イソノ カツオ | 1 |
| 0 | 磯野 ワカメ | イソノ ワカメ | 2 |
| 0 | フグ田 マスオ | フグタ マスオ | 1 |
| 0 | フグ田 サザエ | フグタ サザエ | 2 |
| 0 | フグ田 タラオ | フグタ タラオ | 1 |
| 0 | 波野 ノリスケ | ナミノ ノリスケ | 1 |
| 0 | 波野 タイ子 | ナミノ タイコ | 2 |
| 0 | 波野 イクラ | ナミノ イクラ | 1 |
| 0 | 伊佐坂 難物 | イササカ ナンブツ | 1 |
| 0 | 伊佐坂 軽 | イササカ カル | 2 |
| 0 | 伊佐坂 甚六 | イササカ ジンロク | 1 |
| 0 | 伊佐坂 浮江 | イササカ ウキエ | 2 |
複数のフィールドを指定
renban（“出席番号”,“名簿”,“性別,シメイ”）
性別、氏名の読み仮名順に並び替えを指定し、実行します。
性別、氏名の読み仮名順に連番を振ることができました。
解説です
では、VBAコードの解説です。今回の例は、「名簿」テーブルを「シメイ」フィールドに収録されている氏名の読み仮名順に並び替えて、「出席番号」フィールドに連番を付していきます。
また、今回のVBAコードは、関数ですのでエラー処理を含めて記述しています。
エラー処理の記述が無いと、デバック画面が表示されてしまいます。
エラー処理の記述をすることで、「必要な引数が入力されていません」など、Accessからのエラーメッセージを表示することができます。
Function renban(FieldName As String, TableName As String, SortOrder As String)
ファンクションプロシージャの記述を宣言します。subでなくfunctionで記述することで、Accessに用意された関数のように、マクロやクエリ内で使用することができます。
- 第1引数 FieldName は、連番を設定するフィールド名を指定します。
- 第2引数 TableName は、テーブル名を指定します。
- 第3引数 SortOrder は、並び替えを指定します。SQL文の ORDER BY句と同様に記述することができます。
On Error GoTo ErrHNDL
エラー処理を行う宣言です。エラーが発生したとき、ErrHNDLへ飛び、そこに記述されているコードを実行します。
Dim db As DAO.Database Dim rst As DAO.Recordset Dim strSQL As String Dim Narabi As String Dim LpCNT As String
関数で使用する、変数の宣言をしています。
Narabi = IIf(SortOrder = "", "", " order by " & SortOrder)
変数Narabiに、関数の第3引数である SortOrder の値を代入します。
IF式を使って、SortOrderの指定があればSQL文の並び替えを意味する「Order By」句と SortOrder の値を連結して代入し、SortOrderの指定が無ければ “”（長さ0の文字列）を代入します。
Set db = CurrentDb
変数dbにこのデーターベースを指定します。
strSQL = "update " & TableName & " set " & FieldName & "=0" db.Execute strSQL
変数strSQLにSQL文を代入します。
関数で指定されたテーブル（今回の例では「名簿」）にある指定されたフィールド（ 今回の例では「出席番号」 ）の値を0に更新しています。
strSQL = "select * from " & TableName & Narabi
変数strSQLにSQL文を代入します。
関数で指定されたテーブル（今回の例では「名簿」）にあるすべてのフィールドを、変数Narabi で指定した並び順で並び替えています。
今回の例では、変数strSQLに代入されたSQL文は、
select * from 名簿 order by シメイ
です。
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
変数 rst に先ほどのSQL文で取得した結果（以下、レコードセットといいます。）を代入しています。
If rst.RecordCount <> 0 Then
レコードセットの件数が、0でなければ次のコードへ進みます。そうでなければ、End If へ飛びます。
LpCNT = 1
変数 LpCNT はループカウンター（繰り返し処理が何回行われているか数える）として扱います。LpCNTに初期値である1を代入しています。
Do
Do ～ Loop ステートメントを使用して、繰り返し処理を行います。DoとLoopの間に記述されたコードを繰り返し実行します。
ここから脱出する記述がなければ、Accessが起動している間中、無限に繰り返されます。
rst.Edit
レコードセットの結果を編集するための宣言です。
rst.Fields(FieldName) = LpCNT
関数の第1引数で指定したフィールドに、ループカウンターであるLpCNTの値を代入します。
rst.Update
レコードセットの編集を終え、保存しています。
LpCNT = LpCNT + 1
変数LpCNTの数を1つ増やしています。
rst.MoveNext
レコードセットの次のレコードに進みます。
Loop Until rst.EOF = True
次のレコードが無い場合、EOF（エンド・オブ・ファイル）は「True」を返してきますので、その状態であれば、Do～Loopを脱出することを記述しています。
End If
レコードセットの件数が0かどうか、での条件分岐をここで終える記述です。
rst.Close
レコードセットを閉じています。
MsgBox "正常終了しました。", vbInformation
処理が正常に終了したことを通知するメッセージボックスです。
ErrHNDL_Exit:
エラーが生じてしまった場合、次の「ErrHNDL:」まで処理が飛びます。
その後、「ErrHNDL:」に記述されたコードを処理し、この「ErrHNDL_Exit:」へ飛んでくるので、その場合もここで処理を終了します。
Exit Function
ここで処理を終了します。
ErrHNDL:
エラーが生じてしまった場合、この「ErrHNDL:」まで処理が飛びます。
MsgBox Error$, vbCritical
メッセージボックスにエラーの内容を表示します。
Resume ErrHNDL_Exit
エラー状態を解消し、「ErrHNDL_Exit:」へ飛ぶよう指示しています。
コメントをどうぞ！