Accessコンボボックスについて

Question

●案件テーブル
案件番号　主キー
顧客番号
担当者

●顧客テーブル
顧客番号　主キー

●担当テーブル
担当番号　主キー
担当者
顧客番号

３つのテーブルがあります。
案件テーブルの担当者をコンボボックスにして、選択肢はそれぞれ担当テーブルの該当する顧客のものだけにしたいのですが、どうしたらよいのでしょうか。
また、案件テーブルから案件フォームを作成しており、入力はフォームからするようにしていますが、コンボボックスの設定はテーブルで作っておくのと、フォームで作るの、どちらが良いものなのでしょうか。
（今は全部テーブルで作っています）

よろしくお願いします

Answer 1

案件テーブル の 担当者 と 担当テーブル の 担当番号 がリンクしているとことでよろしいですか。

入力フォームは単票フォームでしょうか、帳票フォームでしょうか。
一応、単票フォームという前提が回答します。帳票フォームの場合はもう少し工夫が必要です。

顧客番号を表するテキストボックスの設定

コントロールソース　顧客番号
名前　txt顧客番号

担当者コンボボックスの設定

名前　cb担当者
コントロールソース　担当者
値集合ソース　SELECT 担当番号, 担当者 FROM 担当テーブル WHERE 顧客番号 = txt顧客番号;
連結列　1
列数　2
列幅　0cm

コンボボックスのフォーカス取得時のイベントプロシージャ

Private Sub cb担当者_Enter()
    Me.cb担当者.Requery
End Sub

フォームのレコード移動時のイベントプロシージャ

Private Sub Form_Current()
    Me.cb担当者.Requery
End Sub

帳票フォームの場合

単票フォームは上記の設定でOKですが、帳票フォームだとリストを制限したときに、他のレコードのデータが表示されない場合があります。その場合は下記のようにしてください。

現状のコンボボックスの上にテキストボックスを配置して、下記のように設定します。

名前　cb2担当者
コントロールソース　担当者
値集合ソース　SELECT 担当番号, 担当者 FROM 担当テーブル;
連結列　1
列数　2
列幅　0cm
タブストップ いいえ

このコンボボックスのフォーカス取得時のイベントプロシージャ

Private Sub cb2担当者_Enter()
    Me.cb担当者.SetFocus
End Sub

以上です。

やっていることは、コンボボックスのリストを制限すると、他のレコードで除外された顧客名が非表示になります。そのため、コンボボックスの上にさらにコンボボックスを重ねて、そのコンボボックスはリストを制限しないようにします。この上のコンボボックスにはフォーカスが来た時には、下のコンボボックスにフォーカスを移動させます。アクティブになったコントロールは前面に浮き上がってきます。

文章では伝わりづらいところがありますので、下記にサンプルファイルを置いておきます。参考にしてください。

帳票フォームでコンボボックスのリストを制限する方法 Microsoft Access 掲示板 - zawazawa

追記

別のスレッドで質問が出ていたので、
顧客番号のテキストボックスの名前とコントロールソースの設定を変える理由を追加しておきます。

コントロールソース　顧客番号
名前　txt顧客番号

コントロールソースには連結テーブルのフィールド名を設定します。
デフォルトではフィールド名と同じ名前になっているので、上記のように名前を変更しておきます。

コンボボックスの値集合ソースで、フォーム上の顧客番号でリストデータを制限（抽出）するときに、下記のようなクエリ（SQL）を設定したとします。

SELECT 担当番号, 担当者 FROM 担当テーブル WHERE 顧客番号 = 顧客番号;

こうすると、この顧客番号はどちらもSQL内の担当テーブルの「顧客番号」ということになるので常にTureになり制限できないことになります。

SELECT 担当番号, 担当者 FROM 担当テーブル WHERE 顧客番号 = txt顧客番号;

とすると、前の「顧客番号」は担当テーブルのフィールド名になり、txt顧客番号 は担当テーブルにはないので、フォームから探すことになり、フォーム上のコントロールの txt顧客番号 を参照することになります。
これでフォーム上の顧客番号の値で、コンボボックスのリストが制限されることになります。

もし、

コントロールソース　顧客番号
名前　顧客番号

というようにコントロールソースと名前が同じときは、下記のようなSQLにすることになります。

SELECT 担当番号, 担当者 FROM 担当テーブル WHERE 顧客番号 = Forms!フォーム名!顧客番号;

これでフィールド名とフォーム上のコントロールとの区別がつきます。

Comments

[cerophan]

ありがとうございます。勝手に簡単にできるものと思い込んでいたので、VBAが必要と知り驚きです。ソースまで教えていただきありがとうございます。しかし、今回のフォームは帳票フォームでした。（関係あると思わず記載漏れでスミマセン）
また、他の方のアドバイスを元に、案件テーブルの担当者を担当番号に変えました。
ネットでも色々検索するのですがやり方が見つかりません。複数のコンボボックスを連動するというものなら見当たります。それを参考にしたら良いのでしょうか…検討がつかずスミマセン。

[hatena19]

そちらの状況が正確にはわからないので、まずは、提示の設定でやってみてください。
帳票フォームでもコンボボックスの設定は同じになりますので、それでカレントレコードの表示がうまくいくかどうか確認してください。
帳票フォームだとカレントレコード以外の表示に不具合が出る場合があるのでその対策が必要になります。それについては、上記の確認ができてから、回答に追記したいと思います。

[cerophan]

帳票でしたが、最初に教えていただいた方法でうまくいきました！まだまだわからないことが多く、実現までに時間がかかってしまい、スミマセン。どうぞ、今後ともよろしくお願いします！

[cerophan]

ごめんなさい！うまく行っていませんでした。1つの案件でコンボボックスを選択すると、他の案件の選択したコンボボックスが消える現象が起こっています。度々ごめんなさい…

[hatena19]

対処法を回答に追記しましたので参照ください。

[cerophan]

ありがとうございます！こちらの方法は、常に表示されることはなく、フォーカスが当たったときだけ該当の担当者が浮かんでくるということですね？

常に表示したい場合は単票で入力させて、帳票はそれを表示するテキストボックスにすればよいのでしょうか?

[cerophan]

なかなか思うようには行かず…単票で入力したものを帳票でテキストボックスで表示しようとしても、担当者番号しか引っ張ってこれませんでした。参照しているクエリに担当者（担当者テーブル）を入れ込むと、担当者を入力したものしか表示されなくなるし、コンボボックスのように、テキストボックスでは連結で引っ張ってくる方法が見当たりませんでした。

[hatena19]

> こちらの方法は、常に表示されることはなく、フォーカスが当たったときだけ該当の担当者が浮かんでくるということですね？

いえ，違います。カレントレコード以外では、上に配置したコンボボックスが表示されるので、担当者名が常に表示されます(リストが制限されていないので)。
フォーカスを取得したコントロールは背面に配置されていても、前面に表示されるので、宣言されたリストで表示される、ということです。
言葉では伝わらないかも知れません。
もし、可能でしたら、下記の掲示板だとファイルをアップロードできますので、そちらの現状のファイルをアップロードしてもらえれば、それを元に修正したものを提示できます。

Microsoft Access 掲示板 - zawazawa
https://zawazawa.jp/ms-access/

[cerophan]

そうだったんですね！では、何かミスってるんですね！修正していただいたほうが早いですよね…本当にお願いしたいところですが、会社のデータで送れそうにありません。
どうしてもできない場合、自宅で再現できないか試してみます！hatena19様には本当に感謝しております。本もクエリ、VBAと沢山買って、ACCESS資格の過去問は全部解けるくらいのレベルになれたと思ったのに実践ではなかなか本も役に立てることができずで…
でも、なかなか初めてにしては良いものができてきたと思います。これも全てhatena19様のおかげです。

[hatena19]

実際のファイルではなく、簡単なサンプルを作成して送付してもらえればいいと思いますが。

[cerophan]

ご説明が大変そうなら自宅パソコンでサンプル作りますので、最後の質問です。現在全部担当者が表示されるようになったのですが、上にのっけたコンボボックスが、選択肢全部表示してしまいます。なぜかわかりますでしょうか
ちなみに、上に乗せるコンボボックスにもWHERE文の同じものをつけたら、訳のわからない状態になりました。

また、このVBAは一部修正しました。あってますか？
Private Sub cb2担当者_Enter()
Me.cb2担当者.SetFocus
End Sub

やっぱりサンプルを作ったほうが良さそうですね…

[hatena19]

そこまでできているなら、あと一歩です。
VBAは、下記です。
Private Sub cb2担当者_Enter()
Me.cb担当者.SetFocus
End Sub
です。

cb2担当者（上のコンボボックス）にフォーカスが来たら、下のコンボボックスにフォーカスを移動させるということです。

回答も間違ってましたので、修正しておきました。

[hatena19]

質問文が削除されてしまっているけど、どうしたのでしょうか？

[cerophan]

バッチリ、うまくいきました！なるほど、理解が足りず、そういうことだったんですね。
作ることに焦って理解を後回しにしていました…通勤中に改めて熟読させていただきます。

[hatena19]

質問文が削除されてしまっているようですので、元に戻しておいてください。
あとから見た人にとって意味不明のスレッドになってしまいますので。

あと、下記にサンプルファイルを置いておきますので参考にしてください。

帳票フォームでコンボボックスのリストを制限する方法 Microsoft Access 掲示板 https://zawazawa.jp/ms-access/topic/200

[cerophan]

ご指摘ありがとうございます。私も消えている気がしていたのですが、私だけそう見えてるのかと思っていました（汗）編集画面が残ってるので戻しておきます。失礼しました。サンプルファイルまでありがとうございました！！

Answer 2

以下参考
コンボボックスにフィールドの値と対応付けられた別の値を表示する - Accessの使い方・操作方法

案件テーブルを担当者ではなく、担当者番号にする本来のリレーションに変更しても、連結列と列幅によって対応（コードは表示せず名称だけをコンボに表示）する事が可能です。

Comments

[cerophan]

ありがとうございます。案件テーブルを担当者ではなく、担当者番号に変更しました。リンク先も一通り読んで連結列のことは理解したつもりですが、顧客番号に連動してコンボボックスの選択肢が絞り込まれる方法がわかりませんでした。どこか読み落としているのでしょうか…

[sazi]

値集合ソースで、画面の顧客番号を担当者テーブルの抽出条件にします。