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.V ehicleJobID "
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.Owner ID "
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=6 0) AND
(tblAuth.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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 GetCurrentVehic leJobID() 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.V ehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID());" & 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.Owner ID "
260 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID())) AND (tblAuth.AuthCa tID)<>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.Prox yID "
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());"
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.V ehicleJobID "
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.Owner ID "
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.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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  3721 
I just took a 10-second look ... but I'm suspicious of Line 380.
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());"
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 "?GetCurrentVeh icleJobID" 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 & GetCurrentVehic leJobID()
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**@NorthStat e.netwrote in message
news:8v******** *************** *********@4ax.c om...
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.V ehicleJobID "
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.Owner ID "
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=6 0) AND
(tblAuth.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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 GetCurrentVehic leJobID() 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.V ehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID());" & 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.Owner ID "
260 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID())) AND (tblAuth.AuthCa tID)<>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.Prox yID "
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());"
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.V ehicleJobID "
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.Owner ID "
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.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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**@NorthStat e.netwrote:
You need to check your db.OpenRecordse t 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 =" &
GetCurrentVehic leJobID() & ";"
-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.V ehicleJobID "
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.Owner ID "
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.Auth ID = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID=6 0) AND
(tblAuth.AuthC atID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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 GetCurrentVehic leJobID() 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.V ehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVeh icleJobID());" & 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 =
(GetCurrentVeh icleJobID());" & 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.Owner ID "
260 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVeh icleJobID());" & 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.Auth ID = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID =
(GetCurrentVeh icleJobID())) AND (tblAuth.AuthCa tID)<>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.Prox yID "
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVeh icleJobID());"
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.V ehicleJobID "
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.Owner ID "
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.Auth ID = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID =
[Forms]![Form1]![Text0]) AND (tblAuth.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxx
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**@NorthStat e.netwrote:
You need to check your db.OpenRecordse t 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 =" &
GetCurrentVehi cleJobID() & ";"
-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.V ehicleJobID "
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
tblVehicleJob s.OwnerID = tblOwners.Owner ID "
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.AuthI D = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID=6 0) AND
(tblAuth.Auth CatID)<>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
tblVehicleJob s.ProxyID = tblProxies.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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 GetCurrentVehic leJobID() 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.V ehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVe hicleJobID());" & 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 =
(GetCurrentVe hicleJobID());" & 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
tblVehicleJob s.OwnerID = tblOwners.Owner ID "
260 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVe hicleJobID());" & 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.AuthI D = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID =
(GetCurrentVe hicleJobID())) AND (tblAuth.AuthCa tID)<>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
tblVehicleJob s.ProxyID = tblProxies.Prox yID "
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVe hicleJobID());"
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.V ehicleJobID "
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
tblVehicleJob s.OwnerID = tblOwners.Owner ID "
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.AuthI D = tblVehicleJobs. AuthID "
320 PString = PString & "WHERE (tblVehicleJobs .VehicleJobID =
[Forms]![Form1]![Text0]) AND (tblAuth.AuthCa tID)<>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
tblVehicleJob s.ProxyID = tblProxies.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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**@NorthStat e.netwrote:
You need to check your db.OpenRecordse t statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.Op enRecordset("Em ployees", _
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.OpenRecord set(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...@NorthStat e.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.V ehicleJobID "
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.Owner ID "
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=6 0) AND
(tblAuth.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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 GetCurrentVehic leJobID() 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.V ehicleJobID "
140 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID());" & 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.Owner ID "
260 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());" & 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 =
(GetCurrentVehi cleJobID())) AND (tblAuth.AuthCa tID)<>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.Prox yID "
380 PString = PString & "WHERE tblVehicleJobs. VehicleJobID =
(GetCurrentVehi cleJobID());"
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.V ehicleJobID "
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.Owner ID "
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.AuthCa tID)<>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.Prox yID "
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.OpenRecord set(PString, dbOpenForwardOn ly)
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**@NorthStat e.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.Op enRecordset("Em ployees", _
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**@NorthStat e.netwrote:
You need to check your db.OpenRecordse t statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.Op enRecordset("Em ployees", _
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.OpenRecord set(PString, dbOpenSnapshot)
or simply do what you suggested and drop the MoveFirst line?
<snip>
Now I gotcha. Thanks.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xx
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**@NorthStat e.netwrote:
dbOpenDynase t 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.Op enRecordset("Em ployees", _
dbOpenSnapshot,
dbForwardOnly)
-Tom.
>>On Mon, 02 Jul 2007 06:55:55 -0700, Tom van Stiphout
<no********** ***@cox.netwrot e:
>>>On Mon, 02 Jul 2007 05:16:20 -0400, MLH <CR**@NorthStat e.netwrote:
You need to check your db.OpenRecordse t statement. It is off by one
argument.
Unsure which arg I'm missing. Checking HELP, I see this...
Set rstEmployees = _
dbsNorthwind.Op enRecordset("Em ployees", _
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.
Fortunatel y 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.OpenRecord set(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**@NorthStat e.netwrote in message
news:sg******** *************** *********@4ax.c om...
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 obviously does not assign the recordset to any variable. My
question is, does this still use memory that is not freed because
there is no "recset.close" and "set variable = nothing"?
|
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 example... if the
first line in my text file is AS1501 then I need to check Dir1 and see
if there are any files that begin with AS1501 then I need to go to
Dir2 and check there also. I am trying to check using the following
code...
If...
|
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 them is to
query "qry_last_transition," which is also a parameter query. Both
querys use the same parameter: a control called "txtSecondDate" on a
pop up form.
What I've been trying to do up to this point is to open up this
parameter form,...
|
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 three where i am collecting the Data. But
in first time the Page is not able to Transfer the Data to ThirdPage.
here is the snippet of code.
second page:
|
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=" & msDbFilename
moConn.Properties("Persist Security Info") = False
moConn.ConnectionString = msConnString
moConn.CursorLocation = adUseClient
moConn.Mode = adModeReadWrite' or using default...same result
|
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 = "ODBC;DSN=ABC;UID=XYZ;PWD=123;"
sSQL = "SELECT * FROM BLAG WHERE UPPER(id) = UPPER('12aBxY')"
Set db = DBEngine.Workspaces(0).OpenDatabase("", False, False, sConn)
Set rs = db.OpenRecordset(sSQL, dbOpenForwardOnly, dbSQLPassThrough)
|
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 qryBatchList.BatchID=GetCurrentBatchID()"
160 Set rst = db.OpenRecordset(PString, dbOpenDynaset)
At compile time, things are OK. But at run time, line #160
gives rise to an error saying some FN I've used for years
is undefined.
It almost seems like it pukes on some random
|
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 class to the type of the object to be created.
Here it is the code, which is a modification of the wikipedia C++
factory example code:
----------------------------------8<--------------------------------
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
