Re: Problem with Set MyRst = MyDB.OpenRecordset(PString, dbOpenForwardOnly)

Thanks for trying to help, but I've already verified that the SQL
concatenation is fine. That might be the first thing one would
suspect. But here, see for yourself, these are the results in building
the SQL statement in the immediate window. Paste 'em in there and
execute the concatenation one line at-a-time...

MySQL = "SELECT [AddnlOwnrFName] & " & Chr$(&H22) & Space(1) &
Chr$(&H22) & " & [AddnlOwnrLName] AS Recipient "
MySQL = MySQL & "FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID "
MySQL = MySQL & "WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();" & vbNewLine &
vbNewLine
MySQL = MySQL & "UNION SELECT [LienHolderName] AS Recipient "
MySQL = MySQL & "FROM tblVehicleJobs INNER JOIN tblLienHolders ON
tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID "
MySQL = MySQL & "WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();" & vbNewLine &
vbNewLine
MySQL = MySQL & "UNION SELECT [OwnerFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [OwnerLName] AS Recipient "
MySQL = MySQL & "FROM tblVehicleJobs INNER JOIN tblOwners ON
tblVehicleJobs.OwnerID = tblOwners.OwnerID "
MySQL = MySQL & "WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();" & vbNewLine &
vbNewLine
MySQL = MySQL & "UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] &
" & Chr$(&H22) & Space(1) & Chr$(&H22) & " &
[AuthLName],[AuthCompany]) AS Recipient "
MySQL = MySQL & "FROM tblAuth INNER JOIN tblVehicleJobs ON
tblAuth.AuthID = tblVehicleJobs.AuthID "
MySQL = MySQL & "WHERE
(tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID()) AND
(tblAuth.AuthCatID)<>2;" & vbNewLine & vbNewLine
MySQL = MySQL & "UNION SELECT [ProxyFName] & " & Chr$(&H22) &
Space(1) & Chr$(&H22) & " & [ProxyLName] AS Recipient "
MySQL = MySQL & "FROM tblVehicleJobs INNER JOIN tblProxies ON
tblVehicleJobs.ProxyID = tblProxies.ProxyID "
MySQL = MySQL & "WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();"

.... and here's what you'll get...

?MySQL
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblAddnlOwnrs ON tblVehicleJobs.VehicleJobID
= tblAddnlOwnrs.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT [LienHolderName] AS Recipient FROM tblVehicleJobs INNER
JOIN tblLienHolders ON tblVehicleJobs.VehicleJobID =
tblLienHolders.VehicleJobID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT [OwnerFName] & " " & [OwnerLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblOwners ON tblVehicleJobs.OwnerID =
tblOwners.OwnerID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient FROM tblAuth INNER JOIN
tblVehicleJobs ON tblAuth.AuthID = tblVehicleJobs.AuthID WHERE
(tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID()) AND
(tblAuth.AuthCatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] AS Recipient FROM
tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID WHERE
tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID();

As you can see, there's nothing wrong with the SQL statement. I paste
that into the QBE grid and the query runs fine there in the query
builder. The problem ONLY arises when the SQL is launched in a
DAO setting.

And like I said, if you substitute the number 60 or any other vehicle
ID number in place of GetCurrentVehicleJobID() ==> everything works
perfectly. Don't get me wrong, I appreciate your 10-second look, but
this thing's been kickin' my ass for days now.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Mon, 02 Jul 2007 13:54:06 GMT, "Don Leverton"
<leveriteNoJunkMail@xxxxxxxxxxxxxxx> wrote:

I just took a 10-second look ... but I'm suspicious of Line 380.

380 PString = PString & "WHERE tblVehicleJobs.VehicleJobID =
(GetCurrentVehicleJobID());"
Because of the fact that your function is enclosed within the quotation
marks, it is simply being passed as text.
If you wanted to be sure that I'm right, add a Debug.Print PString on the
line following 380, and then view it's output in the Debug (Immediate)
window.
While you're in the Debug window, type in "?GetCurrentVehicleJobID" and see
if it returns a value (i.e 60) on it's own.

Then try:

380 PString = PString & "WHERE tblVehicleJobs.VehicleJobID = "
381 PString = PString & GetCurrentVehicleJobID()
382 PString = PString & ";"
383 Debug.Print PString

Now view the Debug (Immediate) window

BTW, that Debug window and query design combination is a pretty powerful
tool. Anytime that I build an SQL string in code, I always copy the
resulting string from the Debug window into the SQL view of a new query.
Then I click on the QBE grid view to see if it looks right. Next I click on
the data*** view to see if the query is returning the expected values. An
added feature to this approach is ... that if there is an error in the
string and something can't be evaluated ... Access will not only tell you
there is a syntax error ... it will even highlight it for you.

This also works in reverse. Build a query that returns the rcords that you
want ... and then switch to SQL view. Copy the SQL string, and paste it into
your code module. Break it up and add your "PString = PString &" lines
(keeping the variables in mind as in Line 381 above) ... and you should be
"good to go".

HTH,
Don


"MLH" <CRCI@xxxxxxxxxxxxxx> wrote in message
news:8veh8359uniiv8cjg00e6inf0b9ummhfba@xxxxxxxxxx
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?


.