By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

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

P: n/a
MLH
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?
Jul 2 '07 #1
Share this Question
Share on Google+
10 Replies


P: n/a
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 datasheet 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" <CR**@NorthState.netwrote in message
news:8v********************************@4ax.com...
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?

Jul 2 '07 #2

P: n/a
On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthState.netwrote:

You need to check your db.OpenRecordset statement. It is off by one
argument.
Btw, if you create a forward-only cursor, you can't MoveFirst.
Fortunately it's not needed, so you can delete that line.

'Too few parameters': you need to concatenate the string differently:
PString = PString & "WHERE tblVehicleJobs.VehicleJobID =" &
[Forms]![Form1]![Text0] & ";"
or:
PString = PString & "WHERE tblVehicleJobs.VehicleJobID =" &
GetCurrentVehicleJobID() & ";"

-Tom.
>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?
Jul 2 '07 #3

P: n/a
MLH
Thanks, Tom. You were exactly correct. I don't know why I
am so easily confused when trying to concat SQL. I can see
why what you pointed out works. Its so confusing to me when
my own concatenation creates an SQL statement that works
when I print it to the debug window (using debug.print) and
paste the output into the query builder. This same kind-a-thing
has been kickin' my ass for years. I just can't seem to get the
hang of it. Dunno why

'preciate it.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxx
On Mon, 02 Jul 2007 06:55:55 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthState.netwrote:

You need to check your db.OpenRecordset statement. It is off by one
argument.
Btw, if you create a forward-only cursor, you can't MoveFirst.
Fortunately it's not needed, so you can delete that line.

'Too few parameters': you need to concatenate the string differently:
PString = PString & "WHERE tblVehicleJobs.VehicleJobID =" &
[Forms]![Form1]![Text0] & ";"
or:
PString = PString & "WHERE tblVehicleJobs.VehicleJobID =" &
GetCurrentVehicleJobID() & ";"

-Tom.
>>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?
Jul 2 '07 #4

P: n/a
MLH
My apologies, Don. I disregarded your suggestion
on the basis of the fact that my CONCAT worked
when I used debug.print to get it out to the debug
window, then copied 'n pasted it into query builder.

But I now see that you were RIGHT and what I was
doing wasn't the same as what you suggested. The
whole business of concatenating SQL is more than
confusing to me. The concatenation solution you
suggested was EXACTLY right. I should have tried
it before responding.

Your 10-second gander at my problem proved to be
more fruitful than my last 48-hours of screwing with it.

Thanks.
Jul 2 '07 #5

P: n/a
MLH
On Mon, 02 Jul 2007 06:55:55 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthState.netwrote:

You need to check your db.OpenRecordset statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenDynaset)
There are 2 arg's there... the table name and dbOpenDynaset.
Am I missing something you're saying? That's happening a lot to me
lately.

>Btw, if you create a forward-only cursor, you can't MoveFirst.
Fortunately it's not needed, so you can delete that line.
I did the forward only thing because I'd heard it was faster. However,
there will never be more than half-a-dozen records returned. So, would
you recommend I use the following:

Set MyRst = MyDB.OpenRecordset(PString, dbOpenSnapshot)

or simply do what you suggested and drop the MoveFirst line?

<snip>
Jul 2 '07 #6

P: n/a
Hi,
Don't know if it would work but, have you tried changing it to a
parameter query?

replace - VehicleJobID=60 with VehicleJobID=[Enter Vehicle Job ID]

bobh

On Jul 2, 5:16 am, MLH <C...@NorthState.netwrote:
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?

Jul 2 '07 #7

P: n/a
On Mon, 02 Jul 2007 11:25:28 -0400, MLH <CR**@NorthState.netwrote:

dbOpenDynaset or dbOpenSnapshot is good, but you had dbForwardOnly.
That's not a kind of recordset, but a recordset modifier, which goes
as the third argument. So a firehose cursor would be:
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenSnapshot,
dbForwardOnly)

-Tom.

>On Mon, 02 Jul 2007 06:55:55 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>>On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthState.netwrote:

You need to check your db.OpenRecordset statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenDynaset)
There are 2 arg's there... the table name and dbOpenDynaset.
Am I missing something you're saying? That's happening a lot to me
lately.

>>Btw, if you create a forward-only cursor, you can't MoveFirst.
Fortunately it's not needed, so you can delete that line.
I did the forward only thing because I'd heard it was faster. However,
there will never be more than half-a-dozen records returned. So, would
you recommend I use the following:

Set MyRst = MyDB.OpenRecordset(PString, dbOpenSnapshot)

or simply do what you suggested and drop the MoveFirst line?

<snip>
Jul 3 '07 #8

P: n/a
MLH
Now I gotcha. Thanks.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Mon, 02 Jul 2007 19:26:19 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>On Mon, 02 Jul 2007 11:25:28 -0400, MLH <CR**@NorthState.netwrote:

dbOpenDynaset or dbOpenSnapshot is good, but you had dbForwardOnly.
That's not a kind of recordset, but a recordset modifier, which goes
as the third argument. So a firehose cursor would be:
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenSnapshot,
dbForwardOnly)

-Tom.

>>On Mon, 02 Jul 2007 06:55:55 -0700, Tom van Stiphout
<no*************@cox.netwrote:
>>>On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthState.netwrote:

You need to check your db.OpenRecordset statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees", _
dbOpenDynaset)
There are 2 arg's there... the table name and dbOpenDynaset.
Am I missing something you're saying? That's happening a lot to me
lately.

>>>Btw, if you create a forward-only cursor, you can't MoveFirst.
Fortunately it's not needed, so you can delete that line.
I did the forward only thing because I'd heard it was faster. However,
there will never be more than half-a-dozen records returned. So, would
you recommend I use the following:

Set MyRst = MyDB.OpenRecordset(PString, dbOpenSnapshot)

or simply do what you suggested and drop the MoveFirst line?

<snip>
Jul 4 '07 #9

P: n/a
Hi Again...

I just got back from a week of holidays, sorry for the delay.

I use a VB utility called SQL2VAR.EXE which makes the chore of cocatenation
of SQL strings considerably easier.
It's not 100% foolproof ... but it does save a lot of copying and pasting.

Download it here:
http://files.chatnfiles.com/Winsite%...zip/index.html

HTH,
Don
"MLH" <CR**@NorthState.netwrote in message
news:sg********************************@4ax.com...
My apologies, Don. I disregarded your suggestion
on the basis of the fact that my CONCAT worked
when I used debug.print to get it out to the debug
window, then copied 'n pasted it into query builder.

But I now see that you were RIGHT and what I was
doing wasn't the same as what you suggested. The
whole business of concatenating SQL is more than
confusing to me. The concatenation solution you
suggested was EXACTLY right. I should have tried
it before responding.

Your 10-second gander at my problem proved to be
more fruitful than my last 48-hours of screwing with it.

Thanks.

Jul 8 '07 #10

P: n/a
Also worth mentioning:

[quote]
This utility is free. It requires the run-time file VBRUN300.DLL
which is not included. You can find this file on the MSBASIC,
WINFUN, or WINSHARE forums on Compuserve.
[End Quote]

"Don Leverton" <le****************@telusplanet.netwrote in message
news:Jp_ji.28918$xk5.10638@edtnps82...
Hi Again...

I just got back from a week of holidays, sorry for the delay.

I use a VB utility called SQL2VAR.EXE which makes the chore of
cocatenation of SQL strings considerably easier.
It's not 100% foolproof ... but it does save a lot of copying and pasting.

Download it here:
http://files.chatnfiles.com/Winsite%...zip/index.html

HTH,
Don
"MLH" <CR**@NorthState.netwrote in message
news:sg********************************@4ax.com...
>My apologies, Don. I disregarded your suggestion
on the basis of the fact that my CONCAT worked
when I used debug.print to get it out to the debug
window, then copied 'n pasted it into query builder.

But I now see that you were RIGHT and what I was
doing wasn't the same as what you suggested. The
whole business of concatenating SQL is more than
confusing to me. The concatenation solution you
suggested was EXACTLY right. I should have tried
it before responding.

Your 10-second gander at my problem proved to be
more fruitful than my last 48-hours of screwing with it.

Thanks.


Jul 8 '07 #11

This discussion thread is closed

Replies have been disabled for this discussion.