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?
10  3685 
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?
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?
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?
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.
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>
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?
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>
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>
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.
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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: K Williams |
last post by:
I recently discovered that you can do the following:
if currentdb.openrecordset ("select statement.....").eof = true then
for cases when you just want to see if any records exist.
This...
|
by: Dan W |
last post by:
Hi there. I am parsing a text file and as I parse each line I need
to go out to two different directories and check to see if any files
beginning with the string I just parsed exist. For...
|
by: jnikle |
last post by:
I have a parameter query named "qry_employee_info_reports" that I need
to run in the OnOpen event of a form. I'm after its total number of
records. The query's got several joins in it, and one of...
|
by: uday |
last post by:
hi all,
i am getting problem on QuerySibling(...) method of PropertyPage please tell
me, i am writing QuerySibling(...) method in PropertyPage named two and
sending some Data to PropertyPage...
|
by: Michael Peters |
last post by:
the Excel data that I would like to import into Access sometimes contain
line breaks, like this:
623
749
in one cell.
In Access, the imported data looks like this then:
623749
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: transpac80 |
last post by:
Problem with SQLPassthrough in Access 2007 using DAO
I have this piece of VBA code from inside Access to retrieve data from external database (SQL or Oracle)
sConn =...
|
by: MLH |
last post by:
100 Dim db As Database, rst As Recordset
120 Set db = CurrentDb
140 PString = "SELECT qryBatchList.ReadyFor906, qryBatchList.BatchID
FROM qryBatchList WHERE...
|
by: Stefano Sabatini |
last post by:
Hi all, I'm encountering this while trying to implement a factory
singleton method to generate objects.
The singleton has a static map which binds a static creation function
defined in each...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| | |
