レコードセット検索

excel　vbaでレコードセットの中身を検索して
レコードセット内の別フィールドのデータを取得する方法がわかりません。やりたいことは、

｜店舗名称｜店番号｜
　渋谷店　　　01
　池袋店　　　02
　目黒店　　　03

というレコードセットがあって渋谷店を
コンボボックスで選択したときに、店番号の"01"が
別のコンボボックスで表示されるようにしたいのですが
うまくいきません。どなたか分かる方教えてください。
お願いします。

No.7 ベストアンサー 回答者： myRange 回答日時： 2009/12/12 12:10

またまた登場、myRangeです。

補足要求６に返答がありませんので次のようなことを推測してみました。

-------------------------------------------------------------
●取引先マスター●（取引先番号なし）

＿＿＿Ａ＿＿＿＿＿＿Ｂ＿＿＿＿＿＿Ｃ＿＿
１＿＿＿＿＿＿＿取引先名＿＿＿＿店番号＿＿
２＿＿＿＿＿ブランドＡ渋谷店＿＿１１１＿＿
３＿＿＿＿＿ブランドＡ青山店＿＿２２２＿＿
４＿＿＿＿＿ブランドＢ渋谷店＿＿１１１＿＿
５＿＿＿＿＿ブランドＢ青山店＿＿２２２＿＿
６＿＿＿＿＿ブランドＢ銀座店＿＿３３３＿＿
　

上記は、
最初の質問と補足の表のレイアウトが違うので
また、取引先名と店番号の関係が分かりませんので
補足の表を参考にして、
取引先名をユニークに、店番号はダブリあり、にしてます。

こういう感じであれば先の回答を少し変えればできます。
　
以下は、検索画面シートのシートモジュールに書くこと。

　
'------- 検索画面がアクティブになったとき、
'-------　ComboBox２をセット

Private Sub Worksheet_Activate()
　Dim myDic
　Dim R As Long

　Set myDic = CreateObject("Scripting.Dictionary")

　For R = 2 To Sheets("取引先マスタ").Cells(Rows.Count, "C").End(xlUp).Row
　　　If myDic.exists(Sheets("取引先マスタ").Cells(R, "C").Value) = False Then
　　　　　myDic.Add Sheets("取引先マスタ").Cells(R, "C").Value, ""
　　　End If
　Next R

　ComboBox2.List = myDic.keys
　ComboBox1.Clear
End Sub


'-----、Combox２ の選択で、ComboBox１を絞り込む

Private Sub ComboBox2_Change()
　Dim R As Long

　ComboBox1.Clear
　For R = 2 To Sheets("取引先マスタ").Cells(Rows.Count, "C").End(xlUp).Row
　　　If Sheets("取引先マスタ").Cells(R, "C").Value = Val(ComboBox2.Value) Then
　　　　　ComboBox1.AddItem Sheets("取引先マスタ").Cells(R, "B").Value
　　　End If
　Next R
End Sub
'-------------------------------------------------------

おそらくこれで望みどおりかと。。

●上記は、検索画面がアクティブになった時にComboBox２に項目をセットしてますので、
ComboBox２に項目をセットするタイミングは適宜変更のこと。

以上です。

この回答へのお礼

返信遅くなりましてすみません。
まさにそのとおりです。
myRangeのおかげで理想どおりの動作を実現することができました。
この度はわかりにくい質問、補足でご迷惑おかけしました。
次回から気をつけたいと思います。
本当にありがとうございました。

お礼日時：2009/12/14 12:11

No.6 回答者： myRange 回答日時： 2009/12/11 21:34

回答１への補足と少し（しかし最重要）違っているようなので再度補足が必要です。

----------------------------------------------------　
●回答１への補足

取引先｜　　 取引先名　　　｜ 店
10000 ｜　ブランドA　渋谷店｜ 01
10001 ｜　ブランドA　池袋店｜ 01
10001 ｜　ブランドB 池袋店｜ 02

この表を提示して、
＞ComboBox１のListfillRangeに設定して【取引先名】を表示させています
＞やりたいことは別のComboBox２を作成してそこに【取引先】だけを表示させて
＞"10001"を選択したらComboBox１に池袋店の2つが絞り込み表示させるということです
との説明なので

ComboBox１は、取引先名
ComboBox２は、取引先（正確には"取引先番号"と推測しますが）

となります。

--------------------------------------------
●今度の補足

＞レコードセットデータは【取引マスタ】シートにあります。
＞B列に取引先名があり、C列に店番号があります
＞ComboBox1には取引先名を表示させ、
＞ComboBox2には店番号が表示させるようにしたいです。
との説明なので

ComboBox１は、取引先名
ComboBox２は、店番号

となります。

--------------------------------------------------------
■■疑問点■■

（１）回答１の補足の表にある、取引先（番号）が今度の説明にはない
（２）回答１の補足説明と今度の説明では、ComboBox２にセットする項目が違う

■■更に確認点■■
　
（１）データの中での、取引先名、店番号、どちらもユーニークか？
（２）取引先（番号）があるとするとそれは、ユニークか？

最初に提示された、表からすると、取引先（番号）、取引先名、店番号ともユニークでないようだが、、
　
----------------------------------------------------
▲▲投稿に関する要望点▲▲

補足を投稿する前には、矛盾がないか何度も読み返してから投稿願います。
そうでないと何回も遣り取りをしなければならなくなるので。。

次回の回答で是非解決にしたいと思います。
　
以上です。
　

No.5 回答者： myRange 回答日時： 2009/12/10 23:13

＞本当に何度もすみません

いえいえ、何も気にする必要はありませぬよ。

で、本題。

＞【取引先マスタ】sheetと【検索画面】sheetが分かれているのですが

当方の見落としかな、と思い、
質問、補足に再度目を通してみましたが
【検索画面】という文言が出てきたのは今度が初めてですね。
ですから、当然ながら当方の回答は
抽出されたデータもCombobox１，２も【取引先マスタ】にあるものだ、
との前提になってます。

前提が崩れてますので、分かっていることもそうでないことも含めて
イチから明確にした方がいいでしょう。

（１）データはどのシートにあるのか
（２）そのデータはどのセルから始まっているか
（３）ComboBox１、２、はどのシートにあるのか
（４）検索画面シートのレイアウトはどうなってるか
　
また、出来れば次の回答で解決を図りたいと思いますので
上記の補足は詳しくお願いします。

以上です。
　
　

この回答への補足

ありがとうございます。
自分の説明不足でした。
＞（１）データはどのシートにあるのか
レコードセットデータは【取引マスタ】シートにあります。
ここには取引先名、店番号フィールドがあります。
完全にテーブル用のシートでそれ以外なにもありません

＞（２）そのデータはどのセルから始まっているか
Range"B2"から下に1400件ほどあります。
B列に取引先名があり、C列に店番号があります。D列にもデータはあるのですが重複データが多すぎて検索には使えなそうです。

＞（３）ComboBox１、２、はどのシートにあるのか
ComboBox1などコントロール類はすべて【検索画面】シートにあります。

＞（４）検索画面シートのレイアウトはどうなってるか
レイアウトですが、日付を指定するテキストボックスが一番上にあり、
ComboBox1がその下にあります。

ComboBox1には取引先名を表示させ、
その下のComboBox2には店番号が表示させるようにしたいです。
ComboBox2から任意の店番号を選択するとComboBox1にその番号に対応した取引先名を絞り込むようにさせたいです。
最終的には日付と指定した取引先名をSQLのWHERE条件にして、
その日にその取引先で売り上げた明細を別ブックに出力するプログラムです。この出力する部分は完成しています。

よろしくおねがいいたします。

補足日時：2009/12/11 15:20

No.4 回答者： myRange 回答日時： 2009/12/08 16:39

回答２の補足に以下のようにありますね。

----------------------------------------------
>上記のコードでmyRecに格納した。
>レコードをWorksheets("取引先マスタ")にコピーすると、

取引先｜　　 取引先名　　　｜ 店
10000 ｜　ブランドA　渋谷店｜ 01
10001 ｜　ブランドA　池袋店｜ 01
10001 ｜　ブランドB 池袋店｜ 02
10002 ｜　ブランドA　目黒店｜ 01　

>という形で表示されます。
>それをComboBox１のListfillRangeに設定して
>【取引先名】を表示させています。
>やりたいことは別のComboBox２を作成してそこに
>【取引先】だけを表示させて"10001"を選択したら
>ComboBox１に池袋店の2つが絞り込み表示させるということです
-----------------------------------------------

この説明から、
シート「取引マスタ」に取り込んだデータを
Combobox１,２に、必要項目だけ表示させ
ComboBox２の選択項目により、Combobox１を絞り込みたい
で、Comboboxへのセットの仕方などを知りたい
と、受け取りました。

●で、シート取引マスタにデータが表示された状態から
　Combobox１，２への処理の回答をしました。
　
が、エラーが出るということなので
質問者のコードを見直したところ
質問者のコードがCombox２のChangeイベントに書いてあることに気づきました。

なぜ、そこに、ADODBのコードがあるのでしょうか？
意味不明です。
ComBox２のChangeイベントには当方が回答したように
Combobox1への絞込み処理のコード【のみ】必要なはずです。


●何度も言うようですが、
当方の回答は、シート「取引マスタ」に取り込まれたデータを
Combobox１，２にそれをセット＆処理するコードです。

そこらをも少し明確に説明したらどうでしょう。

その前に試しに、
シート「取引マスタ」にデータが■表示されてる状態■で
例えば、CommandButtonのクリックで、
Combobox１，２に項目をセットし、
Combobox２のChangeイベントでCombobox１を絞り込む
というふうにしてみてください。


------ CommandButton1 のクリックで
　　　Combobox１，２の初期項目をセットする --------

Private Sub CommandButton1_Click()
　Dim myDic
　Dim R As Long

　Set myDic = CreateObject("Scripting.Dictionary")

　For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　If myDic.exists(Cells(R, "A").Value) = False Then
　　　　　myDic.Add Cells(R, "A").Value, ""
　　　End If
　Next R

　ComboBox2.List = myDic.keys
　ComboBox1.ColumnCount = 2
　ComboBox1.List = Range("B2", Cells(Rows.Count, "C").End(xlUp)).Value
End Sub


'-----、Combox２ Changeイベント　---------------

Private Sub ComboBox2_Change()
　Dim R As Long
　ComboBox1.Clear
　For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　If Cells(R, "A").Value = Val(ComboBox2.Value) Then
　　　　　ComboBox1.AddItem Cells(R, "B").Value
　　　　　ComboBox1.List(ComboBox1.ListCount - 1, 1) = Cells(R, "C").Value
　　　End If
　Next R
End Sub
'-------------------------------------------------------

●●Combobox１は、取引先名と店番号の２列表示に変更しました。

以上です。
　

　
　

この回答への補足

ご回答ありがとうございます。

言われたとおりにやってみましたが、
【取引先マスタ】sheetと【検索画面】sheetが分かれているのですが、
コンボボックスをみると検索画面のA,B列を持ってきてるみたいです。
WorkSheetsオブジェクトを付け足したりしてみましたがうまくいきません。できればアドバイスお願いします。

本当に何度もすみません。

補足日時：2009/12/10 14:25

No.3 回答者： myRange 回答日時： 2009/12/07 23:49

>ComboBox１のListfillRangeに設定して、【取引先名】を表示させています

●●ListFillRangeでは拙いので、ListFillRangeはセットしないこと！！


使用列　：　Ａ、Ｂ，Ｃ列
項＿目　：　１行目
データ　；　２行目～～


＞Worksheets("取引先マスタ").Range("B2").CopyFromRecordset data:=myRec

の次に下記、初期項目セットコードを追加する

------ Combobox１，２の初期項目をセットする部分 --------

　Dim myDic
　Dim R As Long

　Set myDic = CreateObject("Scripting.Dictionary")

　For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　If myDic.exists(Cells(R, "A").Value) = False Then
　　　　myDic.Add Cells(R, "A").Value, ""
　　End If
　Next R

　ComboBox2.List = myDic.keys
　ComboBox1.List = Range("B2", Cells(Rows.Count, "B").End(xlUp)).Value



'-----ここからは、Combox２ を選択する部分　------------------

Private Sub ComboBox2_Change()
　Dim R As Long
　
　ComboBox1.Clear
　For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
　　　If Cells(R, "A").Value = Val(ComboBox2.Value) Then
　　　　　ComboBox1.AddItem Cells(R, "B").Value
　　　End If
　Next R
End Sub
'-------------------------------------------------------

これで
Combobox２の取引先番号を選択すると
Combobox１の取引先名が絞り込まれます。
以上です。

この回答への補足

ご回答ありがとうございます。
教えいただいたとおりにやってみましたが、
初期設定の　＞ComboBox1.List = Range("B2", Cells(Rows.Count, "B").End(xlUp)).Value
の部分で、「不正なプロパティ配列インデックスです。」
とエラーメッセージが出ます。
原因はなにが考えられるでしょうか？

補足日時：2009/12/08 15:16

No.2 回答者： imogasi 回答日時： 2009/12/07 21:02

＞レコードセット

で何が言いたいのか。難しい語句を使うときは、注意して。
（１）ＣＳＶファイル。
（２）ＡｃｃｅｓｓのＭＤＢファイル。
（３）エクセルのシートのセル範囲のこと。（レコードセットとは余りいわないようだが）
（４）その他
それによってできるスキル（回答）が変わる。それを書かないで質問するとは。
もちろんそれらを含めて・総称して、データセットなどという概念もあるが。
＞別のコンボボックスで表示されるようにしたいのですが
またそれらの中から検索するのか？
それでは質問の例示が悪いではないか。
渋谷店　　　01
渋谷ー＞01で終わりではないか？
表示用にはコンボはあまり使わないようだが。
ーー
（１）データの検索のコードがわからない。
（２）フォームなりのコントロールのイベントで連動させるコードがわからない
のどちらか？

この回答への補足

Private Sub ComboBox2_Change()
　　
　　Dim myConn As New ADODB.Connection
Dim myRec As New ADODB.Recordset
Dim myCmd As New ADODB.Command
Dim strSQL As String
Dim intComInx As Integer
Dim strComLst As String


strSQL = strSQL & "SELECT"
strSQL = strSQL & " TM.取引先名 "
strSQL = strSQL & ",TM.取引先 "
strSQL = strSQL & ",TM.店 "
strSQL = strSQL & "FROM "
strSQL = strSQL & "V21.取引先ＲＭ TM "
strSQL = strSQL & "INNER JOIN V21.出荷先ＲＭ SM "
strSQL = strSQL & "ON "
strSQL = strSQL & "TM.取引先 = SM.出荷先 "
strSQL = strSQL & "AND TM.店 = SM.出荷先店 "
strSQL = strSQL & "AND TM.法人 = SM.法人 "


With myConn
.ConnectionString = _
"Provider=**********;Data Source=***;User Id=*****;Password=********"
.Open
End With

' オブジェクトのインスタンス化
Set myCmd.ActiveConnection = myConn
Set myRec.ActiveConnection = myConn

' コマンドテキストの設定
With myCmd
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

intComInx = ComboBox1.ListIndex
strComLst = ComboBox1.List(intComInx)

myRec.Open strSQL, myConn, , adLockReadOnly, 1
myRec.Find

Worksheets("取引先マスタ").Range("B2").CopyFromRecordset data:=myRec

すみません。質問が雑になっていました。
補足しますと、上記のコードでmyRecに格納した。
レコードをWorksheets("取引先マスタ")にコピーすると、

取引先｜　　 取引先名　　　｜ 店
10000 ｜　ブランドA　渋谷店｜ 01
10001 ｜　ブランドA　池袋店｜ 01
10001 ｜　ブランドB 池袋店｜ 02
10002 ｜　ブランドA　目黒店｜ 01　
・
・
・
という形で表示されます。
それをComboBox１のListfillRangeに設定して、【取引先名】を表示させています。やりたいことは別のComboBox２を作成してそこに【取引先】だけを表示させて"10001"を選択したらComboBox１に池袋店の2つが絞り込み表示させるということです。これは可能なのでしょうか？
理想は【取引先名】を選択すると【取引先】と【店】が連動して表示させることです。それと【取引先名】はユニークで【取引先】と【店】の組み合わせで設定しています。
よろしくお願いします。

補足日時：2009/12/07 21:06

No.1 回答者： nda23 回答日時： 2009/12/07 18:13

>別のコンボボックス

これが問題
元ネタが同じで、"01"を選択状態にしたいのか、あるいは
「渋谷」というカテゴリのデータ集団をコンボボックスの値集合に
したいのかで、方法が違います。どっちでしょ？

この回答への補足

補足を追記させていただきましたので、
御時間に余裕があれば是非アドバイスお願いいたします。

補足日時：2009/12/07 21:33