Can anyone shed any light on why many of my Access Reports should now encounter a run-time error # 242? I have been struggling with this problem all day and cannot make sense of it.
I am developing an Access 2007 "project" (.adp) as a front-end to a SQL Server 2005 Express database.
For my Access Reports I pass in "WHERE" and "ORDER BY" clauses via the "OpenArgs" parameter, and in the Report's "Report_Open" procedure I construct a T-SQL statement to be used as the Report's "RecordSource" property. These are usually based on Tables or Views from the SQL Server database.
This has worked fine on many reports for the past two years, but all of a sudden, if a "date" condition is included in the "WHERE" condition for a Report, I am now getting a run-time error before the Report is displayed on the screen (even if no dates are actually displayed in the report). If there is no "date" condition included in the "WHERE" condition, the reports display OK, even if there are date fields included in the Report.
The error description appearing with the run-time error number 242 is either :
"Application-defined or object-defined error", or
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
In the "Report_Open" procedure I have even added extra VBA code to open a temporary, local RecordSet based on the "RecordSource". This appears to work OK, generating the correct number of records with the correct number of fields, so the T-SQL syntax appears to be OK. It is after this event, when presumably the Report is creating its own recordset that the run-time error occurs.
I think the problem lies at the Access end of things rather than the SQL Server end. I maintain "Version/Revision" numbers for both my SQL Server database and Access application (eg 178.001). When the Access application launches and first connects with the SQL Server database, I check that both have the same "Version" number (eg 178) and don't mind if they have different "Revision" numbers. ("Revisions" represent minor changes.)
I keep archive copies of all my previous "Versions" of both the Access application and SQL Server database and have found that the problem lies between my Access application version "178.001" and "178.002". (I know these numbers will mean nothing to anyone else, but they help me identify when the problem has arisen.) As far as I'm aware I made no significant change to the application between these revisions.
Is there some sort of "global" Access or Windows XP setting that might have changed and introduced this problem?
If I have to go back to working from the "good" Access application (Version 178.001), is there a way I can copy a new Access Form created in Version 178.002 and "paste" it into Version 178.001? I can cut and paste underlying VBA code easily enough, it is more the "design and layout" of a new Form I would also like to copy.
Any help or suggestions gratefully received.