Problem with recordsource in Microsoft Access

MS Access 2000 application is front end to MS SQL 2000 database.  

Form_Open event of data entry form calls Initialize subroutine which sets form's RecordSource property:
    ssql = ScalarSelect("SELECT 'Joker Was Here', count(*) from [dbo_Razvojni program] where [Program ID]=70557")
    ssql = "SELECT [dbo_Razvojni program].* FROM [dbo_Razvojni program] WHERE ((([dbo_Razvojni program].[Nadrejeni program ID])="
    ssql = ssql + Str(IzbraniProgramID) + ")) and 1=1+1-1 order by aifra asc;"
    Me.RecordSource = ssql
If form is opened and closed cyclically it opens correctly for the first two iterations and fails on the third. Failing statement is the last one in code above.

Form itself is complex with many subroutines and external calls.

No interactive debugging is available after error appears, debugger fails. A "hand made" logging of executon into text file shows out that execution of Initialize routine is discontinued immediately after error and control is returned to the calling sub Form_Open without any error message. Form's recordset is empty and "Run-Time error 94 Illegal use of Null" appears when trying to reference it's data.

In general, form fails if specific sequence of openings happens on the same data. On some occasions it fails on tghe third iteration, sometimes on 15th...

A number of correction actions were tried:

1.      Query was rewritten into different, but equivalent syntax (eg SELECT Field1, Field2, & instead of SELECT *; static query with parameter; &)
2.      Query was implemented with dynamic DAO recordset
3.      Application was decompiled and repaired
4.      Application was exported/imported into new empty DB;
5.      App was converted into 2007 format and than back to 2000 format
5.      Form was replaced with the new one, recreated from the scratch in a separate empty Access db

Each and every time the same error!!

Attached is MS SQL Profiler log showing SQL code sent to the server when erroneous code was executed. Data navigation in erroneous third trial seems quite different. Last four SELECTS came from Open_Form

Any help would do really much good (customers are crying)

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Are you "Clearing" everything when the form closes?
bercejAuthor Commented:
No explicit instantiation of objects is in the form's code.
What else - and how - could be "Cleared"?

Jeffrey CoachmanMIS LiasonCommented:
Not sure.

Ususally when something "Opens fine x times, then fails on x+1, that usually is a sign that something is "Accumulating", A.K.A.: "Not Clearing".
(For example: ssql = ssql + Str(IzbraniProgramID..., an example of an "accumulator")

So to be sure, make sure that everything that loads when the form is in use, is cleared.
Including things that might load other things.

bercejAuthor Commented:
It took me some time to make extensive cleansing of suspicious code.

It did not help.

But I found more precisely the reason for failure.

Form itself has linked subforms and one of them has linked suborms. If I remove this one, app does not fail any more.

I will post it as a new issue.

So your comment was helpful but solution is yet to come. Thank you any way.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.