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

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.

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

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" wrote in message
news:4dijm35gj010bf5mo6fqq9rmpefh8g1qpu@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.

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

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!

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

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

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

Michka's stuff is always good, if a little dated.

Decompile was undocumented in earlier versions. From memory, it became
documented around Access 2000. Consequently it is now fully supported.

Having said that, you still want to make a backup copy of your database
before you decompile it. Stuff can go wrong.

My personal recommendation is to decompile when:
a) you edit a database in another version (since every version uses a
different binary)

b) there is the faintest hint of anything wrong in VBA code (e.g. phantom
break point)

c) you are releasing a new version of your software (to ensure maximum
compatibility and minimum file size.)

--
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.

"Jens Schilling" wrote in message
news:fkdi4r$ec7$01$1@news.t-online.com...
> 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
>
>

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

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.