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)