ACCESS VBA クエリを開く

質問

ACCESS初心者です。
VBAはEXCELで独学した程度の知識です。

フォームから受注納期を入力し、クエリを抽出。
クエリでレコード毎に製品票の印刷枚数を計算し
レポートで各レコード毎に求められた印刷枚数分を
印刷させたいのですが・・・

クエリを開く段階でつまずいており困っています。

Private Sub コマンド23_Click()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb()
Set qdf = db.QueryDefs("受注クエリ")

With qdf
.Parameters("納期") = Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")
Set rs = .OpenRecordset
.Close
End With

MsgBox rs![注文番号]

rs.Close

End Sub

パラメータクエリの開き方、上記コードで問題ないでしょうか？

そしてこのコードを実行した際
実行時エラー3421
データ型の変換エラーが発生しましたと表示されます。

ローカルウィンドウで 変数rs = nothingとなっており
クエリのレコードが読み込めていないようです。
(Msgbox rs!注文番号はデータが読み込めたかテストするために
コードを書いています。)

どうぞ宜しくお願いいたします。

回答No.8

piroin654 · ベストアンサー

ですから、

＞など設定がばらばらなのでQueryDefを使ってパラメータを
＞VBAで設定するならば、他の方への補足の内容をもとにすると、
＞SQL文を、

として、補足にあるパラメータの宣言、

＞PARAMETERS 納期1 DateTime;

をはずしたクエリのSQL文を提案し、VBAでは、

＞With qdf
＞.Parameters("納期") = Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")
＞Set rs = .OpenRecordset
＞.Close
＞End With

のところで、

With qdf
qdf.Parameters("XYZ") = CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
Set rs = .OpenRecordset
End With

のように、Parameters("納期")　を　Parameters("XYZ")　のように
パラメータが「納期」というようなフィールド名と同じになるのを
避けるために「XYZ」としているのですが・・・。パラメータと
フィールド名を同じにするとエラーが出るのでこのようにしています。

補足にある、

＞フォームからyyyy/mm/ddの書式で日付を指定し該当する
＞レコードをクエリで抽出します。

ということは、フォームではテキストボックスの書式が
日付型に設定してあると、いうことだと解釈します。したがって、
VBAでは、
.Parameters("XYZ") = Forms![受注データ抽出]![date1]
とします。

＞上記記述で
＞実行時エラー3061
＞パラメーターが少なすぎます。2を指定してください。
＞エラーが発生します。

これは、補足にある書き変えたVBAの中で、

＞With qdf
＞.Parameters("納期1") = Forms![受注データ抽出]![date1]
＞Set rs = db.OpenRecordset("確定クエリ", dbOpenDynaset)
＞End With

のところで、
db.OpenRecordset
としているために、「確定クエリ」をパラメータを指定せずに
レコードセットとして接続しようとしているためにエラーが
エラーがでるのです。したがって、ここのところは、もともとの
設定のようにQueryDefを使って、

With qdf
.Parameters([XYZ]) = Forms![受注データ抽出]![date1]
Set rs = .OpenRecordset("確定クエリ")
End With

とします。ようするに、
Set rs = qdf.OpenRecordset("確定クエリ")
ということです。

以上より、クエリの「納期1」の抽出条件に「[XYZ]」 (XYZではなく、[XYZ]です）と記入し、
そのクエリのSQL文は、

SELECT 確定.注文番号, 確定.注文年月日, 確定.発注者品名コード, 確定.[品名(品名仕様)], 確定.[注文数量(受注数量)], 社内データ.SNP, 確定.単位, 確定.納期1, 確定.納入No1, 確定.納品キー番号1, 確定.受渡場所名, 確定.発注者用備考, 確定.発注者名, Int([納入指示数量1]/[SNP]+0.5) AS 印刷枚数
FROM 確定 LEFT JOIN 社内データ ON 確定.発注者品名コード = 社内データ.得意先品目コード
WHERE (((確定.納期1)=[XYZ]));

VBAは、

Private Sub コマンド23_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("確定クエリ")

With qdf
.Parameters("XYZ") = Forms![受注データ抽出]![date1]
Set rs = .OpenRecordset
End With

MsgBox rs![注文番号]

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

お礼

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

上記コードを試してみたところ無事にクエリを開くことができました。
ご回答いただいたコードを参考に
.Parameters("Forms![受注データ抽出]![date1]") = Forms![受注データ抽出]![date1]
とし、同様の結果を得ることができました。

これまでのコードミスと原因が理解できました。
当初のご回答、理解不足で誠に申し訳ありません。

またコードの掲載に際して修正により混乱を招いて
しまい申し訳ありませんでした。

ご回答いただいた他ご回答者様へもこの場をお借りして
お詫び申し上げます。

どうもありがとうございました。

回答No.7

bin-chan

#5ですが、あとからあとから訂正入るが、列名見直しした？
intの中身、納入指示数量1はドコにあるの？

テーブルの列名とデータ型も開示したほうが良くない？

補足

度々申し訳ありません。

納入指示数量1⇒注文数量(受注数量)の誤りでした。

フィールド名、データ型は下記の通りです。

確定テーブルより
注文番号　　　　　[テキスト型]
注文年月日　　　　[日付/時刻型]
発注者品名コード　[テキスト型]
品名(品名仕様)　　[テキスト型]
注文数量(受注数量)[数値型]
単位　　　　　　　[テキスト型]
納期1　　　　　　 [日付/時刻型]
納入No1　　　　　 [テキスト型]
納品キー番号1　　 [テキスト型]
受渡場所名　　　　[テキスト型]
発注者用備考　　　[テキスト型]
発注者名　　　　　[テキスト型]

社内データテーブルより
SNP　　　　　　　 [数値型]
得意先品目コード　[テキスト型]

クエリ　計算式による新規フィールド
印刷枚数 　　　　 [数値型]

回答No.6

piroin654

No3です。
まず、前提として「納期」が日付/時刻型であるとしています。
そうでないならば、テーブルの設定を日付/時刻型にしないと、
変換する意味がありませんが。No3の型が一致しないという
エラーもひょっとして「納期」のフィールドの型が
日付/時刻型以外の型になっていませんか。

>PARAMETERS 納期1 DateTime;
とは、あるフィールドに設定した[納期1]という
抽出条件が、日付/時刻型であるということなのに、
VBAにおいては、
>.Parameters("納期") = Format(Forms![受注データ一覧]![納期],
のように[納期]というフィールド名と同じパラメータ名を
設定していたり・・・・・
など設定がばらばらなのでQueryDefを使ってパラメータを
VBAで設定するならば、他の方への補足の内容をもとにすると、
SQL文を、

SELECT
受注.注文番号, 受注.注文年月日, 受注.品名コード, 受注.[品名(仕様)], 受注.[注文数量(数量)], 参照データ.SNP, 受注.単位, 受注.納期, 受注.納品No, 受注.発注者, Int([注文数量(量)]/[SNP]+0.5)
AS 印刷枚数
FROM 受注 LEFT JOIN 参照データ ON 受注.品名コード = 参照データ.品目コード
WHERE 受注.納期=[XYZ];

として、行儀良く後始末をするためにVBAを、

Private Sub コマンド23_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("受注クエリ")

With qdf
qdf.Parameters("XYZ") = CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
Set rs = .OpenRecordset
End With

MsgBox rs![注文番号]

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

補足の中のSQL文には、「納期1」、「受注.納期1」「受注.納期」
など、意味不明のフィールド名が出てくるので、「受注」テーブル
には、「納期」というフィールドが存在していて、「納期1」という
フィールドは存在しないものとします。

なお、「受注データ一覧」というフォームの「納期」という
(たぶん)テキストボックスに入力するのは、たとえば、
「20140115」のような数値型あるいはテキスト型の
文字列という前提にしています。そのために
CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))
のような型変換をしています。

これでエラーが出るようであれば他の問題があるのでは？

お礼

補足を誤って確定してしまいました。

下記にSQLコードとVBAコードを記載します。

PARAMETERS 納期1 DateTime;
SELECT 確定.注文番号, 確定.注文年月日, 確定.発注者品名コード, 確定.[品名(品名仕様)], 確定.[注文数量(受注数量)], 社内データ.SNP, 確定.単位, 確定.納期1, 確定.納入No1, 確定.納品キー番号1, 確定.受渡場所名, 確定.発注者用備考, 確定.発注者名, Int([納入指示数量1]/[SNP]+0.5) AS 印刷枚数
FROM 確定 LEFT JOIN 社内データ ON 確定.発注者品名コード = 社内データ.得意先品目コード
WHERE (((確定.納期1)=[Forms]![受注データ抽出]![date1]));

Private Sub コマンド23_Click()

Dim db As DAO.database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.QueryDefs("確定クエリ")

With qdf

.Parameters("納期1") = Forms![受注データ抽出]![date1]
Set rs = db.OpenRecordset("確定クエリ", dbOpenDynaset)

End With

MsgBox rs!注文番号

qdf.Close: Set qdf = Nothing
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub

上記記述で
実行時エラー3061
パラメーターが少なすぎます。2を指定してください。
エラーが発生します。

ただ他にパラメータを設定しているフィールドがありません。
やはり他に誤った記述があるのでしょうか・・・

補足

申し訳ありません、得意先の名前がわかるテーブルや
フィールドがあった為SQLを手直しして掲載した為
誤ったコードになってしまいました。

改めてクエリを作成、フィールド名も確認しました。
フォームからyyyy/mm/ddの書式で日付を指定し該当する
レコードをクエリで抽出します。
フィールドの納期1については日付の書式に設定されていたので
Parameterのコードを書き換えました。

回答No.5

bin-chan

VBAを介さないクエリで動かしてみるのが良さそう。

「パラメータを要求される」とは、SQL文に出てくる項目がFromで指定された
テーブル・クエリに存在しないから「どうすれば良いの？」と問われることです。

whereの左辺、なぜ項目末尾に１がある？投稿のためのコピペミスじゃ無いでしょ。
一度、すべての列名を見直しされると良いと思います。

回答No.4

bin-chan

Endwithの前行でCLOSEしちゃダメでしょ。

「受注クエリ」が２つ目のパラメータを要求してるのかな？
SQLビューで開いてコピペで開示できますか？

補足

.close　失礼しました。
新規で空のデータベースに適当なデータを入力し
(パラメータの設定は無し)
クエリをRecordsetで開いたところ問題なく開けました。

下記にSQLコードを記述します。

PARAMETERS 納期1 DateTime;
SELECT
受注.注文番号, 受注.注文年月日, 受注.品名コード, 受注.[品名(仕様)], 受注.[注文数量(数量)], 参照データ.SNP, 受注.単位, 受注.納期, 受注.納品No, 受注.発注者, Int([注文数量(量)]/[SNP]+0.5) AS 印刷枚数
FROM 受注 LEFT JOIN 参照データ ON 受注.品名コード = 参照データ.品目コード
WHERE (((受注.納期1)=[Forms]![受注データ一覧]![納期]));

やはり問題は納期のパラメータでしょうか？
フィールド印刷枚数には計算結果のデータを入れています。
テーブルは同フォルダにcsvファイルを落とし込み
リンクテーブルを作成しています。

回答No.3

piroin654

＞Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd")

を

CDate(Format(Forms![受注データ一覧]![納期], "@@@@/@@/@@"))

補足

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

ご指摘のコードを試してみた結果ですが
別のエラーが発生してしまいました。

実行時エラー13
型が一致しません。

回答No.2

mike32

動作確認したわけではありませんが、想像での話しです。

パラメータ「納期」は日付型フィールドでしょうか？

Format()関数の戻り値はString型ですので、その値をCdate()関数を使用して日付型に
変換してから、パラメータにセットしたらどうでしょうか？

参考URL：

http://office.microsoft.com/ja-jp/access-help/HA001229018.aspx

補足

回答ありがとうございます。
早速CDate関数を使用してみました。

CDate(Format(Forms![受注データ一覧]![納期], "yyyy/mm/dd"))

先の質問のエラー表示ではなく
実行時エラー3061
パラメーターが少なすぎます。2を指定してください。
とのエラーメッセージ。

デバッグを開くと

Set rs = .OpenRecordset　部分がマーキングされ
rs = nothing
やはりクエリが開けていない様子でした。