Access 2003 - Query As Report Source

[Jcase]

RESOLVED!


Hello guys! I have an interesting problem.

Previously we would generate reports for clients by pulling queries from a table and using them as the report's source and life was good.

I received a special request from a particular client and had to automate creating the source table at run time.

I populate the table fine and the reports are created with the correct field being used as the source, except there is one problem: They don't all work.

Some of the reports work fine, they open up and I see the data that needs to be there. Yet other reports I will get 'no records found' or similar errors; here's the catch, if I go into the record's source for a client that doesn't work and do anything (press the spacebar then delete, move the cursor around with the arrow keys) and try to open the report again - it works. So apparently everything is coded properly.

I've reviewed the code and the report source queries in the table and they look fine...and work fine if I manually press the spacebar when I go into the report's source field in the design view of the report. These actions can be performed anywhere on the line, so it's not like the SQL is yelling at me.



Any ideas guys? This one is puzzling me.

 

[Balr14]

Are all reports are created by one action and if so, what would that action be?

 

[Jcase]

The reports already exist in a database.

Basically the code selects the reports the client needs, then references the table I created to set the filename, the report source, filter, etc etc.

The reports are then outputed to a compiled Database where we run a macro that converts the reports to PDF.

This is what gets inserted to the data source field of the report

SELECT REPLACE(UserName, ".Name", " ") AS UserName1, * FROM tblDA_Results WHERE USERNAME LIKE ("*.Name*");

(The actual logic isn't the problem, it works exactly as intended)

Now if I go into a report that doesn't work after it's created and put the cursor anywhere in that query and hit spacebar, then re-open the report, it works fine.

I'll have a bit of time to work on this today, I'll keep you guys updated if I find out why it's doing this.

 

[Balr14]

So, the problem occurs because the affected report is not the current active object?

 

[Jcase]

So, the problem occurs because the affected report is not the current active object?

I'm not sure if that has anything to do with it not working. After all the code has been run and I go into the Database that contains all the completed reports I can double click on some of the reports and they open fine. When I try opening other reports generated in the same batch they give me the error, I go into the Detail mode and hit the spacebar(anywhere) in the data source field, save the report, and double click again and the report works fine.

The code that takes my table's information to generate the reports was before I came on board. But I followed the stipulations for the table to work with that code.

Sorry if I seem to be saying the same thing over and over again, but this problem is kinda odd to explain. Thanks again for any help.

 

[Jcase]

RESOLVED!

Thanks for trying to help Balr:

Essentially the filter field was populated with some redundant exclusions (NOT Station = 'List of stations that were already excluded') which for some unknown reason to me caused this weird error.

Thanks guys.

 

[Balr14]

I'm glad you found it because that's not something I'd have ever guessed. The times I've experienced things similar (sort of) have been timing related.