Accessでサブフォームのコンボボックスをカテゴリーで絞り込む

Accessの勉強メモも書くことにしました。サブフォームに商品を入力するときに商品群が多いと選びにくいのでカテゴリーで絞込を行います。Access2016です。

設定

例としてこんな商品群があって、

別テーブルでカテゴリー情報を持っていたとして、

各コントロールの名称は、こんな感じだとします。

コード

親フォームにある「カテゴリー」というコンボボックスはテーブルに影響を与えるわけじゃないので非連結コントロールです。プロパティシートの「値集合タイプ」を「テーブル/クエリ」にして、「値集合ソース」に

SELECT [商品カテゴリーマスター].[カテゴリーID], [商品カテゴリーマスター].[カテゴリー名] FROM 商品カテゴリーマスター; 

と、書いておきます。

その後、親フォームの「カテゴリークリア」ボタンを右クリックして、「イベントのビルド」から「コードビルダー」を選択してVBE画面を出します。

VBEの左上のプロジェクトウィンドウの「Form_販売データ」がアクティブになっていて、コード画面に以下のコードが出てます。

Option Compare Database
Option Explicit

Private Sub カテゴリークリア_Click()

End Sub

「カテゴリークリア」をクリックした時、というのが既に書かれてます。この中身を、以下のように書きます。ついでに「カテゴリー」コンボボックスが変更されたときの動作も書いちゃいます。

Option Compare Database
Option Explicit

Private Sub カテゴリークリア_Click()
  Me.カテゴリー = ""
  Forms!販売データ!明細データ.Form!商品ID.RowSource = "SELECT 商品ID, 商品名 FROM 商品マスター;"
End Sub

Private Sub カテゴリー_Change()
  Forms!販売データ!明細データ.Form!商品ID.RowSource = "SELECT 商品ID, 商品名 FROM 商品マスター WHERE カテゴリーID = '" & Me.カテゴリー & "';"
End Sub

サブフォーム内の「商品ID」というコンボボックスのソースにSQL文を設定します。クリアしたときは条件なしで全表示、変更された時はコンボボックスの値で商品IDを絞り込みます。

サブフォームコントロール名はサブフォームの名前ではなくサブフォームが埋め込まれているコントロールの名前だということに注意してください。

サブフォームを選択してプロパティシートを見たとき、「その他」タブの「名前」に表示されてるやつです。「ソースオブジェクト」とは違います。

動作

サブフォーム内の「商品ID」コンボボックスを開いてみると、全商品が表示されてます。

親フォームの「カテゴリー」コンボボックスを変更します。

もう一度サブフォーム内の「商品ID」コンボボックスを開くと、「カテゴリー」で絞りこまれています。「カテゴリークリア」ボタンを押すと、また全表示になります。