Accessでレコードに連番を振る関数を作りました

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

マイクロソフト・アクセス(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("出席番号","名簿","シメイ")と入力

プロシージャ名に、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からのエラーメッセージ
Accessからのエラーメッセージ

エラー処理の記述をすることで、「必要な引数が入力されていません」など、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:」へ飛ぶよう指示しています。

コメントをどうぞ!

タイトルとURLをコピーしました