Bizarre Access 2007 problem with subform Form.RecordSource

[cornall]

I have a subform that works perfectly well in Access 2000 and 2003 but displays no results in Access 2007.

If I query on a Number field I get no results e.g.

sSQL = "SELECT * FROM table WHERE NumberFieldA = 1234"

.....Form.RecordSource = sSQL

I get results in Access 2000 and 2003 but nothing in 2007

The query returns results in 2007 when run in the SQL window just not to the sub from.

If I change the query to a string field in the where clause i do get results in 2007 e.g.

sSQL = "SELECT * FROM table WHERE StringFieldA = 'abcd'"

.....Form.RecordSource = sSQL

Now for the wierd part I have found a fix that makes no sence and was found by mistake.

If I break the query that has the WHERE clause e.g.

sSQL = "SELECT * FROM table WHERE NumberFieldA = 1234 dsfas"

.....Form.RecordSource = sSQL

Run it throwing an error then fix the query it works and returns results!!!

I have tested this several times going back to my original file the only way I can fix it for 2007 is to break the query then fix the query!

This isn't an acceptable solution for me and I really want to know what is going on as I suspect this might not be the only problem during changeover to 2007!!

[cornall]

Ok I am getting a little further with this one I think.

Once I break the query causing the compiler error to be thrown, fix the error and save the file it no longer works in Access 2003.

I don't get any error messages at all the file just starts to open and the forms never appear. Access 2003 doesn't hang. I can't open the file in developer mode in 2003 either.

Access 2007 works fine and even though the file claims it is in 2000 format I think my breaking and fixing of the select statement is causing a recompile of the code?

I am going to test if the new file fixed for access 2007 will open in Access 2003 with the compatability pack installed.

Now something that is strange about this is that converting the file to 2007 format did not fix my problem form but inadvertantly forcing some sort of recompile did!

Will be back with news of the compatability pack test.

[cornall]

Compatability pack doesn't seem to fix anything in 2003 still can't open the updated file.

Also if I take a fresh copy of the original file and click compile in 2007 this doesn't seem to fix the problem?!? Only if I break the line of code then fix it does the form start to work in 2007!!!!!

[cornall]

I tried breaking then fixing my code in other places that caused a compiler error. This did not fix the form in 2007

Only breaking then fixing the SELECT statement which throws Run Time Error '3075' fixes the form for 2007 but breaks it for 2003!!!

I am lost this makes absolutly no sense at all what does a run time error cause Access to do that fixes the form in 2007 and breaks it in 2003.

[cornall]

Anyone got any ideas? This is weird!!!!