Access Tips サブフォームなしで動的サブフォームを表示

日本語になっていない矛盾したタイトルだが、どうせ「パラドックス」ブログなのだし、大目に見てやってください。😅
テーブルやビュー作成の試行錯誤期間中にクライアントPCではどう見えるのか確認したい場合が多いので、にわか開発者としては大いに役立っている。

クエリオブジェクトをサブフォームの代用にする

名付けて「万能マスタブラウザツール」ではサブフォームをひとつも使っていない。
多数のテーブルやビューの内容を必要に応じて切り替える管理用途のフォームでは、いちいち対応するサブフォームなど作っていられないので、場所だけ確保しておいて選択されたテーブルの内容表示のためのクエリを使い分けるようにしている。

必要に応じてクエリオブジェクトを生成し、ひとつだけのサブフォームコントロールに SourceObject として差し替えているだけのことなのだが、けっこうお気に入りの手抜きワザである。
カラムの数や構成を気にせずにすむし、Accessでは何とデータシートの列幅調整結果も個別に記憶されるのがありがたい。

クエリオブジェクトで動的に切り替わるサブフォームを生成する

コンボボックスからテーブルを選択すると、更新後イベントプロシージャでサブフォームが変更される。

Sub サブフォーム更新()
    SQL = "Select * from " & Me.対象テーブル & Me.抽出条件 & " Limit " & Limit & "," & 抽出数
    DB.QueryDefs(PQ).SQL = SQL           '-- 共通クエリを更新
    Me.共通SUB.SourceObject = "クエリ." & PQ    '-- SourceObject の変数には修飾子が必要
    Limitガイド = Limit
End Sub

Me.共通SUB がフォームデザイン時のサブフォーム名。PQ という定数は”PQ-共通”という文字列にしてある。
Select 文の後半部分はフォームに配置した条件設定用である。省略しても動作する。
Me.抽出条件 はWhere条件入力用のテキストボックス、変数の Limit は 1、抽出数は200を初期値としている。
出来上がるSQL文は次のようになる。指定したテーブルの先頭行から200行を表示させる命令となる。

Select * from 人名簿マスタ Limit 0,200

サブフォームなしで動的切り替え

SQLプロパティを変更した PQ クエリを サブフォームの SourceObject に代入するだけで内容が切り替わる。
このような仕組みにすることで、様々なテーブルデザインに柔軟に対応可能となる。
動的なサブフォーム切り替えをする場合、通常は複数のサブフォームを作成しておいて SourceObject にフォーム名を代入することで常道だが、Access ではこのような手抜きも許されている。
注意点は、サブフォーム名以外を代入する場合に “クエリ.” というオブジェクト名を付加しないとエラー発生で実行出来ないこと。ちなみに“クエリ.” でなはく “テーブル.” にするとテーブルそのものを表示することも可能だが、さすがに素のままのテーブルの状態ではデータ確認には使えないのでクエリ一択にしている。

フォームのコントロールで検索条件を設定する

フィールドリストからカラムを選択

テーブル選択すると検索条件用にカラムをコンボボックスから選択出来る。
カラムリストのための カラムというコンボボックスは 値集合タイプ = 値リスト である。

Private Sub 対象テーブル_AfterUpdate()
    Me.抽出条件 = Null: Limit = 0: Me.検索語 = Null
    Call サブフォーム更新
    PQW = "PQ$-SUB"  '-- 値集合ソース用の使い捨てクエリ名
    Call PTクエリ作成(PQW)
    N = 0
    With DB.QueryDefs(PQW)  '-- フィールドリストを生成
        .SQL = "Select * from " & Me.対象テーブル & " Where false"
        For N = 0 To .Fields.Count - 1
            LIST = LIST & .Fields(N).Name & ";"  '-- フィールドを追加
        Next
    End With
    Me.抽出条件 = Null
    Me.カラム.RowSource = LIST
    Me.カラム = Null
    Me.サブフォーム.SetFocus
End Sub

Where false 条件により、1行もないテーブルセットを生成することが出来る。
生成されたクエリオブジェクトの Fields プロパティを順次取得してセミコロンで連結すればフィールドリストのが出来上がる仕組み。プロシージャが実行されると変数 LIST は次の内容になる。

ID;氏名;氏名（ひらがな）;年齢;生年月日;性別;血液型;メールアドレス;電話番号;携帯電話番号;郵便番号;住所;会社名;クレジットカード;有効期限;マイナンバー;所属ID;

検索語の更新後イベントプロシージャで検索条件を組み立て、抽出条件テキストボックスに転記しているのだが、その手順gは本筋とは外れるし、大した内容ではないのでコードは載せない。
実行ボタンのイベントプロシージャは、サブフォーム更新プロシージャを呼び出すだけで、条件設定内容を反映したパススルークエリが適用される。SQL文がちょっとめんどくさいコードになっていたのはこのためである。

Private Sub 抽出実行_Click()
    Call サブフォーム更新
End Sub

この例では、SQL文の書き換えをしているが、パススルークエリを元にしていても Filter プロパティも普通に機能するので、フォームデザイン時の気分次第で使い分けている。😊
サンプルではサーバーから一度に取得する行数を200行に制限しているので問題なく動作するはず。