• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

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)

  • 3
  • 2
1 Solution
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..., ...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.

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.

Jeffrey CoachmanMIS LiasonCommented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now