问题描述
先生们：我有一段时间带着DAO走过了一个SQL动态集中的记录
。我正在尝试走一组由UNION查询返回的记录
。我正在尝试将记录过滤到与车辆＃60（[VehicleJobID] = 60）相关的
。如果我明确
在SQL中指定60 ==一切正常。看看：
100 PString =" SELECT [AddnlOwnrFName]& " &安培; Chr $（& H22）&空间（1）&
Chr $（& H22）& " &安培; [AddnlOwnrLName] AS Recipient
120 PString = PString& FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID"
140 PString = PString& WHERE tblVehicleJobs.VehicleJobID = 60; &
vbNewLine& vbNewLine
160 PString = PString& UNION SELECT [LienHolderName] AS收件人
180 PString = PString& FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID"
200 PString = PString& WHERE tblVehicleJobs.VehicleJobID = 60; &
vbNewLine& vbNewLine
220 PString = PString& UNION SELECT [OwnerFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [OwnerLName] AS收件人
240 PString = PString& " FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID"
260 PString = PString& WHERE tblVehicleJobs.VehicleJobID = 60; &
vbNewLine& vbNewLine
280 PString = PString& UNION SELECT
IIf（IsNull（[AuthCompany]），[AuthFName]&"& Chr $（& H22）& Space（1）&
Chr $（& H22）&"& [AuthLName]，[AuthCompany]）AS收件人
300 PString = PString& FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID"
320 PString = PString& WHERE（tblVehicleJobs.VehicleJobID = 60）AND
（tblAuth.AuthCatID）<> 2;" &安培; vbNewLine& vbNewLine
340 PString = PString& UNION SELECT [ProxyFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [ProxyLName] AS收件人
360 PString = PString& FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID"
380 PString = PString& WHERE tblVehicleJobs.VehicleJobID = 60;"
''在这里使用DAO来记录记录。
420 Dim MyDB As Database，MyRst As Recordset
440 Dim strSQL As String
460设置MyDB = CurrentDb
480设置MyRst = MyDB.OpenRecordset（PString，dbOpenForwardOnly）
500 MyRst.MoveFirst
520直到MyRst.EOF
540 POLAXstring = POLAXstring& MyRst！收件人& " &安培;
560 MyRst.MoveNext
580循环
600 MyRst.Close
620 MyDB.Close
上述效果非常好。永远不会出现故障。如果我曾经想要做什么
是在车辆ID＃60上运行 - 一切都会好的。但显然，这不是我想要的。我想用任何车辆提供SQL
我想要的ID。我已经尝试了GLOBAL变量赋值和GLOBAL
过程，它读取并返回其值。我已经尝试将
车辆ID号写入表单字段并在那里引用它。但我保持
得到错误。例如，如果我用这种方式写行100-380，我得到
一个错误，说GetCurrentVehicleJobID（）没有定义函数...
100 PString =" SELECT [AddnlOwnrFName]& " &安培; Chr $（& H22）&空间（1）&
Chr $（& H22）& " &安培; [AddnlOwnrLName] AS Recipient
120 PString = PString& FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID"
140 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
（GetCurrentVehicleJobID（））;" &安培; vbNewLine& vbNewLine
160 PString = PString& UNION SELECT [LienHolderName] AS收件人
180 PString = PString& FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID"
200 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
（GetCurrentVehicleJobID（））;" &安培; vbNewLine& vbNewLine
220 PString = PString& UNION SELECT [OwnerFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [OwnerLName] AS收件人
240 PString = PString& " FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID"
260 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
（GetCurrentVehicleJobID（））;" &安培; vbNewLine& vbNewLine
280 PString = PString& UNION SELECT
IIf（IsNull（[AuthCompany]），[AuthFName]&"& Chr $（& H22）& Space（1）&
Chr $（& H22）&"& [AuthLName]，[AuthCompany]）AS收件人
300 PString = PString& FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID"
320 PString = PString& WHERE（tblVehicleJobs.VehicleJobID =
（GetCurrentVehicleJobID（）））AND（tblAuth.AuthCatID）<> 2;" &安培; vbNewLine&
vbNewLine
340 PString = PString& UNION SELECT [ProxyFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [ProxyLName] AS收件人
360 PString = PString& FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID"
380 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
（GetCurrentVehicleJobID（））;"
如果我用这种方式写行100-380，我会收到错误说
''参数太少了。预期1''...
100 PString =" SELECT [AddnlOwnrFName]& " &安培; Chr $（& H22）&空间（1）&
Chr $（& H22）& " &安培; [AddnlOwnrLName] AS Recipient
120 PString = PString& FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID"
140 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
[Forms]！[Form1]！[Text0];" &安培; vbNewLine& vbNewLine
160 PString = PString& UNION SELECT [LienHolderName] AS收件人
180 PString = PString& FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID"
200 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
[Forms]！[Form1]！[Text0];" &安培; vbNewLine& vbNewLine
220 PString = PString& UNION SELECT [OwnerFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [OwnerLName] AS收件人
240 PString = PString& " FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID"
260 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
[Forms]！[Form1]！[Text0];" &安培; vbNewLine& vbNewLine
280 PString = PString& UNION SELECT
IIf（IsNull（[AuthCompany]），[AuthFName]&"& Chr $（& H22）& Space（1）&
Chr $（& H22）&"& [AuthLName]，[AuthCompany]）AS收件人
300 PString = PString& FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID"
320 PString = PString& WHERE（tblVehicleJobs.VehicleJobID =
[Forms]！[Form1]！[Text0]）AND（tblAuth.AuthCatID）<> 2;" &安培; vbNewLine&
vbNewLine
340 PString = PString& UNION SELECT [ProxyFName]& " &安培; Chr $（& H22）&
Space（1）& Chr $（& H22）& " &安培; [ProxyLName] AS收件人
360 PString = PString& FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID"
380 PString = PString& WHERE tblVehicleJobs.VehicleJobID =
[Forms]！[Form1]！[Text0];"
在每种情况下，都会发生故障运行线＃480，
读取......
设置MyRst = MyDB.OpenRecordset（PString，dbOpenForwardOnly）
Worthy值得一提的是，3个SQL语法中的每一个都工作完美地粘贴到查询构建器中并使用BANG按钮运行。
但是DAO没有处理两个语法非常好。 parm'的
似乎是个问题。如果我喂它一个精确的值 - 似乎很喜欢它b / b
。但是如果我引用该值，指向它在
a表格上的位置或试图从GLOBAL var中读取它 - 所有地狱都会松动。
我是什么试图摆脱这种情况是一连串的名字
类似Bob Jones&萨姆史密斯& ABC公司&本旺 ==>
全部从不同表格的不同字段中读取。
我必须错过这条船。我从来没有遇到像这样的情况。
。评论？
Gentlemen: I am having one heck of a time taking a DAO walk through
the records in an SQL dynaset. I''m trying to walk a set of records
returned by a UNION query. I''m attempting to filter the records to
those related to vehicle #60 ( [VehicleJobID] = 60 ). If I explicitly
specify 60 in the SQL ==everything works fine. Take a look:
100 PString = "SELECT [AddnlOwnrFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AddnlOwnrLName] AS Recipient "
120 PString = PString & "FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs.VehicleJobID=60;" &
vbNewLine & vbNewLine
160 PString = PString & "UNION SELECT [LienHolderName] AS Recipient "
180 PString = PString & "FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID "
200 PString = PString & "WHERE tblVehicleJobs.VehicleJobID=60;" &
vbNewLine & vbNewLine
220 PString = PString & "UNION SELECT [OwnerFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [OwnerLName] AS Recipient "
240 PString = PString & "FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID "
260 PString = PString & "WHERE tblVehicleJobs.VehicleJobID=60;" &
vbNewLine & vbNewLine
280 PString = PString & "UNION SELECT
IIf(IsNull([AuthCompany]),[AuthFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AuthLName],[AuthCompany]) AS Recipient "
300 PString = PString & "FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID "
320 PString = PString & "WHERE (tblVehicleJobs.VehicleJobID=60) AND
(tblAuth.AuthCatID)<>2;" & vbNewLine & vbNewLine
340 PString = PString & "UNION SELECT [ProxyFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [ProxyLName] AS Recipient "
360 PString = PString & "FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID "
380 PString = PString & "WHERE tblVehicleJobs.VehicleJobID=60;"
''USE DAO HERE TO WALK THE RECORDS.
420 Dim MyDB As Database, MyRst As Recordset
440 Dim strSQL As String
460 Set MyDB = CurrentDb
480 Set MyRst = MyDB.OpenRecordset(PString, dbOpenForwardOnly)
500 MyRst.MoveFirst
520 Do Until MyRst.EOF
540 POLAXstring = POLAXstring & MyRst!Recipient & " & "
560 MyRst.MoveNext
580 Loop
600 MyRst.Close
620 MyDB.Close
The above works perfectly. Never a glitch. If all I ever wanted to do
was to run this on vehicle ID #60 - all would be well. But that''s not
what I want, obviously. I would like to feed the SQL whatever vehicle
ID I wanted. I''ve tried GLOBAL variable assignment and a GLOBAL
procedure that reads and returns its value. And I''ve tried writing the
vehicle ID number to a form field and referencing it there. But I keep
getting errors. For example, if I write lines 100-380 this way, I get
an error saying GetCurrentVehicleJobID() is not defined function...
100 PString = "SELECT [AddnlOwnrFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AddnlOwnrLName] AS Recipient "
120 PString = PString & "FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID());" & vbNewLine & vbNewLine
160 PString = PString & "UNION SELECT [LienHolderName] AS Recipient "
180 PString = PString & "FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID "
200 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID());" & vbNewLine & vbNewLine
220 PString = PString & "UNION SELECT [OwnerFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [OwnerLName] AS Recipient "
240 PString = PString & "FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID "
260 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID());" & vbNewLine & vbNewLine
280 PString = PString & "UNION SELECT
IIf(IsNull([AuthCompany]),[AuthFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AuthLName],[AuthCompany]) AS Recipient "
300 PString = PString & "FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID "
320 PString = PString & "WHERE (tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID())) AND (tblAuth.AuthCatID)<>2;" & vbNewLine &
vbNewLine
340 PString = PString & "UNION SELECT [ProxyFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [ProxyLName] AS Recipient "
360 PString = PString & "FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID "
380 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID());"
And if I write lines 100-380 this way, I get an error saying
'' Too few parameters. Expected 1 ''...
100 PString = "SELECT [AddnlOwnrFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AddnlOwnrLName] AS Recipient "
120 PString = PString & "FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs
ON tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
[Forms]![Form1]![Text0];" & vbNewLine & vbNewLine
160 PString = PString & "UNION SELECT [LienHolderName] AS Recipient "
180 PString = PString & "FROM tblVehicleJobs INNER JOIN tblLienHolders
ON tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID "
200 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
[Forms]![Form1]![Text0];" & vbNewLine & vbNewLine
220 PString = PString & "UNION SELECT [OwnerFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [OwnerLName] AS Recipient "
240 PString = PString & "FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID "
260 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
[Forms]![Form1]![Text0];" & vbNewLine & vbNewLine
280 PString = PString & "UNION SELECT
IIf(IsNull([AuthCompany]),[AuthFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AuthLName],[AuthCompany]) AS Recipient "
300 PString = PString & "FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID "
320 PString = PString & "WHERE (tblVehicleJobs.VehicleJobID =
[Forms]![Form1]![Text0]) AND (tblAuth.AuthCatID)<>2;" & vbNewLine &
vbNewLine
340 PString = PString & "UNION SELECT [ProxyFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [ProxyLName] AS Recipient "
360 PString = PString & "FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID "
380 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
[Forms]![Form1]![Text0];"
In each of these cases, the failure occurs running line #480,
which reads...
Set MyRst = MyDB.OpenRecordset(PString, dbOpenForwardOnly)
Worthy of mention is the FACT that each of the 3 SQL syntax''s work
perfectly pasted into the query builder and run with the BANG button.
But DAO is not dealing with two of the syntax''s very well. The parm''s
seem to be the issue. If I feed it a precise value - seems to like it
quite well. But if I reference the value, pointing to its location on
a form or trying to read it from a GLOBAL var - all hell breaks loose.
What I''m trying to get out of this is a concatenated string of names
something like "Bob Jones & Sam Smith & ABC Company & Ben Wang" ==>
all read from different fields of different tables.
I must be missing the boat. I''ve never run into a situation quite like
this. Comments?
这篇关于Set MyRst = MyDB.OpenRecordset（PString，dbOpenForwardOnly）的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！