テーブルの任意のフィールドにフィルタ条件を設定してレコードを絞り込むお手本
図4-5-1 社員テーブルの入社日でフィルタ条件を設定して社員を絞り込んだ例
このサンプルデータベースは、フォームに連結しているテーブル／クエリの任意のフィールドにフィルタ条件を設定してレコードを絞り込むことができます。フィルタ条件には、9種類の比較演算子（LIKE, BETWEEN, NOT BETWEEN, =, NOT =, >, <, >=, <=）を指定することができます。フォームヘッダー左端のコンボボックスをクリックすると、フィールドの一覧が表示されますので、絞込みしたいフィールドをクリックします。フィールドを選択すると、自動的に比較演算子のコンボボックスにフォーカスが移動して一覧が表示されます。比較演算子のコンボボックスから、”BETWEEN”または”NOT BETWEN”を選択したときは、値１と値２を入力するテキストボックスが表示されます。”BETWEEN”以外の比較演算子を選択したときは、値１のテキストボックスのみ表示されます。フィルタ実行ボタンをクリックすると、設定した条件でレコードを絞り込みます。フォームの左下に、絞り込まれたレコード件数が表示されます。フィルタ解除ボタンをクリックすると、全件表示します。
サンプルデータベース(CH4-5.mdb)では、以下のノウハウを習得することができます。
◆ フォームのレコードセットプロパティを利用してテーブル／クエリのフィールド名を取得する方法
◆ コンボボックスの値集合ソースを実行時にダイナミックに作成する方法
◆ コンボボックスの一覧を自動的に表示する方法
◆ コントロールの可視プロパティを書き替える方法
◆ 使い勝手の良いフォームを作成する方法
データベース CH4-5.mdbは、Accessに添付されているサンプルデータベース Northwind.mdbを基に作成します。
● テーブル／クエリの任意のフィールドにフィルタ条件を設定してレコードを絞り込むには
１ Accessを起動して作業フォルダに新規データベース CH4-5.mdbを作成します。
２ ファイルメニューから外部データの取り込み、インポートをクリックします。インポートのダイアログが表示されたら、Access のサンプルデータベース Nortwind.mdbを選択してインポートボタンをクリックします。
オブジェクトのインポートダイアログが表示されたら、テーブルタブをクリックして社員テーブルをクリックします。最後に、OKボタンをクリックしてインポートを完了させます。
図4-5-2 Northwind.mdbから社員テーブルを取り込む
３ データベースウィンドウからクエリ
をクリックして、新規作成ボタンをクリックします。クエリの新規作成ダイアログが表示されたら、デザインビューをクリックしてOKボタンをクリックします。テーブルの表示ダイアログから社員をクリックして追加ボタンをクリックします。閉じるのボタンをクリックしてダイアログを閉じます。クエリが表示されたら、社員テーブルから“＊”をダブルクリックして全フィールドを選択します。さらに、社員テーブルから社員コードをダブルクリックしてQEBフィールドに移動させます。社員コードの並べ替えを昇順に設定して、表示のチェックを外して非表示とします。クエリを”qry社員”の名称で保存して閉じます。
図4-5-3 社員テーブルのクエリ作成
４ データベースウィンドウからフォーム
をクリックして新規作成のボタンをクリックします。フォームの新規作成ダイアログが表示されたら、フォームウィザードを選択します。基になるテーブル／クエリのコンボボックスから“qry社員”を選択したら、OKボタンをクリックします。フォームウィザードの指示に従って、図4-5-4のようなフォームを作成します。
図4-5-4 フォームウィザードで社員のフォームを作成
５ フォームヘッダーにコンボボックス２個、テキストボックス２個、ラベル１個、そしてコマンドボタンを３個作成します。左側から１番目のコンボボックスには、テーブル／クエリのフィールド名を表示します。２番目のコンボボックスには、フィルタ条件の比較演算子を表示します。２個のテキストボックスには、値１と値２を入力します。コマンドボタンは、左からフィルタ実行、フィルタ解除、フォームを閉じるの順に並べます。フォームヘダーに作成したコントロールのプロパティは、表4-5-1のように設定します。
表4-5-1 フォームヘッダーに作成したコントロールのプロパティ
|
コントロールの種類 |
プロパティ |
値 |
|
コンボボックス１ |
名前 |
cboFieldName |
|
値集合タイプ |
値リスト |
|
|
値集合ソース |
実行時作成 |
|
|
連結列 |
１ |
|
|
列数 |
２ |
|
|
コンボボックス２ |
名前 |
cboCondition |
|
値集合タイプ |
値リスト |
|
|
値集合ソース |
"LIKE";"BETWEEN";"NOT BETWEEN";"=";"NOT =";">";"<";">=";"<=" |
|
|
連結列 |
１ |
|
|
列数 |
１ |
|
|
テキストボックス１ |
名前 |
txtValue1 |
|
可視 |
いいえ |
|
|
ラベル１ |
名前 |
lblAnd |
|
可視 |
いいえ |
|
|
標題 |
AND |
|
|
テキストボックス２ |
名前 |
txtValue2 |
|
可視 |
いいえ |
|
|
コマンドボタン１ |
名前 |
cmdFilter |
|
コマンドボタン２ |
名前 |
cmdReSet |
|
コマンドボタン３ |
名前 |
cmdExit |
図4-5-5 フォームヘッダーにコンボボックス、テキストボックス、ラベル、コマンドボタンを作成
６ メニューからコード
のアイコンをクリックしてフォームモジュールを表示させたら、CH4-5.mdbを開いて”frm社員”のフォームモジュールをコピーして貼り付けます。フォームモジュールのリストは、リスト4-5-1を参照してください。また、CH4-5.mdbのモジュール basMyLibも必要になりますのでインポートしてください。
７ フォームを開いたら、フィールドのコンボボックスをクリックしてテーブル／クエリのフィールド一覧を表示させます。入社日をクリックすると、比較演算子のコンボボックスにフォーカスが移動しますので”BETWEEN”をクリックします。値１と値２を入力するテキストボックスが表示されますので、1990/04/01, 1991/03/31を入力します。フィルタ実行ボタンをクリックすると、フィルタ条件に該当する社員６人が表示されます。
図4-5-6 社員を入社日で絞り込んだ例
８ レコードの絞込み処理が正常に動作することが確認できたらフォームを”frm社員”の名称で保存して、Access を終了させます。
Sub Form_Load()イベントの処理について
このイベントは、フォームが開かれてデータをロードしたときに実行されます。このイベントでは、フォームのレコードセット(Recordset)プロパティを使用して、フォームに連結されたテーブル／クエリのフィールド名を取得します。取得したフィールド名は、コンボボックスの値集合ソースとして使用します。With Me.Recordset…End Withでは、フォームのレコードセットを使用することを宣言します。For Each fld In .Fields…Nextでは、レコードセットの全てのフィールドを取得してメモリ変数(strRowSource)に格納します。フィールドのデータ型(Type)プロパティを参照して、データ型がOLEオブジェクト(dbLongBinary)なら除外します。（OLEオブジェクト型のフィールドには、フィルタ条件を設定できません。）コンボボックスの値集合ソースの列１にはフィールド名、列２にはデータ型を格納します。列１と列２を分離するには、区切り文字セミコロン（；）を挿入します。Left(strRowSource, Len(strRowSource) - 1)で、最後の余分なセミコロン（；）を除去して、コンボボックスの値集合ソース(RowSource)プロパティに設定します。
Sub cboFieldName_AfterUpdate()イベントの処理について
このイベントは、フィールドのコンボボックスからフィールド名をクリックしたときに実行されます。このイベントでは、SetFocusメソッドでフィルタ条件のコンボボックスにフォーカスを移動させて、Dropdownメソッドで比較演算子の一覧を表示します。
Sub cboCondition_AfterUpdate()イベントの処理について
このイベントは、フィルタ条件のコンボボックスから比較演算子をクリックしたときに実行されます。InStr()関数で、比較演算子に”BETWEEN”が含まれるかどうか調べて値１、値２のテキストボックスとラベルの可視(Visible)プロパティを更新します。比較演算子に”BETWEEN”が含まれるときは、値１、ラベル（AND）、値２の可視プロパティを”はい”に設定します。”BETWEEN”が含まれないときは、値１の可視プロパティを”はい”、ラベル（AND）,値２の可視プロパティを”いいえ”に設定します。最後に、SetFocusメソッドで値１のテキストボックスにフォーカスを移動します。
Sub cmdFilter_Click()イベントの処理について
このイベントは、フィルタ実行のコマンドボタンをクリックしたときに実行されます。このイベントでは、フィルタ条件（SQLのWhere句）を生成して、フォームのフィルタ(Filter)プロパティに設定します。Len()関数で、コンボボックス値の長さを調べて、フィールド名と比較演算子が選択されているか確認します。値が選択されているときは、BuildCriteria()関数を呼び出してSQLのWhere句を生成します。BuildCriteria()関数には引数として、フィールド名、フィールドのデータ型、比較演算子、値１、値２を渡します。値２は、演算子に”BETWEEN”、”NOT BETWEEN”を選択したときのみ使用します。メモリ変数(strCriteria)にフィルタ条件を生成したら、フォームのFilterプロパティに設定して、FilterOnプロパティを”はい”に設定します。最後に、フォームのRequeryメソッドを実行してレコードを再クエリします。
Sub cmdReSet_Click()イベントの処理について
このイベントは、フィルタ解除ボタンをクリックしたときに実行されます。このイベントでは、コントロールの値を”Null”に初期化します。また、フォームのフィルタ条件を解除して全件表示させます。
Function BuildCriteria()関数について
この関数は、引数に指定された内容からSQLのWhere句を生成します。Select Case intType…End Selectでは、フィールドのデータ型を調べて、データ型に対応した処理を行います。データ型が、テキスト、メモのときは、値の前後にダブルクオテーション(“)を付加します。また、値の中に”＊”が含まれるときは、比較演算子を”Like”に切り替えます。データ型が数値のときは、そのまま値をセットします。データ型が日付のときは、日付の前後に”#”を付加します。その他のデータ型のときは、そのまま値をセットします。
引数にテキスト型のフィールドを指定したときの戻り値：
氏名 = “葛西 秋雄”, 氏名 Like “葛西*”、 氏名 Like “*秋*”
引数に日付型のフィールドを指定したときの戻り値：
入社日 Between #1990/04/01# And #1991/03/31#
引数に数値型のフィールドを指定したときの戻り値：
社員番号 = 101, 社員番号 Between 200 And 299
リスト4-5-1 frm社員のフォームモジュール
|
Option Compare Database Option Explicit Private Sub cboCondition_AfterUpdate() With Me If InStr(1, .cboCondition, "BETWEEN", vbTextCompare) > 0 Then .txtValue1.Visible = True .txtValue2.Visible = True .lblAnd.Visible = True Else .txtValue1.Visible = True .txtValue2.Visible = False .lblAnd.Visible = False End If .txtValue1.SetFocus End With End Sub Private Sub cboFieldName_AfterUpdate() Me.cboCondition.SetFocus Me.cboCondition.Dropdown End Sub Private Sub cmdExit_Click() DoCmd.Close End Sub Private Sub cmdFilter_Click() Dim strSQL As String Dim strCriteria As String
With Me If Len(.cboFieldName) <> 0 Then If Len(.cboCondition) <> 0 Then strCriteria = BuildCriteria(.cboFieldName.Column(0), _ .cboFieldName.Column(1), .cboCondition, _ Nz(.txtValue1), Nz(.txtValue2)) End If End If End With With Me .Filter = strCriteria .FilterOn = True .Requery End With End Sub Private Function BuildCriteria(strFieldName As String, _ intType As Integer, _ strCondition As String, _ varValue1 As Variant, _ Optional varValue2 As Variant) As String
Dim fBetween As Boolean Dim fLike As Boolean Dim strCriteria As String Const conQuotes = """"
fBetween = IIf(InStr(strCondition, "BETWEEN") > 0, _ True, False) fLike = IIf(InStr(strCondition, "LIKE") > 0, _ True, False)
strCriteria = strFieldName & " " Select Case intType Case dbText, dbMemo If InStr(1, varValue1, "*") > 0 Then strCriteria = strCriteria _ & " Like " & conQuotes & varValue1 & conQuotes Else If fLike Then strCriteria = strCriteria & " Like " _ & conQuotes & "*" & varValue1 & "*" & conQuotes Else strCriteria = strCriteria & strCondition _ & " " & conQuotes & varValue1 & conQuotes End If End If
Case dbInteger, dbLong, dbCurrency, dbDouble, dbSingle If fBetween Then strCriteria = strCriteria _ & strCondition & " " & varValue1 & " AND " & varValue2 Else strCriteria = strCriteria _ & strCondition & " " & varValue1 End If Case dbDate If fBetween Then strCriteria = strCriteria & strCondition _ & " #" & Format(varValue1, "yyyy/mm/dd") & "# AND #" _ & Format(varValue2, "yyyy/mm/dd") & "#" Else strCriteria = strCriteria _ & strCondition & " #" & Format(varValue1, "yyyy/mm/dd") & "#" End If Case Else strCriteria = strCriteria _ & strCondition & " " & varValue1 End Select BuildCriteria = strCriteria
End Function Private Sub cmdReSet_Click() With Me .cboCondition = vbNullString .cboFieldName = vbNullString .txtValue1 = vbNullString .txtValue2 = vbNullString .txtValue1.Visible = False .lblAnd.Visible = False .txtValue2.Visible = False .Filter = vbNullString .FilterOn = False .Requery End With End Sub Private Sub Form_Load() Dim fld As DAO.Field Dim strRowSource As String
With Me.Recordset For Each fld In .Fields With fld If Not .Type = dbLongBinary Then strRowSource = strRowSource & .Name & ";" & .Type & ";" End If End With Next fld strRowSource = Left(strRowSource, Len(strRowSource) - 1) End With Me.cboFieldName.RowSource = strRowSource End Sub Private Sub Form_Open(Cancel As Integer) Call SetAppTitle_FS("Filter by Field (C) " _ & Year(Date) & " by Akio Kasai") End Sub |
Tip
|
使い勝手の良いユーザーインタフェースにするには frm社員のフォームヘッダーには、フィルタ実行とフィルタ解除ボタンがあります。フィルタ実行ボタンは、フィルタ条件を入力したときのみ使用可能です。フィルタ解除ボタンは、フィルタ実行ボタンをクリックしたときのみ使用可能です。フィルタ実行／解除ボタンを自動的に使用可能／不可の状態に切り替えることができれば、ユーザーフレンドリーなインタフェースになります。以下の手順で、コマンドボタンを自動的に使用可能／不可の状態に切り替える機能を追加します。 １ frm社員をデザインモードで開いたら、フィルタ実行(cmdFilter)とフィルタ解除(cmdReSet)ボタンのプロパティを表示させて、使用可能(Enabed)プロパティを”いいえ”に設定します。 ２ 値１のテキストボックス(txtValue1)のプロパティを表示させたら、更新後処理(AfterUpdate)イベントに=ChangeProperty("cmdFilter")を設定します。 ３ frm社員のフォームモジュールを表示させたら、ChangeProperty()関数を追加します。
４ Sub cmdFilter_Click()イベントの最後に、 Dim varRC As Variant varRC = ChangeProperty("cmdReSet") を追加します。 ５ Sub cmdReSet_Click()イベントの最後に、 Dim varRC As Variant varRC = ChangeProperty() を追加します。 ６ フォームを開いて、フィルタ実行／解除ボタンが自動的に使用可能／不可に切り替わるか確認します。
|
Tip
|
複数のフィールドにフィルタ条件を設定してレコードを絞り込むには サンプルデータベース CH4-5.mdbでは、１個のフィールドに対してのみフィルタ条件を設定してレコードを絞り込むことができます。複数のフィールドにフィルタ条件を設定して検索するには、タブコントロールを作成して各タブにフィルタ条件を設定するコントロールを貼り付けます。また、条件１～条件５に設定したフィルタ条件をOR（または）、AND(かつ)どちらの条件でも検索できるようにすると更に使い易くなります。 OR検索： 条件１ OR 条件2 OR 条件３ OR････ AND検索： 条件１ AND 条件２ AND 条件３ AND････
|
