前回は、AccessVBAプログラミングでコンボボックスを使ってデータを検索する方法を解説しました。
今回は、さらに応用編としてコンボボックスを3つ組み合わせて検索する方法を解説します。
Accessでデータを検索するためには、選んだワードからSQL文をうまく作り上げられるかがとても重要なポイントです。
検索ワードが一つだけであればシンプルなコードで済むのですが、検索ワードが増えるほど処理は複雑でコードが冗長化します。
本記事を読むことで、フォーム起動直後の設定から検索が完了するまでのプログラムコードの書き方がわかります。
コンボボックスを3つ使っているので、おそらくいろんな検索に対応できるでしょう。
さらに記事内で触れているWhere句を作る方法ですが、定番の方法と配列を使う方法の2つに分けて説明しています。
特に、配列を使ったWhere句の作成方法はあまり一般的ではないかもしれませんが、プログラムコードの部品化をより意識した書き方です。
通常の方法よりも多少時間がかかるかもしれませんが、一度作り上げてしまえばコンボボックスの数に関係なくほぼコードのコピペだけで他の場面でも利用できます。
本記事のサンプルデータベースは、前回の記事と同じものを使用していますが、一応リンクを入れておきますね。
≫ サンプルデータベース(Filter_Example_Combo.accdb)
完成品の動作
サンプルデータベースのフォーム「F01部品マスタM_02_Multi」をクリックして開きましょう。
フォームを開くと、「大分類」「中分類」「小分類」という順番で3つのコンボボックスが並んでいます。
これらの3つのコンボボックスは相互に関連しています。
大分類の「工具」を選択した場合、中分類には大分類「工具」に属するアイテムだけが表示されます。
同様に、中分類を選択すると、その中分類に属する小分類が表示されます。
各分類がどのように関連したのをまとめたのが下の画像です。
大分類から小分類までを選択し、その後検索ボタンをクリックすると検索ワードに該当するデータが表示されます。
いずれかの分類名を空欄にしても正しく検索されます。
また、「検索クリア」ボタンをクリックすると、選択した内容が初期状態に戻ります。
フォームが起動したときに作動するコード
では、フォームを起動したときに作動するコードを確認してみましょう。
Private Sub Form_Load()
Me.RecordSelectors = False ' レコードセレクタを非表示にする
Me.NavigationButtons = False ' 移動ボタンを非表示にする
'分類選択の設定
Me.大分類選択.RowSourceType = "Table/Query"
Me.大分類選択.RowSource = "SELECT 大分類名 FROM T00大分類マスタ;"
Me.大分類選択.Value = ""
Me.中分類選択.RowSourceType = "Table/Query"
Me.中分類選択.RowSource = "SELECT 中分類名 FROM T00中分類マスタ;"
Me.中分類選択.Value = ""
Me.小分類選択.RowSourceType = "Table/Query"
Me.小分類選択.RowSource = "SELECT 小分類名 FROM T00小分類マスタ;"
Me.小分類選択.Value = ""
'データシートフォームの範囲設定
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用;"
End Sub
すべてのコンボボックスとサブフォームをリセットするようになっています。
コンボボックスを操作した直後に作動するコード
コンボボックスを操作したときにも作動するコードも書いておかないといけません。
以下はコンボボックス「大分類選択」と「中分類選択」のワードを選択したら作動するコードです。
Private Sub 大分類選択_BeforeUpdate(Cancel As Integer)
Me.中分類選択.RowSource = "SELECT T1.中分類名 FROM T00大分類マスタ AS T2 INNER JOIN T00中分類マスタ AS T1" & _
" ON T2.大分類ID = T1.大分類ID WHERE T2.大分類名='" & Me.大分類選択.Value & "';"
Me.中分類選択.Value = ""
Me.小分類選択.RowSource = "SELECT T1.小分類名 FROM (T00大分類マスタ AS T3 INNER JOIN T00中分類マスタ AS T2 ON T3.大分類ID = T2.大分類ID) " & _
" INNER JOIN T00小分類マスタ AS T1 ON T2.中分類ID = T1.中分類ID WHERE T3.大分類名='" & Me.大分類選択.Value & "';"
Me.小分類選択.Value = ""
End Sub
Private Sub 中分類選択_BeforeUpdate(Cancel As Integer)
Me.小分類選択.RowSource = "SELECT T1.小分類名, T2.中分類名 FROM T00中分類マスタ AS T2 INNER JOIN T00小分類マスタ As T1 ON T2.中分類ID = T1.中分類ID WHERE T2.中分類名='" & Me.中分類選択.Value & "';"
Me.小分類選択.Value = ""
End Sub
対象のコンボボックスを選ぶと、下位のコンボボックスのRowSourceが再設定されます。
また、下位のコンボボックスの操作後に上位のコンボボックスを操作した場合、下位の選択内容はクリアされます。
検索ボタンを押したときに作動するコード
方法1-定番の方法
Accessフォームで複数のコンボボックスを使った検索は、ANDを組み合わせてWhere句をうまく作れるかがとても重要です。
空欄のコンボボックスを含むパターンも考慮しておかないといけません。
分かりやすいのは基本に忠実にWhere句を作ることですが、コンボボックスが増えるたびにif文のネスト(入れ子）をいくつも重なり、コードの可読性が低下してしまいます。
以下は定番の方法で書いた検索プログラムです。
Private Sub 検索2_Click()
Dim strWhere As String 'Where句を作る文字列型
If Me.大分類選択.Value <> "" Then
If Me.中分類選択.Value <> "" Then
If Me.小分類選択.Value <> "" Then
stWhere = "WHERE 大分類名 = '" & Me.大分類選択.Value & "' AND 中分類名 = '" & Me.中分類選択.Value & "' AND 小分類名 = '" & Me.小分類選択.Value & "';"
Else
stWhere = "WHERE 大分類名 = '" & Me.大分類選択.Value & "' AND 中分類名 = '" & Me.中分類選択.Value & "';"
End If
Else
If Me.小分類選択.Value <> "" Then
stWhere = "WHERE 大分類名 = '" & Me.大分類選択.Value & "' AND 小分類名 = '" & Me.小分類選択.Value & "';"
Else
stWhere = "WHERE 大分類名 = '" & Me.大分類選択.Value & "';"
End If
End If
Else
If Me.中分類選択.Value <> "" Then
If Me.小分類選択.Value <> "" Then
stWhere = "WHERE 中分類名 = '" & Me.中分類選択.Value & "' AND 小分類名 = '" & Me.小分類選択.Value & "';"
Else
stWhere = "WHERE 中分類名 = '" & Me.中分類選択.Value & "';"
End If
Else
If Me.小分類選択.Value <> "" Then
stWhere = "WHERE 小分類名 = '" & Me.小分類選択.Value & "';"
Else
stWhere = ";"
End If
End If
End If
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用" & stWhere
End Sub
上のコードはサンプルデータベースには書いていません。
ネストを３つ重ねると、こんなに解読しづらいコードになるんですね。
コンボボックスが４つになった場合を考えると本当にゾッとします。
しかし、サンプルデータベースは以下のようなシンプルなコードを使っています。
Private Sub 検索_Click()
If Me.小分類選択.Value <> "" Then
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用 WHERE 小分類名 ='" & Me.小分類選択.Value & "';"
ElseIf Me.中分類選択.Value <> "" Then
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用 WHERE 中分類名 ='" & Me.中分類選択.Value & "';"
ElseIf Me.大分類選択.Value <> "" Then
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用 WHERE 大分類名 ='" & Me.大分類選択.Value & "';"
Else
Me.F01部品マスタDS_02_Multi.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用;"
End If
End Sub
それぞれの分類が関係しあっているため、シンプルなコードでも同じ動きを実現できているのです。
ベン図などで情報を整理すれば、よりシンプル書き方が見つかるでしょう。
ただし、マスタ間のデータが全く関連性のない場合は同じ方法が使えないので、コードを部品化して使い回したい人にはあまり向きません。
方法2-配列を使った方法
コードを部品化したい人におすすめしたいのが配列を使った方法です。
フォーム「F01部品マスタM_03_MultiArray」では配列を使っています。
Private Sub 検索_Click()
Dim SearchArray(2, 1) As String 'コンボボックス名と選択分類名を取得する二次元配列
Dim StWhere As String 'SQLのWHERE条件を取得するSQL文
Dim i As Integer, j As Integer
'二次元配列の代入(一次元目)
SearchArray(0, 0) = "大分類"
SearchArray(1, 0) = "中分類"
SearchArray(2, 0) = "小分類"
'二次元配列の代入(二次元目)
For i = LBound(SearchArray, 1) To UBound(SearchArray, 1)
SearchArray(i, 1) = Me.Controls(SearchArray(i, 0) & "選択").Value
Next
'WHERE句を取得
StWhere = ""
j = 0
For i = LBound(SearchArray, 1) To UBound(SearchArray, 1)
If Me.Controls(SearchArray(i, 0) & "選択").Value <> "" Then
If j = 0 Then
StWhere = " WHERE " & SearchArray(i, 0) & "名 = '" & SearchArray(i, 1) & "'"
Else
StWhere = StWhere & " AND " & SearchArray(i, 0) & "名 = '" & SearchArray(i, 1) & "'"
End If
j = j + 1
End If
Next i
'データをフィルタ
Me.F01部品マスタDS_03_MultiArray.Form.RecordSource = "SELECT * FROM Q01部品マスタ表示用 " & StWhere & ";"
End Sub
いくらコンボボックスが増えても入れ子を増やさず、ループだけでWhere文を作ることができます。
サンプルフォームではあえて二次元配列を使用していますので、うまく実務に活用してみてください。
まとめ
今回は、コンボボックスを使って検索する方法の応用編を紹介しました。
あまり他では紹介されていませんが、とても実務的な内容ですので、本記事のコードを活用しコンボボックスを使ったデータ検索を習得しましょう。