検索機能の追加
今回は検索機能を追加していきましょう。
まずは検索項目を何にするか決めましょう。実際に使用する場面を想定してどんな項目で検索する可能性が高いか考えます。ここでは『生徒番号』、『姓かな』、『入学年月日』に絞ってみます。『生徒番号』は完全一致、『姓かな』は前方一致、『入学年月日』は日付の範囲指定とします。更に検索ボタンを追加し、検索項目全体をボックスで囲んでおきます。各検索項目はデータベースと連結しないので、コントロールソースは空白とし、名前を各々付けておきます。
以下が修正した画面レイアウトです。
検索条件
まずは以下のコード例を見て下さい。
Private Sub 検索_Click()
まずSQLを文字列として組み立てる上で変数w_sqlを宣言します。フォームから何も検索条件を入力されなかった場合は全てのレコードを表示する必要があるので、以下の文を記述しておきます。
w_sql = "select * from T_生徒番号 where true=true"
ここで簡単にSQLの説明をしておきます。『select』はテーブルからデータを抽出するという命令です。『*』は全ての項目を対象にするという事です。項目を選んで抽出する場合は項目名をカンマで区切って列記します。『from』の次に取り出したいテーブル名を記述します。『where』以下で検索条件を記述します。実際にはここで様々なバリエーションが出てきます。まずはtrue=trueとし、必ず成り立つことからこの段階では全てのレコードが抽出されることになります。実際にはwhere以下を記述しなかった事と同じになるのですが、この後where条件を追加で組み立てやすくするため、この記述を入れました。
isnullは検索項目がnullかどうかつまり入力があったかどうかを判断するための関数です。その項目に入力があったときだけSQLを追記していき組み立てます。『&』は文字列と文字列の結合を示します。文字列は『"』で囲みます。テーブルの項目がテキスト型の場合はその前後を『'』で囲む必要があります。ちなみに数値型の場合は不要ですし、日付型の場合は『#』で囲みます。例えば生徒番号=1の生徒を検索した場合に実際に生成されるSQLは以下のようになります。
select * from T_生徒番号 where true=true and 生徒番号='1'
姓かなに関しては前方一致なのでワイルド検索文字として『*』を使用します。つまり例えば『こんの*』とすれば姓かなが『こんの』で始まる全てのデータが対象となります。ワイルド検索の場合、演算子として『=』ではなく、『like』を使います。実際のSQLでは以下のようになります。
select * from T_生徒番号 where true=true and 姓かな like 'こんの*'
最後に入学年月日ですが、これは範囲指定をしています。開始だけが入力された場合は終了は問わず、終了だけが入力された場合は開始年月日を問うてません。両方入力された場合はその日付の間に入るデータが対象となります。例えば入学年月日が2010/12/01から2010/12/03の間のデータを抽出した場合のSQLは以下のように生成されます。
select * from T_生徒番号 where true=true and 入学年月日 >= #2010/12/01# and 入学年月日 <= #2010/12/03#
３つの条件は複合条件として組み合わせることも可能です。全て『and』で結合されているので複数の条件が入力された場合はその条件を同時に満たすデータだけが抽出されます。例えば前述の姓かなと入学年月日の条件を指定した場合は以下のようなSQLが生成されます。
select * from T_生徒番号 where true=true and 姓かな like 'こんの*'
条件に合致しない場合は、データが何も表示されません。抽出したデータが複数合った場合の並び順として『order by 生徒番号』とすると、生徒番号の昇順で並び替えられます。
最後の２行を解説しますと、生成されたSQLをフォームのレコードソースプロパティにセットし、『Requery』プロパティで画面を再表示します。
もう少し使い勝手をよくします。新規入力時は検索条件は必要ありませんから、履歴検索ボタンを押したときのみ検索ボックスの中が表示されるようにします。まずはフォームヘッダーの背景色と検索ボックスの背景色を同じ色にしておきます。そして検索ボックスの位置を最前面に移動しておきます。
検索ボックスを最前面に移動
コントロールの背景色を動的に変更するプロパティとして『BackStyle』がありますので、これを前回やったモードの更新後処理のイベントに加えておきます。
Private Sub モード_AfterUpdate()
今回はVBのコード、SQLと内容が濃い回となりましたが、不安な部分はヘルプ等で理解を深めて下さい。次回は生徒番号の採番について考えてみたいと思います。
まずは検索項目を何にするか決めましょう。実際に使用する場面を想定してどんな項目で検索する可能性が高いか考えます。ここでは『生徒番号』、『姓かな』、『入学年月日』に絞ってみます。『生徒番号』は完全一致、『姓かな』は前方一致、『入学年月日』は日付の範囲指定とします。更に検索ボタンを追加し、検索項目全体をボックスで囲んでおきます。各検索項目はデータベースと連結しないので、コントロールソースは空白とし、名前を各々付けておきます。
以下が修正した画面レイアウトです。
検索条件
ここからは検索ボタンを押したときのクリックイベントを記述します。ここまでなるべくコードレスで動くように頑張ってきましたがイベントで動きを制御しようとするとどうしてもコードをある程度書かざるを得ません。なので多少Access Basicの知識なりSQLの知識を覚えて頂く必要があります。それでも最低限で済むように工夫していきたいと思います。
まずは以下のコード例を見て下さい。
Private Sub 検索_Click()
Dim w_sql
w_sql = "select * from T_生徒番号 where true=true"
If Not IsNull(Me!検索生徒番号) Then
w_sql = w_sql & " and 生徒番号='" & Me!検索生徒番号 & "'"
End If
If Not IsNull(Me!検索姓かな) Then
w_sql = w_sql & " and 姓かな like '" & Me!検索姓かな & "*'"
End If
If Not IsNull(Me!検索入学年月日開始) Then
w_sql = w_sql & " and 入学年月日>=#" & Me!検索入学年月日開始 & "#"
End If
If Not IsNull(Me!検索入学年月日終了) Then
w_sql = w_sql & " and 入学年月日<=#" & Me!検索入学年月日終了 & "#"
End If
w_sql = w_sql & " order by 生徒番号"
Me.RecordSource = w_sql
Me.Requery
End Sub
基本的な考え方はフォームから入力された検索条件でSQLを動的に組み立て、フォームのレコードソースを差し替えて再表示するというものです。この手法はACCESSでフォームの内容を入力された条件で動的に変更する場合の常套手段なのでここで完全に理解して下さい。
まずSQLを文字列として組み立てる上で変数w_sqlを宣言します。フォームから何も検索条件を入力されなかった場合は全てのレコードを表示する必要があるので、以下の文を記述しておきます。
w_sql = "select * from T_生徒番号 where true=true"
ここで簡単にSQLの説明をしておきます。『select』はテーブルからデータを抽出するという命令です。『*』は全ての項目を対象にするという事です。項目を選んで抽出する場合は項目名をカンマで区切って列記します。『from』の次に取り出したいテーブル名を記述します。『where』以下で検索条件を記述します。実際にはここで様々なバリエーションが出てきます。まずはtrue=trueとし、必ず成り立つことからこの段階では全てのレコードが抽出されることになります。実際にはwhere以下を記述しなかった事と同じになるのですが、この後where条件を追加で組み立てやすくするため、この記述を入れました。
isnullは検索項目がnullかどうかつまり入力があったかどうかを判断するための関数です。その項目に入力があったときだけSQLを追記していき組み立てます。『&』は文字列と文字列の結合を示します。文字列は『"』で囲みます。テーブルの項目がテキスト型の場合はその前後を『'』で囲む必要があります。ちなみに数値型の場合は不要ですし、日付型の場合は『#』で囲みます。例えば生徒番号=1の生徒を検索した場合に実際に生成されるSQLは以下のようになります。
select * from T_生徒番号 where true=true and 生徒番号='1'
姓かなに関しては前方一致なのでワイルド検索文字として『*』を使用します。つまり例えば『こんの*』とすれば姓かなが『こんの』で始まる全てのデータが対象となります。ワイルド検索の場合、演算子として『=』ではなく、『like』を使います。実際のSQLでは以下のようになります。
select * from T_生徒番号 where true=true and 姓かな like 'こんの*'
最後に入学年月日ですが、これは範囲指定をしています。開始だけが入力された場合は終了は問わず、終了だけが入力された場合は開始年月日を問うてません。両方入力された場合はその日付の間に入るデータが対象となります。例えば入学年月日が2010/12/01から2010/12/03の間のデータを抽出した場合のSQLは以下のように生成されます。
select * from T_生徒番号 where true=true and 入学年月日 >= #2010/12/01# and 入学年月日 <= #2010/12/03#
３つの条件は複合条件として組み合わせることも可能です。全て『and』で結合されているので複数の条件が入力された場合はその条件を同時に満たすデータだけが抽出されます。例えば前述の姓かなと入学年月日の条件を指定した場合は以下のようなSQLが生成されます。
select * from T_生徒番号 where true=true and 姓かな like 'こんの*'
and 入学年月日 >= #2010/12/01# and 入学年月日 <= #2010/12/03#
条件に合致しない場合は、データが何も表示されません。抽出したデータが複数合った場合の並び順として『order by 生徒番号』とすると、生徒番号の昇順で並び替えられます。
最後の２行を解説しますと、生成されたSQLをフォームのレコードソースプロパティにセットし、『Requery』プロパティで画面を再表示します。
もう少し使い勝手をよくします。新規入力時は検索条件は必要ありませんから、履歴検索ボタンを押したときのみ検索ボックスの中が表示されるようにします。まずはフォームヘッダーの背景色と検索ボックスの背景色を同じ色にしておきます。そして検索ボックスの位置を最前面に移動しておきます。
検索ボックスを最前面に移動
コントロールの背景色を動的に変更するプロパティとして『BackStyle』がありますので、これを前回やったモードの更新後処理のイベントに加えておきます。
Private Sub モード_AfterUpdate()
If Me!モード = 1 Then '新規追加
Me.DataEntry = True
Me.AllowAdditions = True
Me!検索ボックス.BackStyle = 1 '普通
Else '履歴検索
Me.DataEntry = False
Me.AllowAdditions = False
Me!検索ボックス.BackStyle = 0 '透明
End If
End Sub
今回はVBのコード、SQLと内容が濃い回となりましたが、不安な部分はヘルプ等で理解を深めて下さい。次回は生徒番号の採番について考えてみたいと思います。
- 関連記事
スポンサーサイト
