こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

Access2000でVBAからパラメータクエリを実行

Access2000でフォームからの値を参照して実行するクエリを作りました。
SQLで表すと、以下のようなものです。

クエリ名:test
SELECT TEST.HAKKODTE FROM TEST
WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd")));

このクエリをダブルクリックで実行すると問題なく実行できます。


これを、VBAからレコードを参照したいと思い、
以下のように記述しました。

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

Dim sqlstr As String

sqlstr = "SELECT * FROM test;"

rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic


しかし、Open処理で、「1つ以上の必要なパラメータの値が設定されていません」
というエラーになってしまいます。

VBAを実行するときに、[Forms]![F_メニュー]![txtNyukinDate]に
きちんと値は入力されています。

これを実行する方法はあるでしょうか?

投稿日時 - 2006-08-30 22:46:34

QNo.2373380

困ってます

質問者が選んだベストアンサー

すみません、No.6です。補足です。

当方のテストでは "yyyy/mm/dd" を使いましたが、
テーブルの「HAKKODTE」列のデータ型によっては
違うと思います。


自作関数の
Function date_return() As Date
も、
takuchantikuさんの環境では
Function date_return() As String
が正解かもしれません。
(フォールドのデータ型のご提示が無いので適当にテストしました。)

ご自分の環境に合わせて適当に読み替えてください。
上記はあくまでもテストなので、適当です。


言いたかった事は…、
パラメータクエリをレコードセットして開きたい時は…。

・フォームの値を自作関数を使って(自作関数を経由させて)
クエリの抽出条件に(言わば間接的に)セットし、
・そのクエリをVBAで、"まんま"で実行する

というやり方です。

説明不足ですみませんでした。

投稿日時 - 2006-09-01 00:53:40

お礼

回答ありがとうございます。
自作関数を作成し、クエリに
=date_return()
と組み込む、という方法は大変参考になりました。
活用させてもらいます。

投稿日時 - 2006-09-01 01:37:15

ANo.7

このQ&Aは役に立ちましたか?

3人が「このQ&Aが役に立った」と投票しています

回答(7)

ANo.6

(01)標準モジュールに、例えば以下のようなフォームの値をGETして返すだけの関数を書きます。

Function date_return() As Date

date_return = Format([Forms]![F_メニュー]![txtNyukinDate], "yyyy/mm/dd")

End Function


(02)クエリの「HAKKODTE」列の抽出条件に(01)の関数を組み込みます。

=date_return()

と書きます。

クエリのSQLは例えばこんな感じになります。

SELECT TEST.a, TEST.HAKKODTE
FROM TEST
WHERE (((TEST.HAKKODTE)=date_return()));

クエリ名を「q_test01」とします。

(03)標準モジュールに以下のコードを書きます。
(クエリ名をかえて最後の行を足しただけです)

Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection

Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

Dim sqlstr As String

sqlstr = "SELECT * FROM q_test01;"

rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

Debug.Print rs1.Fields(0) & "-----" & rs1.Fields(1)

こういう感じで当方ではエラーが無く通っていますが…
イミディエイトにも抽出(ヒット)したレコードの値が表示されます。

なお、sqlstr = "SELECT * FROM q_test01;"

のところは

sqlstr = "SELECT * FROM クエリ名;"

という感じです。

私はいつも、フォームの値を自作関数でクエリに渡して、
クエリをVBAで実行してます。
簡単に無理やりクエリを実行するなら自作関数を使うしか方法は
無いのではないでしょうか?


なお、ご質問では、

>クエリ名:test
>SELECT TEST.HAKKODTE FROM TEST
>WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd")));

ということで、TESTというテーブルを同じくTESTというクエリにて
開こうとしていますが、通常、テーブル名とクエリ名などで
同じ名前を使おうとすると、エラーが出て作れないのではないでしょうか?
(つまりありえない)

だとすれば、

sqlstr = "SELECT * FROM test;"

も少々おかいしような…

これだったら、クエリ名が「TEST」以外の名前なら、
テーブル「TEST」のすべてがレコードセットになるだけで
エラーにはならないはず・・・

クエリ名は本当は「TEST」テーブルと違う名前を使っているのでは
ないでしょうか?

テーブル名、フィールド名、データ型、
クエリ名
などを

もういちどしっかりとご提示されてはどうでしょうか?


違ってたらすみません。

投稿日時 - 2006-09-01 00:38:28

お礼

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

>クエリ名は本当は「TEST」テーブルと違う名前を使っ>ているのでは
>ないでしょうか?
はい、その通りです。
間違っていました。
申し訳ありませんでした。

投稿日時 - 2006-09-01 01:34:33

ANo.5

単純に
DoCmd.OpenQuery
ではダメなんですか?

投稿日時 - 2006-08-31 17:06:12

お礼

回答ありがとうございます。
今回は、VBAでレコードセットを使い、いろいろ編集したいので、
DoCmd.OpenQuery
では、だめ、、、だと思ったんですが、
対象のクエリを、テーブル作成クエリにし、
VBAにて、DoCmd.OpenQueryで実行し、
作成されたテーブルを参照することで、回避することができました。

投稿日時 - 2006-09-01 01:32:04

ANo.4

s_husky です。

チクッと XferExecuteSQL() を作ってみました。

? dblookup(xferexecutesql(getQuerysql("クエリ1"),0))
1;A1;2006/08/01;10:00:00;

と、まあ、成功しています。

Public Function XferExecuteSQL(ByVal strQuerySQL As String, Optional fldType As Integer = 1) As String
  Dim I    As Integer
  Dim J    As Integer
  Dim L    As Integer
  Dim P    As Integer
  Dim C    As String
  Dim V    As String
  Dim frmName As String
  Dim fldName As String
  
  L = Len(strQuerySQL)
  P = InStr(1, strQuerySQL, "Forms!", vbTextCompare) + 6
  For I = P To L
    C = Mid$(strQuerySQL, I, 1)
    If C = "!" Then
      J = 1
    ElseIf C = ")" Then
      Exit For
    Else
      If J = 0 Then
        frmName = frmName & C
      Else
        fldName = fldName & C
      End If
    End If
  Next I
  V = Forms(frmName).Controls(fldName)
  If fldType = 1 Then
    V = "'" & V & "'"
  ElseIf fldType = 2 Then
    V = "#" & V & "#"
  End If
  XferExecuteSQL = CutStr(strQuerySQL, "Forms!" & frmName & "!" & fldName, 1) & _
           V & _
           CutStr(strQuerySQL, "Forms!" & frmName & "!" & fldName, 2)
End Function

Public Function CutStr(ByVal Text As String, _
            ByVal Separator As String, _
            ByVal N As Integer) As String
  Dim strDatas() As String
  
  strDatas = Split("" & Separator & Text, Separator, , 0)
  CutStr = strDatas(N * Abs((N <= UBound(strDatas))))
End Function

なお、GetQuerySQL()に綴りの間違いがあったので正しておきます。

Public Function GetQuerySQL(ByVal QName As String) As String
  Dim dbsCurrent As DAO.Database
  Dim qdfQuery  As QueryDef
  Dim strQuerySQL As String
  
  Set dbsCurrent = CurrentDb
  Set qdfQuery = dbsCurrent.QueryDefs(QName)
  GetQuerySQL = qdfQuery.SQL
  qdfQuery.Close
  dbsCurrent.Close
End Function

XferExecuteSQL()は、パラメータの種類が一つです。
また、パラメータが数字、文字列、日付なのかを指示する必要があります。
XferExecuteSQL()を汎用化するには、幾つかのテストを重ねる必要があるでしょう。

※かように、面倒臭いプログラミングが果たして常道なのか否か?
※多少、疑問に思います。

投稿日時 - 2006-08-31 10:18:29

お礼

回答ありがとうございます。
今回は、上記2つの方法どちらかにしたいと思います。
実際に関数を作っていただきありがとうございました。今後の参考にしたいと思います。

投稿日時 - 2006-09-01 01:28:04

ANo.3

実行する方法はあります。
が、専用の関数を開発する必要があります。

まず、その理由を確認出来る関数を示します。

Public Function GetQurySQL(ByVal QName As String) As String
  Dim dbsCurrent As DAO.Database
  Dim qdfQuery  As QueryDef
  Dim strQuerySQL As String
  
  Set dbsCurrent = CurrentDb
  Set qdfQuery = dbsCurrent.QueryDefs(QName)
  GetQurySQL = qdfQuery.SQL
  qdfQuery.Close
  dbsCurrent.Close
End Function

? GetQurySQL("クエリ1")
SELECT Table1.*, *
FROM Table1
WHERE (((Table1.ID)=[Forms]![フォーム1]![ID]));

このように、GetQurySQL関数でクエリのSQL文を参照すると、[Forms]![フォーム1]![ID]が置換されていません。
これがエラーの原因です。

ということは、

DBLookup(XferExecuteSQL(GetQuerySQL("クエリ1")))

で参照可になるということです。

XferExecuteSQL関数は、[Forms]![フォーム1]![ID]の置換を行う関数です。

ここまでで、質問者は、XferExecuteSQL関数の作成が課題であることが判ったと思います。
質問の的をそこに絞って再質問すれば即答えが集中するでしょう。

Public Function DBLookup(ByVal strQuerySQL As String) As String
On Error GoTo Err_DBLookup
  Dim I   As Integer
  Dim N   As Integer
  Dim Datas As String
  Dim dbs  As DAO.Database
  Dim rst  As DAO.Recordset
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strQuerySQL)
  With rst
    Do Until .EOF
      N = .Fields.Count - 1
      For I = 0 To N
        Datas = Datas & .Fields(I) & ";"
      Next I
      .MoveNext
    Loop
  End With
Exit_DBLookup:
On Error Resume Next
  rst.Close
  dbs.Close
  DBLookup = Datas
  Exit Function
Err_DBLookup:
  MsgBox Err.Description
  Resume Exit_DBLookup
End Function

投稿日時 - 2006-08-31 09:25:39

ANo.2

クエリーをかまさずにやってみてはどうですか。
「1つ以上の必要なパラメータの値が設定されていません」はOpenメソッドを実行したときに
テーブル名やフィールド名が正しくないときにでるようなので、もしかしたらtxtNyukinDateが上手く参照できていないかもしれません。

sqlstr = "SELECT TEST.HAKKODTE FROM TEST WHERE TEST.HAKKODTE = '" & Format(Forms!F_メニュー.txtNyukinDate,"yyyymmdd") & "'"
rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic

現在自分で検証できないため、F_メニューのtxtNyukinDateの参照方法は間違っているかもしれません。

投稿日時 - 2006-08-30 23:50:17

お礼

引き続きありがとうございます。
クエリをかまさずに、べたでSQLを記述すると、問題なく実行できました。

しかし、コードで記述するのは大変(同じようなものが複数ある)ので、
できれば、クエリから実行したいと思っています。

もう少し調査してみます。

投稿日時 - 2006-08-31 00:00:33

ANo.1

sqlstr = "SELECT * FROM test;"
rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic
を、
Set rs1 = cn.Execute("SELECT * FROM test")
に置き換えてもだめですかね。

投稿日時 - 2006-08-30 23:03:26

お礼

回答ありがとうございます。
置き換えてみましたが、やはり同じエラーになってしまいました。。。

投稿日時 - 2006-08-30 23:38:55

あなたにオススメの質問