Excel vba のエラー「オブジェクト変数またはWithブロック変数が設定されていません」を解決できない

当方、はじめてVBAを書き始めている者です。変な質問になていたらすみませんが、よろしくお願いします。

＜やりたいこと＞
・ユーザーフォームにテキストボックス、検索ボタン、リストボックスを設置して、検索フォームを作る。
・その際、Accessのデータベースを参照する（別のモジュールに書いているのをCallしています）

＜エラー内容＞
実行すると、「オブジェクト変数またはWithブロック変数が設定されていません」というエラーが出てしまいます。
おそらく、下記の部分がおかしいのではないかと思うのですが・・・

If strSQL Like "*" & TextBox1.Value & "*" Or strSQL Like "*" & TextBox2.Value & "*" Then  '検索が一致すれば（結果をリストボックスに表示）
        Do Until adoRs.EOF          '抽出したレコードが終了するまで繰り返す
            With SurchList          '該当したレコードをリストボックスに表示する
                .AddItem adoRs.Fields(0).Value
            End With
            adoRs.MoveNext
        Loop
        ・
        ・
        ・

全文は以下の通りです。

Option Explicit

'---ACCESS接続用
Private adoCn As Object
Private adoRs As Object
Private strSQL As String

Private Sub SurchButton_Click() '検索

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

End Sub

（追記）

Dim SurchList As Object　を追加してみましたが、やはり同じ症状でした。。

さきほど気づいたのですが、イミディエイトウィンドウに以下のテキストが表示されていました。

「オブジェクト変数またはWithブロック変数が設定されていません。
SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター」

該当箇所は書籍やネットを参照した感じだと、これで合ってそうなので、どう間違っているのか分かりません。

（追記２）
なるほど。オブジェクトについてよく理解していませんでした。

Set adoRs = ○○
Set SurchList = ○○

という記述が必要だということでよろしいでしょうか？

（追記２-２）

set SurchList = ○○　←インスタンス(実体)への参照を作る

○○　には　CreatObject("△△")を使うということで合っていますでしょうか？

取り急ぎコメントしております。自分でもここら辺のことを調べてみます。

（追記３）

おそらくadoRsはConnectDB関数の中で操作しているはずです。

おっしゃる通りで、確認できました。

SurchListは画面上に貼り付けたリストボックスのはずです。

これもその通りです（上記質問では説明不足ですみません）。ユーザーフォームに貼り付けています。
プロパティのオブジェクト名は一致しているのですが、うまく行きません……
スペルミスのご指摘ありがとうございます。恥をかかずにすみました。

ただ、まだ解決には至っておりません……

追記４（2018/03/15）

まず、デバッグするときは、エラー処理はコメントにしましょう。

ご指摘のとおりしましたところ、次の行にエラーが出ておりました。

vba
1adoRs.Open strSQL, adoCn

strSQL = "SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター"のすぐ下の行です。

エラーメッセージは次のとおりです。
実行エラー'91':
オブジェクト変数またはWithブロック変数が設定されていません。

どのような「検索」がしたいのですか。言葉で説明してみてください。

特に検索対象フィールドは何か、どのような条件で検索するのかを説明してください。

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

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

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

このうちのいずれかがデータと一致していたら、検索結果が表示されるようにしたいです。
テキストボックスは２つ用意してますが、使用するのは基本的にどちらか１つで、より絞り込んで検索したい場合は２つとも使用する、というふうに考えております。

Me.SurchList というように記述しましょう。

修正させていただきます。ご指摘ありがとうございます。とても助かります。

追記５

今回の問題に関係があるかもしれませんので、別のモジュールに書かれてあるコードを併載させていただきます。
どうぞよろしくお願いします。

vba
1Option Explicit
2
3'---ACCESS接続用
4Private adoCn As Object
5Private adoRs As Object
6Private strSQL As String
7
8'データベースに接続
9Sub ConnectDB(flg As Boolean)   
10    Dim DBpath As String
11
12    'DBpath = ThisWorkbook.Path
13    DBpath = "\○○○\○○○\○○○"
14    If flg = True Then Set adoRs = CreateObject("ADODB.Recordset")
15    
16    Set adoCn = CreateObject("ADODB.Connection")
17    adoCn.Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source = " & DBpath & "\○○○.mdb;"
18End Sub
19
20'データベースを切断
21Sub CutDB(flg As Boolean) 
22
23    If flg = True Then adoRs.Close
24    adoCn.Close
25    Set adoRs = Nothing
26    Set adoCn = Nothing
27End Sub

行動規範の内容に同意します

回答5件

adoRsにADODB.RecordsetをNewしてないんじゃないですか？

vba
1'adoRs.Open strSQL, adoCn
2set adoRs = adoCn.Execute(strSQL)

それかこれでいいのでは？

投稿2018/03/15 01:52

sousuke

総合スコア3830

miniryu

2018/03/15 02:34

ご回答ありがとうございます。レコードセットの開き方を勉強してみます！

行動規範の内容に同意します

ベストアンサー

まず、デバッグするときは、エラー処理はコメントにしましょう。

vba
1'    On Error GoTo Err_Handler   'エラーが起きたら"Err_Handler"へ
2

これでエラーが発生したら、エラー箇所が黄色反転表示されるので、
原因を特定しやすくなります。

そして、実行してください。エラーが出たときにどの行が反転表示されるか、
提示してください。
あと、正確なエラーメッセージも提示してください。

話は、それからです。

追記

指摘1
今回のエラーと関係ないですが、下記はあきらかに間違いです。

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

strSQL はただの文字列です。これを Likeで比較しても検索などできません。

どのような「検索」がしたいのですか。言葉で説明してみてください。
特に検索対象フィールドは何か、どのような条件で検索するのかを説明してください。

指摘2
SurchList というのはフォーム上のリストボックスですか。だとしたら、
Me.SurchList というように記述しましょう。そうすれば、フォーム上のコントロールだと他人にも分かります。フォーム上のコントロールなら、Dim SurchList As Object Set SurchList = ○○ というコードは必要ないです。すでにフォーム上に実体が存在しているので。

同様に、TextBox1.Value も Me.TextBox1.Value と記述したほうが読みやすい紛れのないコードになります。

追記2

adoRs.Open strSQL, adoCn でエラーがでるとのことなので、adoRs か adoCn が正しく生成されていないということだと思われます。

質問者さんの追記で「おそらくadoRsはConnectDB関数の中で操作しているはずです。」との指摘にたいして、「確認できました。」と回答してますので、そのConnectDB関数内のコードに問題があるはずです。ADOのコネクション、レコードセットに生成、開き方については、いろいろな方法がありますが、ネット検索すれば、サンプルコードが多く見つかるので、それと現状のConnectDB関数内のコードを比較して、間違いを探してください。
例えば、下記は「ADO レコードセット開く」でググって最初に出てくるものです。

アクセスVBA講座_ADO_Recordsetオブジェクトの作成

上記のリンク先はAccess VBAなので、Excelなら、下記のほうがいいかな。

テーブルへの接続とレコードの取得 - 覚え書き ExcelVBAでデータベース操作

それでわからなければ、ConnectDB関数のコードを提示してください。ただ、やり取りが長くなったので、新規に質問しなおしたほうがいいかも知れませんね。

投稿2018/03/14 11:53

編集2018/03/15 02:43

hatena19

総合スコア34017

miniryu

2018/03/15 01:44

ご回答ありがとうございます。ご指摘いただいた点について、質問欄に追記させていただきました。どうぞよろしくお願いいたします。

hatena19

2018/03/15 02:18

回答に追記しましたので、確認してください。

miniryu

2018/03/15 02:22

ありがとうございます。やり方を調べてみて、分からなかったら新規でまた質問させていただきたいと思います。とても勉強になりました。

hatena19

2018/03/15 02:27

あと、検索条件の設定についても修正する必要がありますが、まずは、ConnectDB関数で、コネクション、レコードセットの生成ができるようになってからですね。これも一つの質問にまとめてしまうと、今回のように煩雑で長いやり取りになる可能性があるので、まずは、次の質問で「ConnectDB関数」を完成させてから、次に新規で、検索条件について質問するというように分けたほうがいいでしょう。

行動規範の内容に同意します

すみません、本回答ではありません。
（追記２）について。

Set adoRs = ○○
Set SurchList = ○○
という記述が必要だということでよろしいでしょうか？

違います。

おそらくadoRsはConnectDB関数の中で操作しているはずです。
ご確認ください。
何もしてなかったらそれはひとつの原因です。

SurchListは画面上に貼り付けたリストボックスのはずです。
プロパティを確認し、同じ名前であるか確認してください。

余談ですが、Surchのスペルが間違っているのが気になります（Searchですね）

改めてまじめに見てみました。
エラーメッセージから推察すると、Withブロックかオブジェクトがおかしいということになります。
コード中唯一のWithブロックは確認いただいたように、SurchListリストボックスを指しているということで問題ないでしょう。
となると他のオブジェクト(Object)が想定しているオブジェクトで構成されていないものと思われます。
提示されているコード中で明示的にオブジェクトを生成している箇所はありません。
最初に回答したように、またご確認いただいたように、ConnectDB関数内でadoCn,adoRsのオブジェクトを生成しているはずなので、そこに問題があると思われます。
つまりデータベースに接続できていないなどの問題があるのではと思います。
ConnectDB関数を呼び出し後、adoRs.Openをしている場所にブレークポイントを置いて、adoRsの状態を確認してみてください。

投稿2018/03/14 08:02

編集2018/03/15 00:15

ttyp03

総合スコア16998

おそらくSurchList の実体がない。もしくは参照されていないのでしょう。
その状態で

with　SurchList

としても何を用いて今後の処理を行えばよいか、vbaさんは理解してくれないのです。

dim SurchList as ○○ ←型を宣言
set SurchList = ○○　←インスタンス(実体)への参照を作る
そのあとでwithを使いましょう。

投稿2018/03/14 07:12

rinren

総合スコア107

SurchList が宣言されていないからでは？

投稿2018/03/14 06:33

Zuishin

総合スコア28662

miniryu

2018/03/14 06:43

早速ありがとうございます。 Dim SurchList As Object　を追加してみましたが、やはり同じ症状でした。。さきほど気づいたのですが、イミディエイトウィンドウにエラーメッセージと一緒に「SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター」が表示されていました。ここがおかしいのかもしれませんが、書籍やネットで参照した感じだと、これで合ってそうなので、どう間違っているのか分かりません。何かお分かりになりませんか？

Zuishin

2018/03/14 06:45

どんなエラーが出ているのか意訳せず正確に書いてください。ここではなく質問に追記する形でお願いします。

Zuishin

2018/03/14 06:57

adoRs がただの Object ではないですか？何か入れていますか？ SurchList もそうです。

hatena19

2018/03/14 12:18

>「SELECT 顧客コード,姓,名,住所１,住所２,電話番号１,電話番号２ FROM 顧客マスター」 > が表示されていました。エラーハンドラー内で、 Debug.Print strSQL としているので、strSQL の内容が表示されているだけです。とりあえず、On Error GoTo Err_Handler の行をコメントアウトしましょう。

行動規範の内容に同意します

あなたの回答