アクセスVBA TOP値を変数

質問

フォーム内テキストボックスの数値を、SELECT TOP の変数にしたくて、コマンドボタンを押した時に、下記VBAを書いたところ、実行時エラー7874　と出て、下から２行目でエラーとなりました。

Private Sub command1_Click()
Dim 抽出数 As Integer
Dim mySQL As String

抽出数 = Me![text1]
mySQL = "SELECT * FROM T_1 AS Q_TEMP WHERE 顧客CD in (SELECT TOP " & 抽出数 & " 顧客CD FROM T_1 WHERE 支店CD=Q_TEMP.支店CD ORDER BY 売上金額 DESC,顧客CD) ORDER BY 支店CD, 売上金額 DESC , 顧客CD;"

DoCmd.OpenQuery mySQL
End Sub

やりたい事は、テーブル：T_1に下記レコードがあり、各支店ごとのトップ売上金額２を抽出したいのと、トップ２を変数にしたいのです。

支店CD　顧客CD　売上金額
1 11 1,000
1 12 2,000
1 13 3,000
1 14 4,000
2 15 2,000
2 16 3,000
2 17 4,000
2 18 5,000
3 19 5,000
3 20 5,000
3 21 5,000
3 22 5,000

を、上記ＳＱＬ文で、下記の結果にしたい

支店CD　顧客CD　売上金額
1 13 3,000
1 14 4,000
2 17 4,000
2 18 5,000
3 19 5,000
3 20 5,000
(売上金額が同じなら、顧客CDの小さい方から２つ)

どこが間違っているか教えてもらえますか？

回答No.1

DexMachina · ベストアンサー

> どこが間違っているか教えてもらえますか？

「DoCmd.OpenQuery」は、保存されたクエリなら実行できますが、
SQL文を実行することはできません。

「DoCmd.OpenQuery」に相当する形で、SQL文を実行するもの
としては、
　　a)DoCmd.RunSQL mySQL
　　b)CurrentDb.Execute mySQL
といった方法がありますが、これはアクションクエリ(追加/削除など)
に相当するSQL文に限定され、データシートとして結果を表示する
Select文には適用できません。

ですので、結果を表示させたい場合は、
　a)新規クエリをその場で作成するか、予めクエリを用意しておき、
　　そのクエリのSQLプロパティに、作成したSQL文を設定する
　b)「T_1」テーブルのすべてのフィールドに対応したコントロールを
　　設置したフォームを用意しておき、作成したSQL文をフォームの
　　RecordSourceに設定する
といった方法をとるしかないかと思います。

下記コードは、提示されたコードから「DoCmd.OpenQuery mySQL」
の一行を削除して、代わりに追加するコードになります。
(Dim/Constの宣言部分は、他の宣言の直下でOk)

【予め「Q_1」という名前のクエリを用意しておくパターン】
※「Q_1」にはテーブルもフィールドも何もなしで、とにかくその名前で
　 保存だけしておけばOkです。
　 (クエリの中身(→レコードではなく)は、下記コードを実行すると
　　上書されます)

　　'「Microsoft DAO #.# Object Library」への参照設定が必要
　　'(VBEのメニューで「ツール(T)→参照設定(R)」を選択してチェック)
　　Dim Qdf As DAO.QueryDef
　　Const cQry As String = "Q_1"

　　Set Qdf = CurrentDb.QueryDefs(cQry)
　　Qdf.SQL = mySQL
　　Qdf.Close
　　Set Qdf = Nothing
　　DoCmd.OpenQuery cQry

【予め「F_1」という名前のフォームを用意しておくパターン】
※「T_1」の全フィールドを表示するためのコントロールの設置が必要。
　(フィールド数に見合った数のコントロールの設置と、コントロールソース
　　の指定：
　　「T_1」テーブルをレコードソースとしてフォームを作成するのが無難)

　　Const cFrm As String = "F_1"

　　DoCmd.OpenForm cFrm, acFormDS
　　Forms(cFrm).RecordSource = mySQL

・・・以上です。

事前準備が多く必要になりますが、VBAによる編集の制御などが、より柔軟に
できることから、フォームを使用する方がお勧めとなります。

お礼

親切にご教示頂き大変感謝します。
パターンa) でできました。ありがとうございます。
Docmd.OpenQuery は、保存されたクエリを実行するコマンドなんですね。勉強になりました。

お勧めのパターンb) もやってみましたが、ダメでした。フォームF_1のプロパティ→データTab→レコードソースをT_1にしてみましたが、

>※「T_1」の全フィールドを表示するためのコントロールの設置が必要。
　(フィールド数に見合った数のコントロールの設置と、コントロールソース
　　の指定：

が理解できず…　　　是非できるようになりたいので、もう少し教えてもらえますか？

>'「Microsoft DAO #.# Object Library」への参照設定が必要
　　'(VBEのメニューで「ツール(T)→参照設定(R)」を選択してチェック)

は、チェックを入れました。DAO 3.6 Object Library　です。

回答No.2

DexMachina

> 是非できるようになりたいので、もう少し教えてもらえますか？

了解しました。

では、以前のご質問からするとご使用のAccessは2003のようですので、
そのバージョンでの操作手順を説明します。
http://okwave.jp/qa/q7906336.html

・・・といっても、前回の回答があまりに漠然としすぎていて不安にさせて
しまったかもしれませんが(汗)、それほど複雑な操作は必要ありません。

特に、今回の場合はデータシート型で、テーブルの全フィールドを表示
させる形なので、以下の操作であっさりできてしまいます：

1)Accessのメニューから「挿入(I)→フォーム(F)」をクリック
　(又はデータベースウィンドウ(フォーム等の一覧が表示される画面)で、
　　『フォーム』を選択して『新規作成(N)』ボタンをクリック、でもOk)
2)『新しいフォーム』ダイアログが開くので、リストボックスで「オートフォーム:
　データシート」を、『基になるテーブル又はクエリの選択』で「T_1」を
　それぞれ選択後、『OK』ボタンをクリック
3)「T_1」テーブルの全フィールド・全レコードが、データシート型フォーム
　として表示される

・・・以上です。

見た目は「T_1」テーブルと同じかと思いますが、Accessのメニューから
「表示(V)→デザイン ビュー(D)」を選択すると、このフォームのデザイン
ビューが表示されます。

そこに表示されるテキストボックス(背景色が白の四角い枠)などが、
前回回答の『全フィールドを表示するためのコントロール』になります。
(『コントロール』というのは、テキストボックスや、コマンドボタン、チェック
　ボックスなどの総称です)

また、これらのコントロールをダブルクリックするとそのコントロールの
プロパティシートが表示されますが、その中の『データ』タブに、
『コントロールソース』という項目があります。
(ラベルなど、テーブルのデータに連動しないタイプのコントロールには
　この項目はありません)

各コントロールには、この『コントロールソース』として指定したフィールド
の値が表示されるようになっています。
(『フィールド』は、テーブルやクエリなどの縦列のことです)

なお、データシートビューでは、テーブルやクエリと同様の表示となるため
デザインビューでのレイアウトは特にデータ表示には影響しませんが、
メニューで「表示(V)→フォーム ビュー(F)」を選択した場合は、デザイン
ビューのレイアウトが、データ表示の際にも反映されるようになります。

フォームビュー(単票フォームや帳票フォームなどがあります)で表示させる
場合は、必要に応じて、デザインビュー上でコントロールをドラッグしたり、
プロパティシート上で設定値を変えるなどして、デザインやレイアウトを
変更します。

フォームを開いた時に最初にどの表示のさせ方にするかは、フォームの
プロパティシートの『表示』タブの、『既定のビュー』の項目で指定します。
(フォームのプロパティシートは、プロパティシートの最上段にあるコンボ
　ボックスで「フォーム」を選択して表示させることができる他、フォームの
　(マス目を含めて)何もないところをクリックして切り替えることもできます)

・・・以上、途中からとりとめがなくなってしまいましたが(汗)、参考まで。

お礼

引き続き丁寧に教えていただきありがとうございます。実はMe![text1]のMe!は、Forms![F_1]!だったので、それのレコードソースをT_1にしてました^^); 説明不足と勘違いですいませんm(_ _)m ご教示頂いたF_1をF_2にしてできました! 感激です!!

今回初めてVBAを書きましたが、マクロと違いもっとできる事が広がる予感がしました☆もっともっと勉強したいと思いました!

ホントにありがとうございました。感謝です!