マイクロソフトのデータベースAccessのちょっとしたテクニックと注意点などを解説してみたいと思います。
今回は、フォーム画面でコンボボックスを使って、テーブル内のあるフィールドを選択したら、別のフィールドからデータを引っ張ってきて表示させるという機能です。
この機能は、既にマスタデータがあって、あるレコードのフィールド値が選択されたと同時に、別のテキストボックスで確認したい時などに役立ちます。
サンプル準備
「商品マスタ」というテーブルと、「抽出フォーム」と言う空のフォームを準備しました。
商品マスタには、サンプルとして上の図のようなデータを入力しました。
つまり、このサンプルで、
- 第一段の選択で分類を選択
- 第二段の選択で商品名を選択
- 第三段の非連結テキストボックスに値段が表示
という選択方法テクニックをレクチャーします。
フォームにコンボボックス設置
上記のように、空の「抽出フォーム」に、非連結のコンボボックスを設置して、
●コンボボックスの値を別のテーブルまたはクエリから取得する
を選択します。
次に、コンボボックスの値の取得元を、「商品マスタ」に選択し「分類」を選択し他は設定をせずに次へ行きます。
分類をグループ化する
「商品マスタ」テーブルを見ると、分類フィールドの中に、野菜が２個、フルーツが２個、魚が１個存在します。
これをグループ化して、コンボボックスでのドロップダウンを、野菜、フルーツ、魚というふうに重複しているのをまとめます。
コンボボックスのプロパティから、「値集合ソース」の右横にある点をクリックします。
「クロス集計」をクリックすることで、「ID」と「分類」がグループ化されました。「ID」に関しては、コンボボックスで見えなくていいので、削除してください。
※「ID」を使って並び替えをするとかの場合は、消さずに利用する方法もありますが、別の機会にします。
最後に、「選択」をクリック閉じて、再び、コンボボックスのプロパティを見ます。
「ID」を消したので、列数を１にして、列幅も１つに変更します。
デザインモードから、表示モードに変えて、コンボボックスのドロップダウンに、分類が選択できるようになっていることを確認してください。
２段目のコンボボックス設定
１段目のコンボボックスと同じように、コンボボックスを設置しますが、途中で「商品マスタ」から「商品名」を選択します。
※後から変更できるので、適当でも大丈夫です。
１段目と同じように、コンボボックスのプロパティから、「値集合ソース」の右横にある点をクリックします。
「ID」を消して、「分類」を追加して、「抽出条件」の場所で、右クリックして、式ビルダーを起動して、上図のように、フォームから「コンボ５」を選択します。
つまり、これをやることで、
一段目のコンボ５というコンボボックスの値によって、２段目のコンボボックス７の値が抽出されることになります。
※サンプルなので、自動ネーミングされたコンボ〇という感じになっていますが、自由に分かり易い名前に変更してOKです。
また、１段目と同じように、列数、列幅も変更しないと、空白になりますのでご注意ください。
いったん、デザインモードから表示させてちゃんと動くか試してみてください。
初回だけうまく行くが２回目が失敗
表示モードにして試しに動かしてみると、初回だけちゃんと動きますが、２回目から、１段目のコンボボックスを変更しても、２段目が反映されません。
動作としては、１段目を変更した時には、再クエリという処理をかけて、２段目を再抽出させる処理が必要になります。
１段目の「コンボ５」のコンボボックスが、更新された時に再クエリがかかるように、プロパティから、点をクリックして、「マクロビルダー」を選択します。
２つのマクロを設定しました。
●値の代入
このマクロは、コンボ７に残骸データが表示されたままになるので、一段目のコンボボックスが選択された段階で、Nullをいれて空白にしています。
●再クエリ
一段目のコンボボックスが変更された時点で、二段目のコンボボックス（コンボ７）に再クエリがかかって再抽出されます。
※「値の代入」という項目が表示されてない場合は、「すべてのアクションを表示」をクリックすると出現します。
ここまでで、試してみましょう。
三段目の設定
三段目は、選択することは無いので、テキストボックスを使います。
「商品マスタ」から、一段目と二段目の値から「値段」を引っ張ってくる方法になります。
２段目の値を選択したらDlookup関数でテキストボックスに入力
テキストボックスを設置してください。（例では、テキスト９：通常はリネームして分かり易くする）
２段目のコンボ７の値を選択した後にテキストボックスのテキスト９に値を代入する処理を設定しますので、コンボ７のプロパティから更新後処理にて、「イベント プロシージャ」を選択して空のVBAコードを起動します。
メモ
※VBAは極力使わないのですが、どうしても簡略化するために、VBAコードを使う場合があります。
Option Compare Database
Private Sub コンボ7_AfterUpdate()
Me.テキスト9 = DLookup("値段", "商品マスタ", "分類= '" & [Forms]![抽出フォーム]![コンボ5] & "' and 商品名= '" & [Forms]![抽出フォーム]![コンボ7] & "'")
End Sub
分類と商品名を「and」で検索していますが、文字列の場合は、カンマとかダブルカンマなどの違いによってエラーになりますのでご注意ください。
完成
以上で、２段の選択により、３段目の内容を抽出する方法が出来上がりました。
この方法は、いろんな場面で応用が利きますので覚えておくと便利です。
また、チョコっとだけVBAを使った部分がありますが、クエリーを使えば同じことができます。ただ、VBAの方が処理が速いので数行で処理できる内容ならVBAを使った方が楽な場合もあります。
このようなコンボボックスを使って選択した値によって、データを抽出する方法も、方法はこれだけではありません。
Accessは、様々な方法で欲しい機能が実現できるという面があります。