470,631 Members | 2,061 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,631 developers. It's quick & easy.

Union Query Dfficult To Access From DAO

MLH
I have a saved UNION query named
qryAuthsOwnersAddnlOwnersLienholders4OneCar.

Here it is...
SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] & " " &
[AddnlOwnrAddr] & " " & [AddnlOwnrCity] & ", " & [AddnlOwnrState] & "
" & [AddnlOwnrZip] & " " & "(" & [AddnlOwnrCounty] & " county) " &
Format$([AddnlOwnrPhone],"(@@@) @@@-@@@@") & " " &
IIf([Corporation]=True,"(corporation)","(person)") AS Item,
tblVehicleJobs.VehicleJobID, "X" AS OLAX, tblAddnlOwnrs.AddnlOwnrID AS
RecID, [AddnlOwnrFName] & " " & [AddnlOwnrLName] AS Recipient,
[AddnlOwnrFName] & " " & [AddnlOwnrLName] & " " & [AddnlOwnrAddr] & "
" & [AddnlOwnrCity] & ", " & [AddnlOwnrState] & " " & [AddnlOwnrZip]
AS NameAddr
FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT [LienHolderName] & " " & [LienHolderAddr] & " " &
[LienHolderCity] & ", " & [LienHolderState] & " " & [LienHolderZip] &
" " & "(" & [LHCounty] & " county) " &
Format$([LienHolderPhone],"(@@@) @@@-@@@@") & " (corporation)" AS
Item, tblVehicleJobs.VehicleJobID, "L" AS OLAX,
tblLienHolders.LienHolderID AS RecID, [LienHolderName] AS Recipient,
[LienHolderName] & " " & [LienHolderAddr] & " " & [LienHolderCity] &
", " & [LienHolderState] & " " & [LienHolderZip] AS NameAddr
FROM tblVehicleJobs INNER JOIN tblLienHolders ON
tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT [OwnerFName] & " " & [OwnerLName] & " " & [OwnerAddr] &
" " & [OwnerCity] & ", " & [OwnerState] & " " & [OwnerZip] & " " &
"(" & [OwnerCounty] & " county) " & Format$([OwnerPhone],"(@@@)
@@@-@@@@") & " " & IIf([Corporation]=True,"(corporation)","(person)")
AS Item, tblVehicleJobs.VehicleJobID, "O" AS OLAX, tblOwners.OwnerID
AS RecID, [OwnerFName] & " " & [OwnerLName] AS Recipient,
[OwnerFName] & " " & [OwnerLName] & " " & [OwnerAddr] & " " &
[OwnerCity] & ", " & [OwnerState] & " " & [OwnerZip] AS NameAddr
FROM tblVehicleJobs INNER JOIN tblOwners ON tblVehicleJobs.OwnerID =
tblOwners.OwnerID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

UNION SELECT IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) & " " & [AuthAddr] & " " & [AuthCity] & ",
" & [AuthState] & " " & [AuthZip] & " " & Format$([AuthPhone],"(@@@)
@@@-@@@@") AS Item, tblVehicleJobs.VehicleJobID, "A" AS OLAX,
tblAuth.AuthID AS RecID, IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) AS Recipient,
IIf(IsNull([AuthCompany]),[AuthFName] & " " &
[AuthLName],[AuthCompany]) & " " & [AuthAddr] & " " & [AuthCity] & ",
" & [AuthState] & " " & [AuthZip] AS NameAddr
FROM tblAuth INNER JOIN tblVehicleJobs ON tblAuth.AuthID =
tblVehicleJobs.AuthID
WHERE (tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobI D()) AND
(tblAuth.AuthCatID)<>2;

UNION SELECT [ProxyFName] & " " & [ProxyLName] & " " & [ProxyAddr] &
" " & [ProxyCity] & ", " & [ProxyState] & " " & [ProxyZip] & " " &
Format$([ProxyPhone],"(@@@) @@@-@@@@") AS Item,
tblVehicleJobs.VehicleJobID, "P" AS OLAX, tblProxies.ProxyID AS RecID,
[ProxyFName] & " " & [ProxyLName] AS Recipient, [ProxyFName] & " " &
[ProxyLName] & " " & [ProxyAddr] & " " & [ProxyCity] & ", " &
[ProxyState] & " " & [ProxyZip] AS NameAddr
FROM tblVehicleJobs INNER JOIN tblProxies ON tblVehicleJobs.ProxyID =
tblProxies.ProxyID
WHERE tblVehicleJobs.VehicleJobID=GetCurrentVehicleJobID ();

This query runs fine when I dbl-clik it in the database window. But
when I use DAO to access it, I get an error saying
GetCurrentVehicleJobID() is UNDEFINED. This has only recently
become a problem. The FN returns a VehicleJobID long integer value
when called. For nearly a year, I've had no problems calling the FN
to furnish a criteria value for the saved query. Now, in the past week
it seems I've had nothing but problems in several different places
where I've used the function to furnish a criteria value in an SQL
statement.
Jul 6 '07 #1
2 1593
MLH
The error occurs in line #1100

1000 Dim POLAXdb As Database, POLAXrecs As Recordset
1050 Set POLAXdb = CurrentDb
1100 Set POLAXrecs =
POLAXdb.OpenRecordset("qryAuthsOwnersAddnlOwnersLi enholders4OneCar",
dbOpenSnapshot)

Jul 6 '07 #2
MLH wrote:
The error occurs in line #1100

1000 Dim POLAXdb As Database, POLAXrecs As Recordset
1050 Set POLAXdb = CurrentDb
1100 Set POLAXrecs =
POLAXdb.OpenRecordset("qryAuthsOwnersAddnlOwnersLi enholders4OneCar",
dbOpenSnapshot)
Does this query require paramaters?

Nobody in this newsgroup know what this query is. Maybe you should
consider reconstructing it or rewriting it for this function.
Jul 7 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Paradigm | last post: by
3 posts views Thread by Dalan | last post: by
4 posts views Thread by spam | last post: by
1 post views Thread by forey | last post: by
5 posts views Thread by BillCo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.