Changing a MS-Access report's RecordSource property

[Grygonos]

I'm trying to have a perl app print an access report based on a set of queries it just ran. The result set of all those queries is in a table. I just need to change the standardized report's recordsource to be the table I just created. I can make it print the report, so I know its opening correctly and creating the ole object correctly. Here's what I've tried so far. My error is:

Can't use an undefined value as a HASH reference at C:\test_report_print.pl line 29.

If I try and remove the curly braces on the RecordSource property I get this error

Can't call method "RecordSource" on an undefined value at C:\test_report_print.pl line 29.

Any ideas at all? below is my code

sub printReport
{
    my ($location) = @_;
    
    my $path = "XXX";

    #get new object
    my $access = Win32::OLE->new('Access.Application') or die "$!";
    
    #open the database
    $access->OpenCurrentDatabase($path);
    
        #change the record source to the new table
        $access->{Reports}->Item(1)->{RecordSource} = $location;
    
    #Print the report
    $access->DoCmd->OpenReport("XXX");

    
}

Thanks in advance for any help.

[VSarkiss]

It appears your $access->{Reports}->Item(1) is not defined. First make sure (using, say, Object Browser) that you do have an item numbered 1. Then try referencing it as $access->Reports(1)->{RecordSource}.

You may also want to scour Dave Roth's site for more info.

[Tomte]

check $access->{Reports}->Item(1), it looks as if this is undef;

regards,
tomte

---

Hlade's Law:

If you have a difficult task, give it to a lazy person --
they will find an easier way to do it.

[Grygonos]

I have tried doing it without the curly braces on Reports and it doesn't make any difference.

The application method has a property called reports which is a report collection for the application (which I would guess means in relation to the current database, which should be the one I opened) Then the reports object has a method Item(n) where n is the item you want to view from the collection. I know the report is defined and is there... and it starts at subscript 1.. and not 0. sorry I double checked and M$ is inconsistent... for some ole it is 1 and others 0 .. this happens to be zero... and it says of currently open reports... maybe I have to find a method to open it ;)

There is a method in Access.Application called currentDB which returns a DAO object of type Database. DAO object database doesn't seem to have any method of referencing the reports contained in the database... any more ideas?

[Tomte]

Nope, I'm sorry :(
I'm WinFree? since two and a half years or so, I just spotted the obvious cause. A quick glance over the net didn't make me smarter about OLE-Programming...well, how could it?

again, sorry...I guess following VSarkiss advice and scanning available info (along with putting a lot of debug-prints/dies into the code) is the stony way you have to go

regards,
tomte

---

Hlade's Law:

If you have a difficult task, give it to a lazy person --
they will find an easier way to do it.

[yosefm]

Just a guess: If you want to access the first item, maybe you should use Item(0) - as most arrays start with a zero.

[MZSanford]

For exploration you may want to tey something like :

use Win32::OLE qw(in);

# ...

my @reports = in $access->{Reports};
printf("Found %d reports\n",scalar(@reports));

This way you can see how many reports are returned, and use them from the array. I have found that some of the office COM objects have odd collections and i end up having to use indexes that don't seem correct to the non-Gatesian mind.

---

from the frivolous to the serious

[Grygonos]

I took what you did with the showing how many reports are there... and bastardized it slowly back to what my original idea was... and somehow it worked this time... here's how it's done.

#!/Perl/bin/perl

use Win32::OLE;

    my $path = "XXX";        

    my $access = Win32::OLE->new('Access.Application');
    
    $access->OpenCurrentDatabase($path);
    
        #Don't show the window    
        $access->{Visible} = 0;

        #Open the report in design mode
    $access->DoCmd->OpenReport("XXX",1);
    
        #Change the recordsource
    $access->Reports->Item(0)->{RecordSource} = "XXX";
    
        #Save the report
    $access->DoCmd->Save(3,XXX);
    
        #Quit
    $access->DoCmd->Quit(2);

Thanks to all who offered assistance with this quite sucky problem. Much appreciate guys and gals... thank you.

sorry about the comment indention... something was lost in the transalation(read:paste) from Textpad :P

[jsprat]

Did you realize that the Reports object returns a collection of all open reports?

Move the OpenReport before the change and make sure you open it in acViewDesign (acDesignView = 1), make your change, then re-open it in acViewNormal (print) or acViewPreview.

A couple of notes:

1. I usually use Win32::OLE::Const "Microsoft Access 8.0"; to import the constants. (Yeah, I'm stuck with Access 97 ;)
2. You can access the report with $access->Reports("Report Name") as well as using the zero-based array subscripts.

HTH...