Access VBA 複数条件検索フィルタの作り方【検索・排他制御・Excel出力の設計パターン】

FREE ACCESS TEMPLATE VOL.2

Access VBA 複数条件検索フィルタの作り方【検索・排他制御・Excel出力の設計パターン】

こんにちは。ニンジンです🥕

前回、Access VBAで作った「対応履歴管理データベース」を無料テンプレートとして配布しました。今回はその中身のVBAコードを解説する回です。

生成AIにVBAを書かせれば、動くコードはすぐ出てきます。ただ、「なぜその書き方なのか」「他にどんな書き方が壊れやすいのか」を知っているかどうかで、AIの提案を鵜呑みにしていいかどうかの判断力が変わってきます。今回は、実務でそのまま使えるAccess VBAの設計パターンを、実際にハマった失敗も含めて紹介します。

Access VBA 複数条件検索フィルタの作り方(OR/AND・部分一致/完全一致対応)

対応履歴管理データベースの検索フォームでは、対象名・分類・種別・バージョン・環境・発生回数・再現性・内容・対応内容の9項目から、任意の組み合わせでチェックを入れて検索できます。

これを実現しているのが、チェックボックスの状態を見ながらFilterプロパティ用のSQL文字列を動的に組み立てるロジックです。

Do
    If Me!対象名CBox = True Then
        strFilter = strFilter & "[対象名]" & strJyouken & "'*" & strKey & "*' Or "
    End If

    If Me!分類CBox = True Then
        strFilter = strFilter & "[分類]" & strJyouken & "'*" & strKey & "*' Or "
    End If

    '(以下、種別・バージョン・環境・発生回数・再現性・内容・対応内容も同様)

    strFilter = Left(strFilter, Len(strFilter) - 4)  ' 余計な" Or "を削除
Loop

チェックが入っている項目だけOr条件を連結していき、最後に余った Or をLeft関数で切り落とす、という組み立て方です。件数が変わっても(項目を増減しても)同じロジックで対応できるのがポイントです。

「部分一致」と「完全一致」の切り替えは、条件演算子とワイルドカードの有無を切り替えるだけで実現しています。

Select Case Me!検索方法OG
    Case 1
        strJyouken = " Like "   ' 部分一致
    Case 2
        strJyouken = "="        ' 完全一致
End Select

'(中略。フィルタ文字列を組み立てたあと)

If Me!検索方法OG = 2 Then
    strFilter = Replace(strFilter, "*", "")  ' 完全一致ならアスタリスクを除去
End If

一度「部分一致」前提でアスタリスク付きの条件を組み立ててから、完全一致の場合だけあとからアスタリスクを取り除く、という2段構えです。条件演算子だけを先に出し分けるより、コードの重複が少なくなります。

入力ロック(排他制御)をEnumで管理する設計

登録フォームには「閲覧」「追加」「編集」の3つの状態があり、状態に応じて各項目の色(黒/青)とロック状態が切り替わります。

最初に書いたときは、この状態を「追加ボタンの文字色が青かどうか」で判定していました。

' 良くない例：ボタンの見た目を状態の判定基準にしてしまっている
If Me!追加B.ForeColor = RGB(0, 0, 255) Then
    ' 追加モード中の処理
End If

これは動くには動くのですが、「見た目のためのプロパティ」を「ロジックの分岐条件」に使ってしまっているため、あとからボタンのデザインを変えたくなった瞬間に地雷になります。そこで、状態そのものをEnumとして定義し、モジュール変数に持たせる形に直しました。

Private Enum enmEditMode
    emReadOnly
    emAdding
    emEditing
End Enum

Private mEditMode As enmEditMode

If mEditMode = emAdding Then
    ' 追加モード中の処理
End If

見た目(ForeColor)はあくまで見た目として管理し、ロジックの分岐は専用の状態変数で行う。当たり前のようですが、UIの状態をそのまま判定条件に流用してしまうのは、AIが生成するコードでも意外とよく見かけるパターンです。

フィールドのロック/アンロック自体は、対象コントロールをまとめて1つのプロシージャに切り出しています。

Private Sub SetEditableFields(ByVal blnEditable As Boolean)

    Dim lngColor As Long
    lngColor = IIf(blnEditable, RGB(0, 0, 255), RGB(0, 0, 0))

    Dim ctls(10) As Variant
    Set ctls(0) = Me!対象名
    Set ctls(1) = Me!種別CoBox
    '(以下、対象コントロールを順にSetで格納)

    Dim ctl As Variant
    For Each ctl In ctls
        ctl.ForeColor = lngColor
        ctl.Locked = Not blnEditable
    Next ctl

End Sub

もともとは「追加ボタン用」「編集ボタン用」「ロック用」の3箇所に、ほぼ同じロック処理がコピペされていました。1箇所にまとめたことで、対象フィールドを増やしたいときの修正箇所が1つで済むようになっています。

For Eachコンパイルエラーの罠(Array関数の落とし穴)

上のコードで地味にハマったのが、コントロールをまとめて扱う配列の作り方です。最初はこう書いていました。

' 一見動きそうだが、実行時エラー「オブジェクトが必要です」になる
For Each ctl In Array(Me!対象名, Me!種別CoBox, Me!環境CoBox)
    ctl.ForeColor = lngColor
Next ctl

Array(Me!コントロール, ...)という書き方は、コントロールへの参照ではなく、コントロールの既定プロパティ(テキストボックスなら入力値)を配列に格納してしまいます。そのためctl.ForeColor = ...のようにオブジェクトとして扱おうとした瞬間、「オブジェクトが必要です」という実行時エラーになります。

これを直すにはSetで明示的にオブジェクト参照を配列へ格納する必要があるのですが、今度は配列の型宣言でもう一段ハマりました。

' これもコンパイルエラーになる：「For Eachを配列で使用する場合はバリアント型の配列でなければなりません」
Dim ctls(10) As Control
Set ctls(0) = Me!対象名

VBAのFor Eachは、配列に対して使う場合、配列自体だけでなく、受け取る側のループ変数もVariant型でなければならないというルールがあります。最終的に、次の形に落ち着きました。

Dim ctls(10) As Variant     ' 配列はVariant型
Set ctls(0) = Me!対象名     ' Setで明示的に参照を格納

Dim ctl As Variant          ' ループ変数もVariant型
For Each ctl In ctls
    ctl.ForeColor = lngColor
Next ctl

「配列で複数コントロールをまとめて扱いたい」というだけの単純な処理で、3段階のエラーを踏むことになりました。AIが提案してくるコードでも、この手の「動きそうで動かない」パターンはよく出てくるので、実際にコンパイル・実行して確認する習慣が大事だと実感した箇所です。

Access VBAからExcel(.xlsx)へレコードを一括出力する方法

対応履歴管理データベースには、登録されている全レコードをExcelファイルとして出力するボタンがあります。実装はシンプルで、Access標準のTransferSpreadsheetメソッド1行です。

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "T_DataBase", strFilePath, True

第2引数のacSpreadsheetTypeExcel12Xmlでxlsx形式を指定し、最後のTrueで1行目に列名(フィールド名)を出力するかどうかを指定しています。保存先のファイルパスは、次回紹介するファイル選択ダイアログの仕組みを流用し、「名前を付けて保存」用のダイアログから取得しています。

よくある質問

まとめと次回予告

今回紹介した設計パターンは、いずれも「一見動くコードを、あとから壊れにくい形に直した」ものばかりです。生成AIに書かせたコードをそのまま使うのではなく、実際に動かして検証し、必要なら設計を見直す、という工程が今のところ欠かせません。

次回は、まさにその「検証して見直す」を地で行った話です。ファイル選択ダイアログを実装する際、Win32 APIを直接呼び出す方法に挑戦したところ、Accessごとクラッシュする事態になりました。生成AIが提案したコードを鵜呑みにせず、実際に動かして原因を切り分けていった一部始終を紹介します。