Tips

【Access VBA】実践演習 #2 (検索機能の実装)
2019.08.15

【Access VBA】実践演習 #2 (検索機能の実装)

このシリーズでは書籍管理システムの作成を通して、Access VBAの使い方を学ぶことを目標とします。
データベース操作はDAOを使って実装します。
Accessの基本的な操作は知っているけれど、VBAはあまり書いたことがない方を対象としています。

途中SQLも使いますので、不安な方はこちらの記事等で確認してください。
AccessでSQLの練習

必要なファイルのダウンロードやテーブル等の確認は初回の記事をご覧ください。
【Access VBA】実践演習 #1


今回は「F_書籍検索」の各機能を実装していきたいと思います。

■練習2-1 (検索ボタン)

「検索」ボタンをクリックしたときに、
「タイトル」「カテゴリ」「著者」の値に応じた検索結果が表示されるようにしてください。
※「タイトル」「著者」は部分一致検索、「カテゴリ」は完全一致検索とします。
※全て空の状態で検索が押されたときは、全件表示となるようにしてください。
(実行例)

(補足)
・F_書籍検索のレコードソースは「Q_書籍検索」クエリを使用しています。

解答例・解説
方法はいくつかありますが、2パターン紹介します。

①レコードセットの「Filterプロパティ」を使った検索

流れとしては以下です
1.「Q_書籍検索」を使ってレコードセットを取得
2. 必要に応じてFilterプロパティに条件式を設定。⇒ レコードセット再取得
3. 手順2. を検索条件毎に行う。
4. 得られたレコードセットをフォームに再設定 ⇒ フォームを更新

では順に実装していきます。
まずDB接続とレコードセットの取得までが以下のコードになります。

Private Sub 検索ボタン_Click()

    ' 変数の宣言
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' 現在開いているデータベースに接続
    Set db = CurrentDb()
    
    '「Q_書籍検索」を使ってレコードセットを取得
    Set rs = db.OpenRecordset("Q_書籍検索", dbOpenDynaset)
    
End Sub

ここで取得したレコードセットは全件取得しているので、Filterプロパティを使って絞り込みを行います。
Filterプロパティの使い方は以下。

使い方 : rs.Filter = "条件式"

条件式にはSQLのWHERE句とほぼ同じ書式で条件を記述します。
例えばカテゴリ名「プログラミング」で検索する場合「rs.Filter = “カテゴリ名 = ‘プログラミング'”」

これを、「タイトル」「カテゴリ」「著者」に値が入力されている時のみ実行するようにしたのが以下のコードです。

    ' タイトルが入力されている場合
    If IsNull(Me.検索_タイトル) = False Then
        rs.Filter = "タイトル like '*" & Me.検索_タイトル & "*'"
        Set rs = rs.OpenRecordset
    End If
    
    ' カテゴリが入力されている場合
    If IsNull(Me.検索_カテゴリ) = False Then
        rs.Filter = "カテゴリ名 = '" & Me.検索_カテゴリ.Column(1) & "'"
        Set rs = rs.OpenRecordset
    End If
    
    ' 著者が入力されている場合
    If IsNull(Me.検索_著者) = False Then
        rs.Filter = "著者 like '*" & Me.検索_著者 & "*'"
        Set rs = rs.OpenRecordset
    End If

Filterプロパティの設定値は文字列結合が絡んでいるので見づらくなっています。
不安な方は一度固定値で条件式を作成して確認してみてください。
また、Filterを設定した後に「Set rs = rs.OpenRecordset」でレコードセットを再取得しています。
Filterプロパティは設定するたびに以前のフィルターを無効化してしまうので、
これが無いと複数条件を指定した時に、最後の条件しか効かなくなってしまいます。
気になる方はコメントにして動作確認してみてください。

ここまででフィルター処理済みのレコードセットが取得できたので、
最後にフォームのレコードセットを再設定します。

    Set Me.Recordset = rs    
    Me.Requery

「Me.Requery」は無くても動くかと思いますが、念のためフォームの更新をかけています。

以上をまとめたコードが以下です。

Private Sub 検索ボタン_Click()

    ' 変数の宣言
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    ' 現在開いているデータベースに接続
    Set db = CurrentDb()
    
    '「Q_書籍検索」を使ってレコードセットを取得
    Set rs = db.OpenRecordset("Q_書籍検索", dbOpenDynaset)
    
    ' タイトルが入力されている場合
    If IsNull(Me.検索_タイトル) = False Then
        rs.Filter = "タイトル like '*" & Me.検索_タイトル & "*'"
        Set rs = rs.OpenRecordset
    End If
    
    ' カテゴリが入力されている場合
    If IsNull(Me.検索_カテゴリ) = False Then
        rs.Filter = "カテゴリ名 = '" & Me.検索_カテゴリ.Column(1) & "'"
        Set rs = rs.OpenRecordset
    End If
    
    ' 著者が入力されている場合
    If IsNull(Me.検索_著者) = False Then
        rs.Filter = "著者 like '*" & Me.検索_著者 & "*'"
        Set rs = rs.OpenRecordset
    End If
    
    Set Me.Recordset = rs
    
    Me.Requery

End Sub
②SQLを使った検索
こちらは先に解答例を載せます。

Private Sub 検索ボタン_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    
    ' 全件取得のSQLを作成
    sql = "SELECT * FROM 蔵書一覧 LEFT OUTER JOIN カテゴリ一覧 ON 蔵書一覧.CATEGORY_ID = カテゴリ一覧.CATEGORY_ID WHERE 1 = 1"
    
    ' タイトルが入力されている場合
    If IsNull(Me.検索_タイトル) = False Then
        sql = sql & " AND タイトル like '*" & Me.検索_タイトル & "*'"
    End If
    
    ' カテゴリが入力されている場合
    If IsNull(Me.検索_カテゴリ) = False Then
        sql = sql & " AND カテゴリ名 = '" & Me.検索_カテゴリ.Column(1) & "'"
    End If

    ' 著者が入力されている場合
    If IsNull(Me.検索_著者) = False Then
        sql = sql & " AND 著者 like '*" & Me.検索_著者 & "*'"
    End If
    
    ' データベースに接続
    Set db = CurrentDb()
    
    ' SQLでレコードセットを取得
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)

    Set Me.Recordset = rs
    
    Me.Requery
    
End Sub

まず初めにベースとなるSQLを作成していきます。
欲しいのは蔵書一覧ですが、カテゴリーはIDではなく名前で取得したいので、カテゴリ一覧テーブルを結合します。
念のため左外部結合を使って以下のようなSQLになります。

SELECT * FROM 蔵書一覧 LEFT OUTER JOIN カテゴリ一覧 
ON 蔵書一覧.CATEGORY_ID = カテゴリ一覧.CATEGORY_ID

これで問題無いのですが、7行目のSQLでは「WHERE 1=1」という検索に影響しない条件式を繋げています。
これはこの後の「タイトル」「カテゴリ」「著者」のフィルター実装をシンプルにするための工夫です。
(WHEREを付ける付けない、ANDを付ける付けないの分岐処理を省略することができます。)

SQLが作成できれば、あとはOpenRecordsetの引数に指定することで、
いきなり目的のレコードセットが取得できます。


■練習2-2 (クリアボタン)

「クリア」ボタンをクリックしたときに入力フォームの値が消去され、
全レコードが表示されるようにしてください。

(ヒント)
・値の消去は、入力フォームの値をNullに設定。
・フィルターの解除は全件取得した結果をレコードセットに設定。

解答例・解説
練習2-1と同様に、2パターン紹介します。

①クエリを使って取得
解答例は以下になります。

Private Sub クリアボタン_Click()
    
    ' 検索条件を空にする
    Me.検索_タイトル = Null
    Me.検索_カテゴリ = Null
    Me.検索_著者 = Null
    
    ' 「Q_書籍検索」の結果をフォームに設定
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Q_書籍検索", dbOpenDynaset)
    
    Set Me.Recordset = rs
    
    Me.Requery

End Sub

検索条件を空にするところは、「Me.検索_タイトル = “”」のようにしても
見かけ上問題無く動作しますが、その状態で「検索」ボタンをクリックすると何も表示されないケースが生まれます。
(IsNULLの判定が空文字に対してはFalseになってしまうため。)

データの取得は練習2-1と同じように行い、フィルターをかけずにフォームにセットするだけなので、
練習2-1の内容を理解していれば簡単かと思います。

②SQLを使って取得
解答例は以下になります。

Private Sub クリアボタン_Click()
    
    ' 検索条件を空にする
    Me.検索_タイトル = Null
    Me.検索_カテゴリ = Null
    Me.検索_著者 = Null
    
    ' 「Q_書籍検索」の結果をフォームに設定
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim sql As String
    
    ' 全件取得のSQLを作成
    sql = "SELECT * FROM 蔵書一覧 LEFT OUTER JOIN カテゴリ一覧 ON 蔵書一覧.CATEGORY_ID = カテゴリ一覧.CATEGORY_ID"
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset(sql, dbOpenDynaset)
    
    Set Me.Recordset = rs
    
    Me.Requery

End Sub

今回のようにクエリが用意されていればあえてSQLを記述する必要は無いので、
基本的には解答例①の方法で良いかと思います。


■練習2-3 (貸出ボタン)

「貸出ボタン」をクリックした際に、「F_貸出登録」を開くようにします。
ただし、対象の書籍(BOOK_ID)のフォームが開くようにフィルターをかけてください。
また、「貸出中」の書籍の場合にはエラーメッセージを表示してください。

・BOOK_ID=2 の貸出ボタンクリック時

・BOOK_ID=3 の貸出ボタンクリック時

(ヒント)
・フィルターはDoCmd.OpenFormの第4引数で指定可能。

解答例・解説

解答例は以下になります。

Private Sub 貸出ボタン_Click()
        
    If Me!貸出状況 = "貸出可" Then
        DoCmd.OpenForm "F_貸出登録", acNormal, "", "BOOK_ID=" & Me.BOOK_ID
    Else
        MsgBox "貸出中の書籍です。", vbCritical
    End If
    
End Sub

前回と同じようにDoCmd.OpenFormを使いますが、第4引数でBOOK_IDによるフィルターをかけています。
第2、第3引数は特別な設定は不要なので規定値にしています。
貸出状況による分岐はIf文を使って作成しています。


■練習2-4 (編集ボタン)

「編集ボタン」クリック時に、「F_書籍編集」を開くようにしてください。
その際、「DoCmd.OpenForm」の引数「OpenArgs」に対象のBOOK_IDを設定してください。
また、開く処理の前に「F_書籍編集」を閉じる処理を実装してください。

(補足)
・OpenArgsは開くフォームに対して値を渡すための引数です。
今回は「F_書籍編集」にBOOK_IDを渡しています。
・閉じる処理は既に開いているフォームに対してOpenArgsの値が渡されない現象(バグ?)を防ぐためです。

解答例・解説

解答例は以下になります。

Private Sub 編集ボタン_Click()
    DoCmd.Close acForm, "F_書籍編集"
    DoCmd.OpenForm "F_書籍編集", OpenArgs:=Me!BOOK_ID
End Sub

ここで渡しているBOOK_IDは編集の処理を作成する時に使用します。

なお、渡した値が受け取れているか確認する場合、
「F_書籍編集」の「Form_Open」イベントに以下の様に記述すると良いです。

Private Sub Form_Open(Cancel As Integer)
    MsgBox OpenArgs
End Sub

渡したときと同じ「OpenArgs」という変数名で受け取ることができます。

以上、「F_書籍検索」関連の機能を実装しました。
やや難し目の内容も含んでいますが、今回の内容が理解できていると
データベース操作で良く使う検索の処理はおおよそ問題無いかと思います。

次回は登録処理を実装する予定です!

■記事一覧

#1 (準備とトップページの処理)
#2 (検索機能の実装)
#3 (登録機能の実装)
#4 (編集機能の実装)
#5 (貸出・返却機能の実装)

excel_VBAを学んで業務効率化!

excel-vba入門 連載

Recent News

Recent Tips

Tag Search