ユーザーフォームに検索機能を実装したいが、検索に必要なコードの書き方が分からない

Question

（前回の質問に対する回答を受けてエラーを消すことができました。次に検索機能の実装に必要なコードについて質問させてください）

＜やりたいこと＞

• Excelのユーザーフォームで検索する
• その際はAccessのデータベースを参照する

＜ユーザーフォームの操作＞

テキストボックスは２つ用意してますが、使用するのは基本的にどちらか１つで、より絞り込んで検索したい場合は２つとも使用する、というふうに考えております。

検索条件は
１つめのテキストボックスに「姓」または「ｾｲ」を入力する
２つめのテキストボックスに「電話番号」を入力する
ということを、また部分一致を想定しています。

このうちのいずれかがデータと一致していたら、検索結果が表示されるようにしたいです。

検索対象フィールドは
「姓」「ｾｲ」「電話番号１」「電話番号２」
というのを想定しています。

リストボックスに表示させたいフィールドが
「顧客コード」「姓」「名」「住所１」「住所２」「電話番号１」「電話番号２」
です。

下記は（書き方を理解できていない）検索条件のコードです…

If strSQL Like "*" & Me.TextBox1.Value & "*" Or strSQL Like "*" & Me.TextBox2.Value & "*" Then  '検索が一致すれば（結果をリストボックスに表示）

前回の質問時にstrSQLはただの文字列なので、LIKEで比較しても検索できないとご指摘いただいています。
自分でも調べてみたのですが、まだ要領がわかっておりません。

検索用のコード全文は下記の通りです。よろしくお願いします。

'---ユーザーを検索
Private Sub SearchButton_Click()

'    On Error GoTo Err_Handler   'エラーが起きたら"Err_Handler"へ
    Call ConnectDB  'データベースに接続
    
    strSQL = "SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター" '特定のフィールドを抽出
    adoRs.Open strSQL, adoCn
    
    If adoRs.BOF = True And adoRs.EOF = True Then   'データがない場合、データベースを切断して終了
        Call CutDB
        MsgBox "参照先にデータがありません。"
        Exit Sub
    End If
    
    If strSQL Like "*" & Me.TextBox1.Value & "*" Or strSQL Like "*" & Me.TextBox2.Value & "*" Then  '検索が一致すれば（結果をリストボックスに表示）
        Do Until adoRs.EOF      '抽出したレコードが終了するまで繰り返す
            With Me.SearchList  '該当したレコードをリストボックスに表示する
                .AddItem adoRs.Fields(0).Value
            End With
            adoRs.MoveNext
        Loop
    Else                        '検索に一致するものがなければ、データベースを切断して終了
        Call CutDB
        MsgBox "一致するデータがありません。"
    End If
    
    Call CutDB      '検索がすんだら、データベースを切断して終了
    Exit Sub
    
Err_Handler:        'エラーが起きたときの飛び先
    Call CutDB
    MsgBox Error$
    Debug.Print Error$
    Debug.Print strSQL

End Sub

Comments

[hatena19]

検索条件は、完全一致ですか、それとも、部分一致ですか。Like "*" & Me.TextBox1.Value & "*" というような条件は部分一致ですが。

Answer 1

検索対象フィールドは
「姓」「ｾｲ」「電話番号１」「電話番号２」

リストボックスに表示させたいフィールドが
「顧客コード」「姓」「名」「住所１」「住所２」「電話番号１」「電話番号２」

です。

これらの全てのフィールドを含むレコードセットを開く必用がありますので、SQL文字列は、
"SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２, ｾｲ FROM 顧客マスター"
ですね。

上記のSQL文字列を利用してレコードセットを開いたあと、そのレコードセットに抽出条件を設定するには、Filter プロパティを利用します。

複数列のリストボックスの作成は下記が参考になるでしょう。

Office TANAKA - Excel VBA Tips[複数列のリストボックス]

サンプルコード

条件の詳細な仕様が不明なので、部分一致で、(姓, ｾｲ) と (電話番号1, 電話番号2) はAND条件 と仮定してます。

'---ユーザーを検索
Sub SearchButton_Click()
    Dim strSQL As String
	Dim cnt As long
'    On Error GoTo Err_Handler   'エラーが起きたら"Err_Handler"へ
    Call ConnectDB  'データベースに接続

    strSQL = "SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２, ｾｲ FROM 顧客マスター"
    adoRs.Open strSQL, adoCn

    If adoRs.BOF And adoRs.EOF Then   'データがない場合、データベースを切断して終了
        Call CutDB
        MsgBox "参照先にデータがありません。"
        Exit Sub
    End If

    '抽出条件を設定してフィルタをかける
    adoRs.Filter = "姓 & ﾒｲ Like '%" & Me.TextBox1.Value & "%'" & _
              " AND 電話番号１ & ";" & 電話番号２ Like '%" & Me.TextBox1.Value & "%'"
    If adoRs.BOF AND adoRs.EOF Then
        Call CutDB
        MsgBox "条件に一致するデータがありません。"    
    Else
        With Me.SearchList  '該当したレコードをリストボックスに表示する
            .ColumnCount = 7 '列数 7
            .ColumnWidths = "10;30;30;100;50;50" '列幅を設定
            Do Until adoRs.EOF      '抽出したレコードが終了するまで繰り返す
                .AddItem ""
                .List(cnt,0) = adoRs(0).Value
                .List(cnt,1) = adoRs(1).Value
                .List(cnt,2) = adoRs(2).Value
                .List(cnt,3) = adoRs(3).Value
                .List(cnt,4) = adoRs(4).Value
                .List(cnt,5) = adoRs(5).Value
                .List(cnt,6) = adoRs(6).Value                
                adoRs.MoveNext
                cnt = Cnt + 1
            Loop
        End With
    End If

    Call CutDB      '検索がすんだら、データベースを切断して終了
    Exit Sub

Err_Handler:        'エラーが起きたときの飛び先
    Call CutDB
    MsgBox Error$

End Sub

Comments

[sazi]

ワイルドカードは*では？

[hatena19]

ADOの場合は、ワイルドカードは % ですね。
ただ、今、確認してみたら、Filterプロパティに設定する場合は、* でも % でもいいみたいですね。
SQLの場合は、% でないとだめですが。

[miniryu]

サンプルコードを元に自分の思う仕様に近づけることができました。
私の理解が不十分な点についても的確にアドバイスいただけて助かりました。
前回に引き続き、ありがとうございました。

Answer 2

検索条件のところ良く見てなかったので適当な回答になってましたので訂正します。

SQL文に検索条件を組み込みましょう。
SQL自体はこんな感じで（TextBox1と2を指定した場合）

SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２
  FROM 顧客マスター
  WHERE 姓 LIKE '%検索性%' OR 電話番号１='検索番号' OR 電話番号２='検索番号'

VBA側はこんな感じ。

strSQL = "SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター "
strSQL = strSQL & "WHERE 姓 LIKE '%" & TextBox1.Value & "%' OR 電話番号１='" & TextBox2.Value & "' OR 電話番号２='" & TextBox2.Value & "'"

姓は部分一致、電話番号は完全一致にしていますが、仕様にあわせて修正してください。
またTextBox1と2が必ず指定されるわけではないはずですので、必要な分だけ条件を追加しましょう。
電話番号が数字型なのか文字型なのかわからないので、とりあえず文字型としています。
条件がORなのかANDなのかはわからないので、仕様にあわせてください。

Comments

[sazi]

ワイルドカードは*では？

[ttyp03]

おっと、accessだから*か。うっかり。
指摘ありがとうございます。

[miniryu]

動的なSQL?の書き方があるんですね。勉強になります。
ありがとうございました。

Answer 3

先ず、

１つめのテキストボックスに「姓」または「ｾｲ」を入力する

とは、漢字か半角カナを入力するということですか？
もしそうなら、検索対象とする項目はなんでしょう？
該当しそうな項目は「姓」,「名」しかありませんがこちらには漢字の氏名が格納されているのではないでしょうか？

条件の氏名の部分は条件追加になるでしょうが、記述としては以下のようになるかと。

    If adoRs!姓 Like "*" & IIF(IsNull(Me.TextBox1.Value), "", Me.TextBox1.Value) & "*" Or adoRs!電話番号1 Like "*" & IIF(IsNull(Me.TextBox2.Value), "", Me.TextBox2.Value) & "*" Or adoRs!電話番号2 Like "*" & IIF(IsNull(Me.TextBox2.Value), "", Me.TextBox2.Value) & "*" Then  '検索が一致すれば（結果をリストボックスに表示）

ところで以下はリストボックスに顧客コードを追加していますが、顧客コードのみで分かるんでしょうか？

            With Me.SearchList  '該当したレコードをリストボックスに表示する
                .AddItem adoRs.Fields(0).Value
            End With

Comments

[miniryu]

私の理解が不十分なところをご指摘いただきありがとうございます。とても勉強になりました。