Access – レコードソースを設定せずテキストボックスに入力した文字列によってSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する

前回、前々回に、アクセスのフォームやレポートでレコードソースをプロパティに設定せず、VBAからSQL文によってレコードソースを指定してデータを取得する方法の概要をまとめてみた。

「Access – レコードソースを設定せずVBAで動的にテーブルのレコードを取得する」
https://matsumotoyoshio.wordpress.com/2011/12/25/access-%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%82%bd%e3%83%bc%e3%82%b9%e3%82%92%e8%a8%ad%e5%ae%9a%e3%81%9b%e3%81%9avba%e3%81%a7%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab%e3%81%ae%e3%83%ac%e3%82%b3/

「Access – レコードソースを設定せずSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する」
https://matsumotoyoshio.wordpress.com/2011/12/25/access-%e3%83%ac%e3%82%b3%e3%83%bc%e3%83%89%e3%82%bd%e3%83%bc%e3%82%b9%e3%82%92%e8%a8%ad%e5%ae%9a%e3%81%9b%e3%81%9asql%e6%96%87%e3%81%a7%e3%83%87%e3%83%bc%e3%82%bf%e3%82%92%e7%b5%9e%e3%82%8a/

今回はVBAによってフォームのテキストボックスに入力した文字列によってSQL文でデータを絞り込みVBAで動的にテーブルのレコードを取得する方法をまとめてみる。

テーブルのサンプルデータは、テーブル構造は前回までの説明で使ったものと同じだが、少しデータを増やした以下のようなものを使う。

<Fig 1 : 絞り込み検索をするテストデータ>

検索するためのフォームは、新しい空白のフォームを作成し、フォームヘッダーを作成する。フォームヘッダーにはテキストボックスをひとつ、ボタンを2つ作成する。テキストボックスは非連結で、名前を「検索氏名」としておく。ボタンの表題には、それぞれ「全文一致」と「部分一致」とする。

詳細にはデータ表示のためのテキストボックスを3つ作成する。フォームのプロパティにはレコードソースを設定しないが、それぞれのテキストボックスのプロパティでコントロールソースには各フィールド、すなわち「学籍番号」、「氏名」、「性別」を記述しておく。

<Fig 2 : データを検索して表示するフォームをデザインする>

フォームのデザインができたら、ボタンのイベントハンドラにVBAコードを書こう。コードを書くにはボタンを選択してプロパティシートの「イベント」タブで「クリック時」のイベントを「・・・」をクリックする。「ビルダーの選択」ウィンドウが表示されたら「コードビルダー」を選択して「OK」をクリックする。

「全文一致」ボタンのイベントハンドラには次のコードを記述する。

———————————————————————————

Me.RecordSource = "select * from 生徒１ where 氏名 = ‘" & 検索氏名 & "’"

———————————————————————————

ここで記述するSQL文は、select * from 生徒１ where 氏名 = （「検索氏名」テキストボックスのテキスト）、であるが、「検索氏名」テキストボックスの記述方法は、ダブルクォーテーションで囲まれたSQL文の外に「&」演算子で接続する。検索氏名の実態はテキストなのでシングルクォーテーションで囲む。したがって「&」で挟まれる「検索氏名」テキストボックスの記述の前後は、シングルクォーテーションとダブルクォーテーションで挟まれることになる。

次に「部分一致」ボタンのイベントハンドラには次のコードを記述する。

———————————————————————————

Me.RecordSource = "select * from 生徒１ where 氏名 like ‘*" & 検索氏名 & "*’"

———————————————————————————

ここで記述するSQL文は、select * from 生徒１ where 氏名 like *（「検索氏名」テキストボックスのテキスト）*、である。「検索氏名」に入力された文字列の前後を「*」で囲み、文字列を含む指定にする。上と同様に「検索氏名」テキストボックスの記述方法は、ダブルクォーテーションで囲まれたSQL文の外に「&」演算子で接続するので、「&」で挟まれる「検索氏名」テキストボックスの記述の前後は、シングルクォーテーションとアステリスク、そしてダブルクォーテーションで挟まれることになる。

VBAコードの全文は次のようになる。

———————————————————————————

Private Sub コマンド1_Click()
    Me.RecordSource = "select * from 生徒１ where 氏名 = ‘" & 検索氏名 & "’"
End Sub

Private Sub コマンド2_Click()
    Me.RecordSource = "select * from 生徒１ where 氏名 like ‘*" & 検索氏名 & "*’"
End Sub

———————————————————————————

<Fig 3 : ボタンのイベントハンドラにVBAでSQL文を記述する>

コードを記述したらフォームを開いてみよう。フォームが開いた時点ではレコードソースが指定されていないので、データは何も表示されず、テキストボックスは「#Name?」となってエラー表示になる。

「検索氏名」に文字列を入力し、「全文一致」か「部分一致」のボタンをクリックする。たとえば「織田 信長」のようにフルネームを入力し「全文一致」ボタンかまたは「部分一致」ボタンをクリックすると「織田 信長」のレコードだけが表示される。「徳川」のように氏名の一部だけ入力したときは、「全文一致」では何も表示されず、「部分一致」のボタンをクリックしたときに、部分一致したデータが表示される。

<Fig 4 : フォーム「生徒フォーム」を開いてもデータは何も表示されない>

<Fig 5 : 「検索氏名」テキストボックスに「織田 信長」と入力して「全文一致」または「部分一致」ボタンをクリックした>

<Fig 6 : 「検索氏名」テキストボックスに「徳川」と入力して「全文一致」ボタンをクリックしてもデータは表示されない>

<Fig 7 : 「検索氏名」テキストボックスに「徳川」と入力して「部分一致」ボタンをクリックして3件のレコードが表示された>