Access200xにおいて、VBAの変数の値をリストボックスのRowSource内で参照する方法はありますか？

質問

Access初心者です。説明不足なところなどありましたら、ご指摘ください。

フォームにテキストボックスとリストボックスを配置し、テキストボックスに入力した値をVBAで加工して、その加工された値をキーにDBを参照して、リストボックスに表示したいと考えています。

テストの手順を簡単に説明します（この問題はデータの加工自体にはないと考えているので、入力された値をそのままキーにして値を取得しています。）

１．テーブル作成（テーブル名：tblTEST）
テーブル構造
ID DATA
1 あああ
2 いいい
3 ううう

２．フォーム作成（フォーム名：frmTSET）

３．frmTEST内にテキストボックス作成（オブジェクト名：txtTEST）

４．frmTEST内にリストボックス作成（オブジェクト名：lstTEST）
RowSourceプロパティの値に
SELECT [tblTEST].[DATA] FROM tblTEST WHERE ID=[strTEST];

５．VBAでtxtTESTのChangeイベントに以下のコードを記述
宣言部
Dim strTEST As String

関数部
Private Sub txtTEST_Change()
strTEST = txtTEST.Text
lstTEST.Requery
End Sub

期待する動作は、テキストボックスに1と入力したらリストボックスに「あああ」が表示される。という動作なのですが、テキストボックスに値を入力すると、「パラメータの入力」ダイアログが表示され、strTESTの値を入力するよう促されてしまいます。

恐らく、txtTEST_Change()関数でstrTESTにはテキストボックスの入力値が入っていますが、手順４の条件句のところで、strTESTの値が取れてないのだと思います。この条件句のところでVBAの変数を参照するにはどのように記述すればよいのでしょうか？

尚、上記のやり方はAccess2003及び2007でテストしてダメでしたが、Access97では動作するようなのです。

他の手法で同様の動作を実現する方法はいくつか思いつくのですが、Accessのバージョンアップによる作業を減らすため、上記のような方法によって、この機能を実現させたいと考えています。

よろしくお願いします。

回答No.5

kosamon

#2です。
ご質問の内容を少し読み違えてました。

リストボックス自体に
＞RowSourceプロパティの値
を設定されているのですね。
これについては私自身経験ありませんので明確な解決方法を
提示することができません。

http://www.accessclub.jp/

こちらで変数についてお調べになってみたらどうでしょう？

回答No.4

beppo

> テキストボックスに値を入力すると、「パラメータの入力」ダイアログが
> 表示され、strTESTの値を入力するよう促されてしまいます。

SQLの挙動について、誤解されているものと思います。

Access(JETデータベースエンジン)では、SQL中に
[]（半角大カッコ）で囲まれた文字列は、

　・列名
　・パラメータクエリ

いずれかに解釈されます。

同じ書式の　[DATA]　ではダイアログボックスが表示されないのは、
前者の列名との解釈が成立しているためです。
（テーブル内に同一の名称の列が存在するので）

対して、　[strTEST]　は該当する列が存在しないため、
後者のパラメータクエリと解釈され、
対話的な処理を行うために、JETがダイアログを表示しているのです。

※ダイアログボックスに、strTESTという文字列が表示されているはずですが、　それはstrTESTというパラメータに何を指定するか？とJETが聞いている状態です。

よって、対応としては、

　・lstTESTのRowSourceプロパティは、
　　（GUIで設定するのではなく）VBAでの制御に修正

　　⇒　txtTEST_Change()イベントの度にRowSourceを書き換える

　・SQL文を修正

の２点となります。

※プラス、Form_Load()でRowSourceにブランク設定、も必要なので３点かな？

コード内でのSQL記述は以下の通り。

| Private Sub txtTEST_Change()
|
| 　　Dim strSQL As String
| 　　Dim strTEST As String
|
| 　　strTEST = txtTEST.Text
|
| 　　strSQL = "SELECT A.DATA FROM tblTEST AS A "
| 　　strSQL = strSQL & "WHERE A.ID = '" & txtTEST & "'"
|
| 　　lstTEST.RowSource = strSQL
|
| 　　lstTEST.Requery
|
| End Sub

で、動くはずです…あくまではず、ですが^_^;

> Access2003及び2007でテストしてダメでしたが、
> Access97では動作するようなのです。

うーん、、動くというのが不思議ですね。。

97と2000以降との仕様の違い、といってしまえばそれまでですが・・・。

ひょっとして、97（既存ですよね？）では、

　SELECT ～ WHERE ID=[txtTEST]

とオブジェクト指定になっている、とかいうイージーな話ではないですよね？

ここは答えが出せませんが、上記のような作りが一般的なものだと思います。

※DLookupも立派な解決策のひとつですが、
　Accessの方言ということで自分は避けています。
　更なるバージョンアップを見据えて、上記のように制御するのが、
　最もオーソドックスな方法だと考えています。

以上、ご参考まで。

補足

ご回答ありがとうございます。
大変参考になりました。

＞ひょっとして、97（既存ですよね？）では、
＞　SELECT ～ WHERE ID=[txtTEST]
＞とオブジェクト指定になっている、とかいうイージーな話ではないですよね？
ここは、VBA側の変数を見ているのは間違いないと思います。

ここに載せたサンプルを非常に単純化してしまったがために、
逆に質問の意図を伝えにくくしてしまったようで申し訳ありません。

実際は、テキストボックスに入力された値をキーにSOAPで別システムと
やり取りをして、返ってきた値をVBA側の変数（サンプルではstrTESTにあたる変数）に
格納し、その変数をキーにリスト表示させています。
SQLももっと複雑なのですが、「SQLはリストボックスのRowSourceに記述されており、
変数名を[]で囲んで参照している」という箇所は変わりません。
・・・と、思っていたのですが、皆さんの回答を頂いてちょっとその辺に
自信がなくなってきました^^;;
#3の方の回答にも書いたとおり、もしかしたらぼくが既存の方式を見間違えて
いたのかもしれないので、もう一度、その方式を確認してみます。

ありがとうございます。

回答No.3

Dxak

＃１です

DLookup 関数
http://office.microsoft.com/ja-jp/access/HA012288251041.aspx

にある通りだよ

リストボックスに、値が入ってるから・・・
それを元に
DLookup("DATA","tblTEST","ID = " & Me.lstTEST)
と、いう感じ

> strTESTの値が取れてないのだと思います。

これは、パラメータと変数を混同していると言う話だと思うから、VBAでパラメータ値を変更するのは・・・どうしたかは＾＾；

普通は、「frmTSET」のレコードソース上で「lstTEST」の値を参照できるように作成するのが通常だけどね

補足

Dlookup関数の使い方分かりました。ありがとうございました。

＞> strTESTの値が取れてないのだと思います。
＞これは、パラメータと変数を混同していると言う話だと思うから、
現在Access97では動作しているものが、このような方式であるように
思えたのですが、もしかしたらその方式を見間違えていたのかもしれません。

まずは、既存の方式を再確認してみたいと思います。
ありがとうございます。

回答No.2

kosamon

>テキストボックスに入力した値をVBAで加工して
この件が良く解りませんが、テキストボックスに入力した値を
抽出条件にしたいと言うことでしょうか？

だとすれば・・・という前提で。

テキストボックスの更新後処理に
me.テキストボックス名.RowSource = "SELECT * FROM テーブル名 WHERE ID=" & Me.テキストボックス名

こんな感じかな。

>、「パラメータの入力」ダイアログが表示され、strTESTの値を入力するよう促されてしまいます。
>strTESTの値が取れてないのだと

データ型の違いだと思われます。
抽出するフィールドのデータ型が提示されたSQLを見る限り数値であると思われるのに、なんでわざわざ文字列に変換させてるのですか？
正しいデータ型を指定しましょう。

あくまでも変数に拘りたいというならば別の方法もありますが、まずは
データ型についての記述が無いため、そのあたりをはっきりお書きになってからという事で・・・。

補足

質問の書き方が悪かったようです。申し訳ありません。

＞あくまでも変数に拘りたいというならば別の方法もありますが
変数に拘りたいのです。
というのも、方式的にその方法が良いと考えているわけではなく、
現在Access97用に作られているものがその方式（変数を使った方式）を取っており、
それをAccess200xで動かすようにしたいのですが、同じような処理が
大量にあるため、VBAをすべて書き換えるのは大変なので、
可能であれば、RowSourceのところの書き換えだけで、なんとか
Access97と同様の動作を実現できないかと思っています。

また、データ型の件ですが、ためしにInteger型などに変更してみましたが、
結果は同様でした。

回答No.1

Dxak

リストボックスから、何とかしようとするより、ソースへ値から、DLookup関数で取得すると言うのが普通だと思うけど？

補足

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

DLookup関数について調べてみたのですが、使用方法がいまいち分かりません。この関数を使うと、リストボックスのプロパティからVBAの変数を参照できるようになるのでしょうか？
質問に記載したサンプルに適用するには具体的にどうすればよいのでしょうか？

質問ばかりで申し訳ありませんが、よろしくお願いいたします。