A2007 - problem with queries on reports

[Damon Heron]

Is this a bug or is my db corrupted? I have queries for the record source
of many reports that were originally created in A 2003. When I open up the
query (not a stored query, but just the sql code) and make changes, and run
it, it runs fine. I save it and save the report. Then when I open the
report, it reverts back to the previous sql statement and my changes
disappear! I have ended up having to create stored queries and use them as
the record source, which works, but it is frustrating to not be able to have
a sql string as a record source on the report. Can anyone else duplicate
this? Here is an example:

SELECT DISTINCTROW tblSupplies.SupplyName,
tblInventoryTransactions.TransactionID,
tblInventoryTransactions.TransactionDate, tblInventoryTransactions.SupplyID,
tblInventoryTransactions.PurchaseOrderID,
tblInventoryTransactions.TransactionDescription,
tblInventoryTransactions.UnitPrice, tblInventoryTransactions.UnitsOrdered,
tblInventoryTransactions.UnitsReceived, tblInventoryTransactions.UnitsUsed,
tblInventoryTransactions.UnitsShrinkage, tblSupplies.CategoryID,
tblCategories.CategoryName
FROM (tblCategories INNER JOIN tblSupplies ON tblCategories.CategoryID =
tblSupplies.CategoryID) INNER JOIN tblInventoryTransactions ON
tblSupplies.SupplyID = tblInventoryTransactions.SupplyID
WHERE
(((tblInventoryTransactions.TransactionDate)>=[forms]![frmAllReports]![BeginningDate]
And
(tblInventoryTransactions.TransactionDate)<=[forms]![frmAllReports]![EndingDate])
ORDER BY tblCategories.CategoryName;

then I add an additional Where condition, so it reads:

..............WHERE
(((tblInventoryTransactions.TransactionDate)>=[forms]![frmAllReports]![BeginningDate]
And
(tblInventoryTransactions.TransactionDate)<=[forms]![frmAllReports]![EndingDate])
AND ((tblSupplies.CategoryID)=[forms]![frmAllReports]![cboCat])) ORDER BY
tblCategories.CategoryName;

It will execute as expected in the query window. Then I save the report and
when it opens, my changes are gone.

Damon

 

[Damon Heron]

I just discovered that Allen Browne has it listed as a bug on his web site -
http://allenbrowne.com/Access2007.html#Bugs

"If you click the Build button beside the RecordSource property for a form
or report, any changes are lost when you return from the query designer
(unless you move to another property.)"

Thanks to Allen and others who do the heavy lifting for the rest of us!

Damon

 

[Allen Browne]

Hopefully some of these issues will be fixed when SP1 comes out.

The press reports that they are working on a service pack and testing it at
the moment.