日常~おまけ雑記~

日常にほんの少し活力を~毎日の出来事やオーディオなど趣味のことを紹介するブログです



accessフォームのデータ検索をVBAで作る方法|クエリを使わずに抽出する|マイクロソフトアクセス

accessのテーブルデータを、フォームのVBAだけで「複数条件の検索・抽出」する方法です。

ここでは、フォームにコンボボックスを3つ作成して、複数条件に合致するデータだけを抽出します。

また、抽出するデータ型を3種類用意し、記述方法も記載しました。

f:id:takalogpoint:20180423164251j:plain 記事の内容MicroSoft ACCESS 2013を使用しています

 

「作成フォーム」

f:id:takalogpoint:20180930114741j:plain

 

■使用するテーブルの解説

例として下記のようなテーブルを作成します。

テーブル名は「TJ_生産実績」としました。

また、フォームのコンボボックスで検索する項目は、「生産日」、「商品名」、「ロットNO」の3項目とします。

「ID」、「生産数」は表示のみとします。

フィールド名と、データ型、書式設定も確認ください。

f:id:takalogpoint:20180930114957j:plain

 

■テーブルに入力したデータ

テーブルには下記のようなデータを入れてあります。

「生産日」は日付(S)型で、2018/09/01~04

「商品名」は長いテキスト型で、AAA,ABB,ABC の3種類

「ロットNO」は長整数型で、生産日をyyyymmddにした数値

f:id:takalogpoint:20180930115109j:plain

 

■フォームの作成とコンボボックスの追加

フォームウィザードで、フォームを新規作成し、コンボボックスを3つ追加しました。

コンボボックスの名前は下記とします。

「生産日cmb」・・・生産日検索用

「商品名cmb」・・・商品名検索用

「ロットNOcmb」・・・ロットNO検索用

 

f:id:takalogpoint:20180930115210j:plain

 

■コンボボックスの値集合ソース設定

各コンボボックスの値集合ソースクエリを示します。

データは「TJ_生産実績」テーブルを元に、項目をグループ化して作成します。

なお、「生産日」と「ロットNO」は最新日付、NOが上に来るように降順で並べ替えしています。


 

「生産日cmb」

f:id:takalogpoint:20180930115258j:plain


 

「商品名cmb」

f:id:takalogpoint:20180930115342j:plain


 

「ロットNOcmb」

f:id:takalogpoint:20180930115436j:plain

 

■あとはフォームのVBAコードを書き込むだけ

各コンボボックスの「更新後処理」(AfterUpdate)には、「kensaku」というSubプロシージャを呼び出します。

 

---フォームのVBAコード全文---

Option Compare Database

Private Sub ロットNOcmb_AfterUpdate()

Call kensaku

End Sub

 

Private Sub 商品名cmb_AfterUpdate()

Call kensaku

End Sub

 

Private Sub 生産日cmb_AfterUpdate()

Call kensaku

End Sub

 

Public Sub kensaku()

Dim strwork As String

strwork = ""

 

If Me.生産日cmb = "" Or IsNull(Me.生産日cmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "生産日 =" & "#" & Me.生産日cmb & "# "

End If

 

If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "商品名 =" & "'" & Me.商品名cmb & "' "

End If

 

If Me.ロットNOcmb = "" Or IsNull(Me.ロットNOcmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "ロットNO =" & Me.ロットNOcmb & " "

End If

 

If strwork = "" Then

Me.RecordSource = "SELECT * FROM TJ_生産実績 ORDER BY ID"

Else

Me.RecordSource = "SELECT * FROM TJ_生産実績 Where " & strwork & "ORDER BY ID"

End If

End Sub

---コードここまで---------------------------------

 

■解説① コンボボックス

まずは前半のフォームで、コンボボックスの値を選択・指定した場合の更新後処理内容をみてみます。

Call kensaku・・・ここではkensakuというプロシージャを呼び出して終了です。

「Call」は省略可能ですが、わかりやすいように記述してあるだけです。

 

'「ロットNOcmbの更新後処理」

Private Sub ロットNOcmb_AfterUpdate()

Call kensaku

End Sub

 

'「商品名cmbの更新後処理」

Private Sub 商品名cmb_AfterUpdate()

Call kensaku

End Sub

 

'「生産日cmbの更新後処理」

Private Sub 生産日cmb_AfterUpdate()

Call kensaku

End Sub

 

■解説② 検索パターン

kensakuプロシージャでの3つのデータ型の検索パターンについて。

 

'パターン① 日付型の抽出

'コンボボックスが空白、Nullの時は何もしない。

'strworkの内容が空値でない場合、AND+空白を代入します。

'ただし最初の検索項目のみAND追加の行は無くてもかまいません。

'strworkに日付の検索条件を代入します。

'このとき日付データの前後に#を入れます。また、後ろの#の後には空白があります。

 

If Me.生産日cmb = "" Or IsNull(Me.生産日cmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "生産日 =" & "#" & Me.生産日cmb & "# "

End If

 

'パターン② テキスト型の抽出

'コンボボックスが空白、Nullの時は何もしない。

'strworkの内容が空値でない場合、AND+空白を代入します。

'strworkにテキストデータの検索条件を代入します。

'このときテキストデータの前後に'を入れます。また、後ろの'の後には空白があります。

 

If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "商品名 =" & "'" & Me.商品名cmb & "' "

End If

 

'パターン③ 数値型の抽出

'コンボボックスが空白、Nullの時は何もしない。

'strworkの内容が空値でない場合、AND+空白を代入します。

'strworkにテキストデータの検索条件を代入します。

'数値型の抽出は、数値のみでOKです。ただし、やはり行の最後には空白を入れておきます。

 

If Me.ロットNOcmb = "" Or IsNull(Me.ロットNOcmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "ロットNO =" & Me.ロットNOcmb & " "

End If

 

■解説③ レコードソースの変更

最後にフォームのレコードソースを変更します。

検索パターンで作成した内容にてクエリ(SQL)を作成し、レコードソースに引当てします。

 

'全てのコンボボックスにデータがない場合は、テーブルデータをそのまま全て表示する。

'IDで昇順並べ替えをしています。

 

'コンボボックスに一つでもデータがある場合は、抽出条件strworkの検索結果を表示する。

'IDで昇順並べ替えをしています。

 

If strwork = "" Then

Me.RecordSource = "SELECT * FROM TJ_生産実績 ORDER BY ID"

Else

Me.RecordSource = "SELECT * FROM TJ_生産実績 Where " & strwork & "ORDER BY ID"

End If

 

■解説のおまけ あいまい検索について

例えば"C"という文字がつく商品名を全て抽出したいなどの場合、下記となります。

あいまい検索に使用するLikeの後に'*テキストデータ*'+空白を入れます。

 

If Me.商品名cmb = "" Or IsNull(Me.商品名cmb) Then

Else

If strwork <> "" Then strwork = strwork & "AND "

strwork = strwork & "商品名 Like '" & "*" & Me.商品名cmb & "*' "

End If

 

f:id:takalogpoint:20180930115709j:plain

 

■最後に

クエリで検索する場合、項目が多いと、クエリの抽出条件がこんなことになりますね。

ちょっと見にくくて申し訳無いですが。

 

f:id:takalogpoint:20180930115826j:plain

 

これは、5つの項目について抽出条件を作成したものですが、行・列ともにかなり多くなります。

さらに検索項目を後で増やすなどの修正が入ると、もう、面倒でしょうがないです。

なので、特に条件項目が多い場合や、後で増えることが予想されるときは、VBAにしておくと便利なのです。

データ型が大体、上記の3パターンが多いので、ひな形として置いておきます。

そしてパターン①②③のいずれかをコピーして、項目名を変更すれば良いだけとなります。

(あいまい検索もよく使用しています)

クエリで検索は面倒だなと思ったら、これで検索の実装がかなり楽になると思うのでやってみてはいかがでしょうか。

 

 

 


よくわかる Microsoft Access 2016 基礎

よくわかる Microsoft Access 2016 基礎

 

 

 

 アマゾンで検索

https://amzn.to/2R6vELY

 


■関連記事

 

www.omake-zakki.com

 

www.omake-zakki.com

 

www.omake-zakki.com

 


 

www.omake-zakki.com

 

www.omake-zakki.com

 

www.omake-zakki.com

 

ふとんクリーナーはレイコップ

 

www.omake-zakki.com

 

www.omake-zakki.com

 

www.omake-zakki.com

 

 

www.omake-zakki.com

 

www.omake-zakki.com

 

www.omake-zakki.com

 

www.omake-zakki.com

 

 

 

 

フリューのフィギュアは次のステージへ!F:NEX(フェネクス)