Setting a report's recordsource?

[Maury Markowitz]

I'm sure there's a simple trick to this, but I can't figure it out...

I'm trying to set up a report's recordsource in response to changes on an
associated form. The current implementation has the report's Open event call
into a method on the form, which creates a SQL string and hands it back.

However this is very inflexible, because it means the report is tied to
_that_ form, and I'd rather have it able to be opened from any form, or even
other applications. I would like to re-write the code to have an open method
that is handed the filters, creates the SQL, and then opens the form. Like
this...

Public Sub OpenBlotter(Optional portfolioId As Long)
theWhere = " WHERE deleted=0 "
if Nz(portfolioId) <> 0 then theWhere = theWhere & " AND portfolioId=" &
portfolioId

DoCmd.OpenReport "rptBlotter", acViewPreview
DoCmd.Maximize

Reports("rptBlotter").RecordSource = "SELECT * FROM vReportBlotter " &
theWhere
end if

I don't get any errors, but I also don't get any records. Is there some sort
of refresh command I need to use?

Maury

 

[Molnár Tamás]

"Maury Markowitz" <[email protected]> az alábbiakat
írta a következő üzenetben
news:[email protected]...

 

[Dirk Goldgar]

I'm sure there's a simple trick to this, but I can't figure it out...

I'm trying to set up a report's recordsource in response to changes on an
associated form. The current implementation has the report's Open event
call
into a method on the form, which creates a SQL string and hands it back.

However this is very inflexible, because it means the report is tied to
_that_ form, and I'd rather have it able to be opened from any form, or
even
other applications. I would like to re-write the code to have an open
method
that is handed the filters, creates the SQL, and then opens the form. Like
this...

Public Sub OpenBlotter(Optional portfolioId As Long)
theWhere = " WHERE deleted=0 "
if Nz(portfolioId) <> 0 then theWhere = theWhere & " AND portfolioId="
&
portfolioId

DoCmd.OpenReport "rptBlotter", acViewPreview
DoCmd.Maximize

Reports("rptBlotter").RecordSource = "SELECT * FROM vReportBlotter " &
theWhere
end if

I don't get any errors, but I also don't get any records. Is there some
sort
of refresh command I need to use?

I'm pretty sure that specific approach won't work, because I don't believe
control will return to the calling code until after the report's Open event
has finished, at which point it's too late to to change the recordsource.

But why don't you just use the WhereCondition argument of the
DoCmd.OpenReport method to filter your report? E.g.,

theWhere = "deleted=0 " ' <--- NOTE CHANGE HERE
if Nz(portfolioId) <> 0 then theWhere = theWhere & " AND portfolioId=" &
portfolioId

DoCmd.OpenReport "rptBlotter", acViewPreview, , theWhere

If all you want to do is apply a pre-built filter string when you open the
report, that's what that argument is there for.

If you need to do more than just apply a filter -- for example, if you have
to totally change the recordsource, then if you are using Access 2002 or
later, the OpenReport method has an OpenArgs property that you can use to
pass the new recordsource query (or just an option code) to the report, and
you can get that value from the report's OpenArgs property in the report's
Open event.

 

[Maury Markowitz]

:

But why don't you just use the WhereCondition argument of the

Apparently because I'm an idiot! Thanks again Dick, I knew I was missing
something obvious!

Maury