フォームで複数のチェックボックスからの抽出をしたい

質問

Access初心者です（2010使用）
業務でDB作成をしているのですが、チェックボックスで実行可能な内容なのかが分かりません。
ご教授下さい。

＜テーブル＞
マスターT
通し番号（オートナンバー）
製品（テキスト型で内容は１０個の種類があります）
カテゴリー（上と同じ）
素材（上と同じ）
備考1（テキスト型）
備考2（テキスト型）

このテーブル内にある
製品、カテゴリー、素材の3フィールドについて、フォーム上でそれぞれチェックボックスを項目数分作り、チェックしたもののレコードを別クエリで表示させる（チェックは複数可能）
というものが作りたいのです。

以下フォームイメージ

＜製品＞　＜カテゴリ＞　＜素材＞　　
□A　　　　　□1　　　　　　□あ　　　　　　　
□B　　　　　□2　　　　　　□い　　　　　　←＜製品＞＜カテゴリ＞＜素材＞のうちどれか１つでも
□C　　　　　□3　　　　　　□う　　　　　　　　チェックが入ればそのレコードを別表示させたい
□D　　　　　□4　　　　　　□え
□E　　　　　□5　　　　　　□お

この動作がチェックボックスで可能なのか、他に方法があるのか
参考書を当たってみたのですが希望する内容を見つけられませんでした。
不勉強で申し訳ありませんが、お力をお貸しください。

宜しくお願い致します。

（作業者のレベルとしては、コンボボックスで絞り込みを作れる程度です。
マクロ、VBAも初学者レベルです）

回答No.8

piroin654

No7の続き

なお、(4)のところでは、二つの変数にリストボックスの
名前と選択された項目をシンクロさせながら格納して
いますが、この場合、二つの変数ではなく、二次元配列、
ユーザ定義型などいろいろ方法はあります。あるいは
場合によってはテーブルにデータを書き込み、
レコードセットオブジェクトを利用してデータのやりとりを
する場合もあります。

二つの変数には、
リストボックスの名前を取得することにより、テーブルの
フィールドの名前を確定できるので、

　　Right(ctl.Name, Len(ctl.Name) - 3)

というコードを使います。つまり、リストボックス(ctl)の名前(ctl.Name)
の右から(Right)リストボックスの名前の長さから(Len(ctl.Name)　3文字
取り除いた(Len(ctl.Name) - 3)文字列がこれに該当します。
また、

　　ctl.Column(1, varItm)

はリストボックスのコントロールソースにテーブルの
二つのフィールドを指定しているので、データとして
必要なフィールドが二番目にあるので、1を指定しています。
リストボックスの項目の並びのインデックスは0から
始まるので二番目は1ということになります。

コードの終わりあたりの、

　　　　If strFilter <> "" Then
　　　　　　strFilter = strFilter & " OR [" & varNa(j) & "] In (" & varSp(j) & ")"
　　　　Else
　　　　　　strFilter = strFilter & "[" & varNa(j) & "] In (" & varSp(j) & ")"
　　　　End If

は、フィルタをかけるのに必要な、In句を作成するためのコードです。
たとえば、

　　　　[製品] In('A','C') OR [カテゴリ] In('2','5')

のような、文字列の作成のためのコードです。If文で分岐があるのは変数strFilter
が空の場合は、たとえば、上記の例でいうと、ORをくっつけないで、

　　　　[製品] In('A','C')

を格納し、空でない場合は、半角の空白とORをくっつけて

　　　　 OR [カテゴリ] In('2','5')
とし、

　　　　[製品] In('A','C') OR [カテゴリ] In('2','5')

という文字列にしています。

以上です。わからないところがあれば補足してください。

回答No.7

piroin654

長いので、ひょっとしたら二つにわけるかも
しれません。

＞更に質問で申し訳ないのですが、抽出用の選択項目を更に増やす場合（
＞例えば「色：あか　あお　みどり…」）
＞どういう組み合わせを考えればいいのでしょうか？

途中までは、No4～No5までと同じです。
まずは、No4～No5までで作ったフォームを
他の適当な名前で保存してください。
保存したフォームを基に作成します。

(1)
テーブルを一つ追加し、名前を「T色」とします。
フィールドは、
　　　　ID　　　　（オートナンバー）（主キー）
　　　　色　　　　（テキスト型）

(2)
フォームをデザインビューで開き、フォームを横に
広げ、ボタンを右に移動し、空いたスペースに
リストボックスを一つ追加し、名前を「lst色」とします。
プロパティを開き、

　　名前　　　　　　　lst色
　　値集合ソース　　　T色
　　列数　　　　　　　2
　　列幅　　　　　　　0cm;2cm
　　複数選択　　　　　標準

のように設定します。

(3)
フォームをデザインビューで開き、「詳細」の「フォームフッター」
のところにマウスを当て、十字になったら下にぐいっと下げます。
ツールボックスからテキストボックスを選択し、詳細の部分に
貼り付け、右に移動して、他のテキストボックスの並びの
横に配置します。このときラベルは切り取り、リストボックスなどが
ある部分に貼り付け、配置したテキストボックスのところに
他のラブルと同じように配置します。

テキストボックスのプロパティを開き、
　　　名前　　　　　　　　　　　色
　　　コントロールソース　　　　色
とします。
作業が終わったら、フォームフッターの上部にマウスを当て、十字になったら
クリックしたまま上部に押し上げ、テキストボックスに当たるところまで
移動してください。

途中まで同じというのはここまでです。
ここかから本題です。
No4～No5までで作ったフォームの場合は、条件が
3つでした。条件が3つのとき、cmdデータ抽出のクリック時の
イベントに記述したIf文は、８こです。ではもう一つ条件が
加わるとIf文はいくつ必要になるかと言えば、16こです。
条件が5つになると・・・、そんなに書けるわけないし、
どこかで間違うのは目に見えています。そこでこのような
場合は、いろいろな道具を使ってコードを短くすることに
します。

最初からそうすれば、と言われそうですが、これから
述べる方法は見ただけでは何のことやらのような
コードなので、本来はコードの流れがわかりやすい
No4～No5までのコードが一番いいのです。

と、前提はこれくらいにして、フォームの改造をします。
これからはコードの改造なので、ほとんどコピペですみます。

(4)
Private Sub cmdデータ抽出_Click()

End Sub

の間のコードを削除して、以下と取り替えます。

　　Dim strFilter As String
　　Dim strKey As String
　　Dim ctl As Control
　　Dim varItm As Variant
　　Dim strName As String
　　Dim strKeyS As String
　　Dim i As Long
　　Dim j As Long
　　Dim varSp As Variant
　　Dim varNa As Variant

　　strKey = ""
　　varItm = ""
　　strName = ""
　　strKeyS = ""
　　varSp = ""
　　varNa = ""

　　'フォームの詳細より上にあるコントロールを検索
　　For Each ctl In Me.Section(acHeader).Controls
　　　　'初期化
　　　　strKey = ""
　　　　'コントロールの種類がリストボックスであるか確認
　　　　If TypeOf ctl Is ListBox Then
　　　　　　'リストボックスの名前が「lst」で始まるか確認---検索に関係のないリストボックスを排除
　　　　　　If Left(ctl.Name, 3) = "lst" Then
　　　　　　　　'リストボックスの項目が選択されているか確認
　　　　　　　　If ctl.ItemsSelected.Count > 0 Then
　　　　　　　　　　'選択された項目を検索し変数に格納
　　　　　　　　　　For Each varItm In ctl.ItemsSelected
　　　　　　　　　　　　If strKey = "" Then
　　　　　　　　　　　　　　strKey = "'" & ctl.Column(1, varItm) & "'"
　　　　　　　　　　　　Else
　　　　　　　　　　　　　　strKey = strKey & ",'" & ctl.Column(1, varItm) & "'"
　　　　　　　　　　　　End If
　　　　　　　　　　Next varItm

　　　　　　　　　　If strKey <> "" Then
　　　　　　　　　　　　If strName = "" Then
　　　　　　　　　　　　　　strName = strName & Right(ctl.Name, Len(ctl.Name) - 3)
　　　　　　　　　　　　　　strKeyS = strKeyS & strKey
　　　　　　　　　　　　Else
　　　　　　　　　　　　　　strName = strName & ";" & Right(ctl.Name, Len(ctl.Name) - 3)
　　　　　　　　　　　　　　strKeyS = strKeyS & ";" & strKey
　　　　　　　　　　　　End If
　　　　　　　　　　Else
　　　　　　　　　　　　Exit For
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　Next ctl

　　varSp = Split(strKeyS, ";")
　　varNa = Split(strName, ";")
　　For j = 0 To UBound(varNa)
　　　　If strFilter <> "" Then
　　　　　　strFilter = strFilter & " OR [" & varNa(j) & "] In (" & varSp(j) & ")"
　　　　Else
　　　　　　strFilter = strFilter & "[" & varNa(j) & "] In (" & varSp(j) & ")"
　　　　End If
　　Next j

　　If strFilter = "" Then
　　　　Exit Sub
　　Else
　　　　Me.Filter = strFilter
　　　　Me.FilterOn = True
　　End If

(5)
次にいらないコードを削除します。
コード表の最初にある

Dim str製品 As String
Dim strカテゴリ As String
Dim str素材 As String

を削除します。

(6)
次に、以下のコードを削除します。No5の(9)にあるコード
すべてです。

Private Sub cmdMakeStr(ByVal strCtl As String)
　　・
　　・
End Sub

以上です。

[続きます]

回答No.6

piroin654

No4の(6)のところで、

同様に、素材のリストの下のボタンの
プロパティを
　　名前　　　　cmd素材選択解除
　　標題　　　　選択解除
のように設定し、同じようにクリック時の
イベントで、

Private Sub cmd素材選択解除_Click()
Call cmd選択解除("素材")
End Sub

のように設定してください。

が、抜けていました。追加しておいてください。

補足

詳しいご回答ありがとうございました！
思う様な動きが出ました！
コードの読解はおいおいやっていこうと思います。

更に質問で申し訳ないのですが、抽出用の選択項目を更に増やす場合（例えば「色：あか　あお　みどり…」）
どういう組み合わせを考えればいいのでしょうか？
ヒントだけでも教えていただけませんでしょうか？

何度も申し訳ありませんが宜しくお願い致します。

回答No.5

piroin654

「つづき」

(8)
次に、フォームのリストボックスの横の
空いたスペースにボタンを二つ設定します。

一つのボタンのプロパティで、
　　　名前　　　　　cmdデータ抽出
　　　標題　　　　　データ抽出
とし、ボタンのクリック時のイベントで、
以下のように設定してください。

Private Sub cmdデータ抽出_Click()
Dim strSQL As String
Dim strPart As String

Call cmdMakeStr("製品")
Call cmdMakeStr("カテゴリ")
Call cmdMakeStr("素材")

If str製品 = "" And strカテゴリ = "" And str素材 = "" Then
Exit Sub
End If

If str製品 <> "" And strカテゴリ <> "" And str素材 = "" Then
strPart = "[製品] In (" & str製品 & ") OR カテゴリ] In (" & strカテゴリ & ")"
End If

If str製品 <> "" And strカテゴリ = "" And str素材 <> "" Then
strPart = "[製品] In (" & str製品 & ") OR [素材] In (" & str素材 & ")"
End If

If str製品 = "" And strカテゴリ <> "" And str素材 <> "" Then
strPart = "[カテゴリ] In (" & strカテゴリ & ") OR [素材] In (" & str素材 & ")"
End If

If str製品 <> "" And strカテゴリ = "" And str素材 = "" Then
strPart = "[製品] In (" & str製品 & ")"
End If

If str製品 = "" And strカテゴリ <> "" And str素材 = "" Then
strPart = "[カテゴリ] In (" & strカテゴリ & ")"
End If

If str製品 = "" And strカテゴリ = "" And str素材 <> "" Then
strPart = "[素材] In (" & str素材 & ")"
End If

If str製品 <> "" And strカテゴリ <> "" And str素材 <> "" Then
strPart = "[製品] In (" & str製品 & ") OR [カテゴリ] In (" & strカテゴリ & ") OR [素材] In (" & str素材 & ")"
End If

With Me
.Filter = strPart
.FilterOn = True
End With
End Sub

(9)
以下のコードをコード表の空いたところに
貼り付けてください。

Private Sub cmdMakeStr(ByVal strCtl As String)
　　Dim ctl As Control
　　Dim varItm As Variant
　　Dim strKey As String

　　Set ctl = Me.Controls("lst" & strCtl)
　　strKey = ""
　　For Each varItm In ctl.ItemsSelected
　　　　If strKey = "" Then
　　　　　　strKey = "'" & ctl.Column(1, varItm) & "'"
　　　　Else
　　　　　　strKey = strKey & ",'" & ctl.Column(1, varItm) & "'"
　　End If
　　Next varItm

　　If strCtl = "製品" Then
　　　　str製品 = strKey
　　End If
　　If strCtl = "カテゴリ" Then
　　　　strカテゴリ = strKey
　　End If
　　If strCtl = "素材" Then
　　　　str素材 = strKey
　　End If
End Sub

(10)
次に、コード表の一番上のところで、

Option Compare Database
Option Explicit

のようになっているすぐ下にくっつけて、

Dim str製品 As String
Dim strカテゴリ As String
Dim str素材 As String

をはりつけてください。貼り付けると、

Option Compare Database
Option Explicit
Dim str製品 As String
Dim strカテゴリ As String
Dim str素材 As String

のようになります。

(10)
リストボックスの横のもう一つのボタンの
プロパティで、
　　　名前　　　　cmdフィルタ解除
　　　標題　　　　フィルタ解除
とし、ボタンのクリック時のイベントで、

Private Sub cmdフィルタ解除_Click()
Me.FilterOn = False
End Sub

と、設定してください。

以上です。これはフォームでレコードにフィルタを
かけて条件に合うレコードを表示する方法です。
クエリを表示する方法をありますが、方法は
フォームの場合とあまり変わりませんが、SQL文を
コードの中で作成することが少し工程が増加することに
なります。どちらが使い勝手がいいかは一概には
言えませんが。

分からないところがあれば補足してください。

回答No.4

piroin654

少し長いので回答を二つに分けます。

(1)
テーブルを3つ作成します。
　「T製品」
　　ID（オートナンバー)(主キー)
製品（テキスト型）

　「Tカテゴリ」
　　ID（オートナンバー)(主キー)
　　カテゴリ（テキスト型）

　「T素材」
　　ID（オートナンバー)(主キー)
　　素材（テキスト型）

これらのテーブルにそれぞれ10種類の
内容記述してください。

(2)
フォームを作成します。
「マスタT」を基にフォームの新規作成から
「表形式」のフォームを作ります。できたら
一旦、適当な名前で保存します。

(3)
できたフォームをデザインビューで開き、
フォームの「詳細」のところにマウスを
当て、十字になったらクリックしたまま
ずいーっと下に下げます。残ったラベルも
全て選択して詳細の上部にくっつけます。

(4)
あいたスペースに、リストボックスを3つ
設定します。

(5)
1つ目のリストボックスのプロパティを
開き、
　　名前　　　　　　　lst製品
　　値集合ソース　　　T製品
　　列数　　　　　　　2
　　列幅　　　　　　　0cm;2cm
　　複数選択　　　　　標準

に設定してください。そのほかは規定値の
ままでいいです。

2つ目のリストボックスは、
　　名前　　　　　　　lstカテゴリ
　　値集合ソース　　　Tカテゴリ
とし、その他は上記と同様です。

3つ目のリストボックスは、
　　名前　　　　　　　lst素材
　　値集合ソース　　　T素材
とし、その他は上記と同じです。

(6)
それぞれのリストボックスの下にボタンを
一つ設定します。製品のリストの下のボタン
のプロパティを表示して、
　　名前　　　　cmd製品選択解除
　　標題　　　　選択解除
のように、設定し、クリック時のイベントから
コード表を開き、

Private Sub cmd製品選択解除_Click()
Call cmd選択解除("製品")
End Sub

のようにコードを設定してください。

同様に、カテゴリのリストの下のボタンの
プロパティを
　　名前　　　　cmdカテゴリ選択解除
　　標題　　　　選択解除
のように設定し、同じようにクリック時の
イベントで、

Private Sub cmdカテゴリ選択解除_Click()
Call cmd選択解除("カテゴリ")
End Sub

のように設定してください。

(7)
次に、以下のコードをコピーしコード表の空いた
ところに貼り付けてください。

Private Sub cmd選択解除(ByVal strCtl As String)
Dim i As Integer
　With Me.Controls("lst" & strCtl)
　　For i = 0 To .ListCount - 1
　　　　.Selected(i) = False
　　Next i
　End With
End Sub

回答No.3

piroin654

確認ですが、補足をもとに状況を考えてみましたが、
間違っていたら訂正してください。

マスターTには素材、製品、カテゴリの各10種類
を基にして様々な組合せのレコードが存在していて
(すべての組合せがあるとは限らない)、チェックした
項目にあてはまるレコードをマスターTの中から
取り出したい。

ということで、いいのでしょうか？
たとえば、

　　　製品　　　カテゴリ　　　素材
　　　●Ｃ　　　　●3　　　　　○う

のような場合は、製品Cを含むものすべてと、
カテゴリ3を含むものすべて、であって、
製品Cとカテゴリ3を同時に含むもの
ではない、ということでよろしいでしょうか？

回答No.2

piroin654

たとえば、（●はチェックがはいってるものとします)

　　　製品　　　カテゴリ　　　素材
　　　○Ａ　　　　○1　　　　　●あ
　　　○Ｂ　　　　●2　　　　　○い
　　　●Ｃ　　　　●3　　　　　○う
　　　○Ｄ　　　　○4　　　　　○え
　　　○Ｅ　　　　●5　　　　　●お
　　　●Ｆ　　　　○6　　　　　○か

のようになっている場合、
どのレコードをどのように抽出するのですか？

あるいは、

　　　製品　　　カテゴリ　　　素材
　　　○Ａ　　　　○1　　　　　●あ
　　　○Ｂ　　　　●2　　　　　○い
　　　○Ｃ　　　　●3　　　　　○う
　　　○Ｄ　　　　○4　　　　　●え
　　　○Ｅ　　　　●5　　　　　●お
　　　○Ｆ　　　　○6　　　　　○か

のように、製品にチェックがまったくはいっていない
ような場合は、どのように、どのようなデータが抽出
されるのですか？

補足

piroin654さま

依頼主は
製品　　　カテゴリ　　　素材
　　　○Ａ　　　　○1　　　　　●あ
　　　○Ｂ　　　　●2　　　　　○い
　　　●Ｃ　　　　●3　　　　　○う
　　　○Ｄ　　　　○4　　　　　○え
　　　○Ｅ　　　　●5　　　　　●お
　　　●Ｆ　　　　○6　　　　　○か
の場合ですと、
データとして帰って来るのは

・素材に「あ」を含むレコード総て
・カテゴリに「2」を含むレコード総て
・製品に「C」を含むレコード総て
・カテゴリに「５」を含むレコード総て
・素材に「お」を含むレコード総て
・製品に「F」を含むレコード総て

ということをしたいのです。
同様に、
製品　　　カテゴリ　　　素材
　　　○Ａ　　　　○1　　　　　●あ
　　　○Ｂ　　　　●2　　　　　○い
　　　○Ｃ　　　　●3　　　　　○う
　　　○Ｄ　　　　○4　　　　　●え
　　　○Ｅ　　　　●5　　　　　●お
　　　○Ｆ　　　　○6　　　　　○か
の場合ですと

・素材に「あ」を含むレコード総て
・カテゴリに「2」を含むレコード総て
・カテゴリに「3」を含むレコード総て
・素材に「え」を含むレコード総て
・素材に「お」を含むレコード総て
・カテゴリに「5」を含むレコード総て

という風に抽出をするようにしたいのです。

抽出結果はテーブルでもクエリでもどちらでも構わないので、
一覧に出せればと考えています。

ですが、こういった動きのものを作ったことがなく、きっかけだけでもアドバイスいただければと思い
質問させていただきました。

宜しくお願い致します。

回答No.1

NotFound404

＜製品＞　＜カテゴリ＞　＜素材＞　　
□A　　　　　□1　　　　　　□あ　　　　　　　
□B　　　　　□2　　　　　　□い　　　　　　←＜製品＞＜カテゴリ＞＜素材＞のうちどれか１つでも
『どれか１つでも』ということ（OR条件）なので
チェックボックスは一つだけで十分かと思いますけど？
べたな方法はテーブルにYes/Noフィールドを作れば事足ります。

テーブルにフィールドを追加せずに行う方法を hatena さんが
非連結のチェックボックスでレコードを選択する
http://hatenachips.blog34.fc2.com/blog-entry-28.html
で紹介されています。

あと、『正規化』もじっくりと考えられた方が宜しいかと。
古いですけど・・図解入りで分かりやすいかと。。
http://www.microsoft.com/japan/office/previous/xp/suminaka/access/database/database1_3.htm

補足

早速のアドバイスありがとうございます。
言葉足らずで申し訳ありません。
------------------------------
＜製品＞　＜カテゴリ＞　＜素材＞　　
□A　　　　　□1　　　　　　□あ　　　　　　　
□B　　　　　□2　　　　　　□い　　　　　　←＜製品＞＜カテゴリ＞＜素材＞のうちどれか１つでも
『どれか１つでも』ということ（OR条件）なので
チェックボックスは一つだけで十分かと思いますけど？
---------------------------------
ということですが、要は「製品」「カテゴリ」「素材」に入っているデータ全てを検索キーにしてしまいたいということなので、
チェックボックスが全てに付かなくてはいけないのです。
その状態で、どこか一つでもチェックを入れたらその項目が入っているレコードを返したいのです。

正規化についてのサイトはじっくり拝読させていただきます。