bercej
asked on
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)
Regards,
J
SqlTrace.txt
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)
Regards,
J
SqlTrace.txt
Jeffrey Coachman
Are you "Clearing" everything when the form closes?
ASKER
No explicit instantiation of objects is in the form's code.
What else - and how - could be "Cleared"?
What else - and how - could be "Cleared"?
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..., ...is 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.
JeffCoachman
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..., ...is 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.
JeffCoachman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.