Saved query and FN calling it now failing after 18-mos of use?

[MLH]

I have the following saved UNION query named qryPeople2NameInNPaperAd:

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] & " " &
[AddnlOwnrAddr] & " " & [AddnlOwnrCity] & ", " & [AddnlOwnrState] & "
" & [AddnlOwnrZip] AS Item, tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID Where
tblVehicleJobs.VehicleJobID = GetCurrentVehicleJobID();

UNION SELECT [LienHolderName] & " " & [LienHolderAddr] & " " &
[LienHolderCity] & ", " & [LienHolderState] & " " & [LienHolderZip]
AS Item, tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs INNER JOIN tblLienHolders ON
tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID Where
tblVehicleJobs.VehicleJobID = GetCurrentVehicleJobID();

The query has worked fine now for 1.5 years and still opens and runs
fine today.

It is used in Function MakeNPaperAd() As String the following way...
431 If DCount("*", "qryPeople2NameInNPaperAd") = 0 Then GoTo Point01
'(13-lines down)
440 Dim myDB As Database, MyRst As Recordset
450 Set myDB = CurrentDb
460 Set MyRst = myDB.OpenRecordset("qryPeople2NameInNPaperAd",
dbOpenForwardOnly)
470 Do Until MyRst.EOF
480 MoreLitigants = MyRst!Item & "; "
490 MyRst.MoveNext
500 Loop
510 MoreLitigants = left$(MoreLitigants, Len(MoreLitigants) - 2) 'Trim
trailing semicolon-space pair
520 MyRst.Close
530 myDB.Close
540 Set MyRst = Nothing
550 Set myDB = Nothing
Point01:
560 MyString = "Notice of Sale by Lienor: " & TowCoName & ", " &
TowCoAddrCSZ & " at " & SaleHour & ", " & SaleDate & ". " &
VehicleDescr
570 MyString = MyString & "; VIN: " & VIN & "; Odo: " & MyOdo & ".
Litigants: " & OwnerName & ", " & OwnerAddr & " " & OwnerCSZ
580 MyString = MyString & IIf(MoreLitigants = "", "", "; " &
MoreLitigants) & ". Lien Claim: " & Format$(CurrentFeesDue, "$#,#")
600 MyString = MyString & " on SALE date."
620 MakeNPaperAd = MyString

Ths FN has worked fine now for 1.5 years. Now I might have made
some mod's that I cannot recall in the past couple of weeks - but I
certainly don't think so.

Here's the problem: When I run the function (designed to return a
string), an error is captured saying that in line #460, citing an
Unknown Function GetCurrentVehicleJobID in Expression.

Now, GetCurrentVehicleJobID() has been around for a long time. It
is certain that it isn't "unknown". It runs fine called from the debug
window. The UNION query runs fine opened & run from the database
window - no such errors. But the code in Function MakeNPaperAd()
is crashing apparently in line #460. I cannot imagine why. I suspect
corruption because the report calling the query runs fine in my dev
copy of the database. I don't know what to do about it if it is
corruption.

[Allen Browne]

Some things to check:

Make sure the Function GetCurrentVehicleJobID() declaration is in scope. It
could be in the same module, or in a standard module (provided it doesn't
have Private preceeding it), but not in a class module or in the module of
another form/report (for the way you are calling it.)

Is there anything else using the name GetCurrentVehicleJobID?

Put the cursor in the GetCurrentVehicleJobID() call on line 460, and press
Shift+F2. What does Access say?

Are your library references okay:
http://allenbrowne.com/ser-38.html

If that does not identify the problem, make sure Name AutoCorrect is off,
then compact, decompile, and compact again. Post back if you need more
detail on that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MLH" <CR**@NorthState.netwrote in message
news:4d********************************@4ax.com...

>I have the following saved UNION query named qryPeople2NameInNPaperAd:

SELECT [AddnlOwnrFName] & " " & [AddnlOwnrLName] & " " &
[AddnlOwnrAddr] & " " & [AddnlOwnrCity] & ", " & [AddnlOwnrState] & "
" & [AddnlOwnrZip] AS Item, tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID Where
tblVehicleJobs.VehicleJobID = GetCurrentVehicleJobID();

UNION SELECT [LienHolderName] & " " & [LienHolderAddr] & " " &
[LienHolderCity] & ", " & [LienHolderState] & " " & [LienHolderZip]
AS Item, tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs INNER JOIN tblLienHolders ON
tblVehicleJobs.VehicleJobID = tblLienHolders.VehicleJobID Where
tblVehicleJobs.VehicleJobID = GetCurrentVehicleJobID();

The query has worked fine now for 1.5 years and still opens and runs
fine today.

It is used in Function MakeNPaperAd() As String the following way...
431 If DCount("*", "qryPeople2NameInNPaperAd") = 0 Then GoTo Point01
'(13-lines down)
440 Dim myDB As Database, MyRst As Recordset
450 Set myDB = CurrentDb
460 Set MyRst = myDB.OpenRecordset("qryPeople2NameInNPaperAd",
dbOpenForwardOnly)
470 Do Until MyRst.EOF
480 MoreLitigants = MyRst!Item & "; "
490 MyRst.MoveNext
500 Loop
510 MoreLitigants = left$(MoreLitigants, Len(MoreLitigants) - 2) 'Trim
trailing semicolon-space pair
520 MyRst.Close
530 myDB.Close
540 Set MyRst = Nothing
550 Set myDB = Nothing
Point01:
560 MyString = "Notice of Sale by Lienor: " & TowCoName & ", " &
TowCoAddrCSZ & " at " & SaleHour & ", " & SaleDate & ". " &
VehicleDescr
570 MyString = MyString & "; VIN: " & VIN & "; Odo: " & MyOdo & ".
Litigants: " & OwnerName & ", " & OwnerAddr & " " & OwnerCSZ
580 MyString = MyString & IIf(MoreLitigants = "", "", "; " &
MoreLitigants) & ". Lien Claim: " & Format$(CurrentFeesDue, "$#,#")
600 MyString = MyString & " on SALE date."
620 MakeNPaperAd = MyString

Ths FN has worked fine now for 1.5 years. Now I might have made
some mod's that I cannot recall in the past couple of weeks - but I
certainly don't think so.

Here's the problem: When I run the function (designed to return a
string), an error is captured saying that in line #460, citing an
Unknown Function GetCurrentVehicleJobID in Expression.

Now, GetCurrentVehicleJobID() has been around for a long time. It
is certain that it isn't "unknown". It runs fine called from the debug
window. The UNION query runs fine opened & run from the database
window - no such errors. But the code in Function MakeNPaperAd()
is crashing apparently in line #460. I cannot imagine why. I suspect
corruption because the report calling the query runs fine in my dev
copy of the database. I don't know what to do about it if it is
corruption.

[anthony]

Allen, what's MS's position on the decompile switch? I've seen posts
advocating its use and others which say that it is an untested, rough
and ready approach that may even trash your database. I'm confused!

[Jens Schilling]

Hi, Anthony

anthony wrote:

Allen, what's MS's position on the decompile switch? I've seen posts
advocating its use and others which say that it is an untested, rough
and ready approach that may even trash your database. I'm confused!

I suggest to you to read the following article written by Michael Kaplan:

The real deal on the /Decompile switch

http://www.trigeminal.com/usenet/usenet004.asp?1033

Regards
Jens

[MLH]

The code has been in use for 18-months. I'm thinking this
is not something 'obvious'. In a DOS window - I did this...

C:\DB\TimN>copy tpmclivedata.mdb testing.mdb /v
Overwrite testing.mdb? (Yes/No/All): y
1 file(s) copied.

C:\DB\TimN>

I opened testing.mdb, set the prerequisite vars and ran
the report. Perfect. No errors. No hitches. I reopened
tpmclivedata.mdb, did the same, ran the report, got the
error - exactly the same as before. Now that is scary.