Accessのサブフォームでコンボボックス選択で値を埋める

Accessの良い点は、Excelと違い可能な限りテーブルにあるデータを使いまわして、再入力の手間を省く事にあります。ExcelではVBAコードをバリバリ使わないとできない事が、Accessは限りなくローコード、またはノーコードでも実現ができたりします。

今回は「サブフォームの商品名を選ぶと、商品テーブルにある他の情報を引っ張り出して情報を埋める」といったことを２パターンで実現します。商品テーブルの例えば単価情報などは手入力など無駄ですから、これを自動で持ってきて、数量と掛けて計算したり、情報を受注明細テーブルに追加したりします。

今回使用するサンプル

• 受注明細 - Accessファイル

起動すると自動的にフォームが1個起動する。テーブル構成は

1. 商品マスタ（商品IDは数値型なので新規追加時は手入力が必要）
2. 会社マスタ（会社情報だけが入ってる。いわゆる顧客データ）
3. 受注明細（商品の売上情報を入れる為のサブフォーム用テーブル）

リレーションシップの構成は以下の図のような感じ。なくても問題はないけれど。

図：簡単なリレーションシップを張っておく

事前準備

今回の肝となるのは、サブフォームのレコードソースになる受注明細クエリ（テーブルではなくクエリを使います）。そして、サブフォームのコンボボックスの2つになります。

受注明細クエリ

受注明細クエリは、受注明細テーブルと商品マスタの2つで作ったクエリです。主なポイントは

1. 商品マスタからは単価だけを取得する
2. 商品IDが選択されると、1.のリレーションにより自動的に商品単価を表示してくれる(商品マスタより引っ張ってくる)
3. 小計として、以下の数式を入れてあり、単価 * 数量を自動計算してくれる（単価が0の時は計算せず）
4. 商品マスタの単価情報を変更すると連動して変わるので、受注明細には単価のフィールドは設けていません。

図：受注明細テーブルの構造

サブフォームのコンボボックス

受注明細クエリのサブフォームに於いて、商品IDにコンボボックスを用意する必要があります。通常は商品マスタのIDと商品名の2つのフィールドを用意しておく所ですが、今回は商品ID, 商品名, 商品説明の3つを入れておきます（後述のVBAの項目で使う為）。

1. 商品IDのパーツをコンボボックスにしておく
2. 列数は3とし、商品名のフィールドだけ表示したいので、列幅は、0cm;8cm;0cmとする
3. 値集合ソースでは、商品マスタから3つのフィールドだけ入れておきます。

図：コンボボックスの設定に注意

コンボボックス選択で値を入れる方法

クエリのみで実現する方法

既にこの段階でもう「クエリのみでコンボボックスを選択すると単価が自動で引っ張ってくる」はもう実現できています。リレーションシップが張られている為、商品IDを選択するとそれに基づいた単価を商品マスタから持ってきて表示してるクエリなので、このような事が実現できます。

また、サブフォームのソースはクエリなので、数量を手入力すると小計が自動で計算されるオマケ付きです。

※よって、受注明細テーブルには単価フィールドを設けていない（あってもそこに値が入るわけじゃないので）

図：リレーションシップのおかげで単価が取れる

前項の受注明細クエリのポイントにもあるように、このパターンの場合単価情報は常に商品マスタの単価を参照しているので、大元の商品マスタを変更してしまうと、過去のデータ全ての単価情報が入れ替わってしまいます。よって、確定した受注明細情報は別テーブルに別途切り離しておくべきでしょう。

但し、連動して変わるので、単価のような計算を要するものではなく、別の商品情報などを対象にしておけば、過去に渡って全データの手動で修正が必要なくなるというメリットもあります（例えば商品のメーカーの所在地が変わった場合、過去に渡って全部書き換えといった事が不要です。マスタだけを編集すればそれでOK)

ちょっとだけVBAを使う方法

クエリだけの場合、クエリで情報を持ってきてるだけなので、商品マスタを変更すると全部の受注明細クエリのデータは書き換わります。しかし、実用上はこれで困ることがおきます。理由は

• 商品単価は永久に不変という事はないという事。
• 単価を変えてしまうと、過去の全データが書き換わることになるので、おかしなデータになってしまいます。そこで、受注明細にその時の単価が入るように入れたい！！

となります。クエリでは既存のフィールドに持ってきた値を入れることはできないので、コンボボックスに対してほんの少しだけVBAを使う必要があります。

今回は単価じゃなく商品マスタの商品説明を受注明細に用意した商品説明フィールドに入れてあげるコードを追加します。以下の手順でコンボボックスにコードを書きます。

1. 受注明細クエリのサブフォームを編集する
2. 商品IDのコンボボックスのプロパティを開く
3. 「更新後処理」の「...」をクリックして、コードビルダーをクリック
4. 以下のようなコードを入れて保存する

今回のコードで、商品IDを変更した後に「商品説明フィールド」にコンボボックスの3列目の値が自動で入るようになります（VBAでは1列目は0から始まるので、2で3列目となります）。

図：商品IDを選択すると商品説明にマスタから値が入る（商品マスタを変更しても影響なし）

今回のコードのおかげでその時のデータが受注明細に入るので、例えば商品マスタの単価を対象にしていた場合、商品単価を変更しても受注明細の単価には影響を及ぼしません。ただし逆に何か過去データ全てを修正する場合には、全部手修正が必要となる。

クエリで実現するパターン、VBAで選択するパターンはケースによって使い分けが必要になります。

関連リンク

• コンボボックスの項目を選択したら、他のフィールドの値が入るようにしたい
• アクセスで、突然「式で型が一致しない」エラーが出た時、確認する事と、対処法。
• テーブルを結合すると編集できないときの対処法