report record source

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vsn - 12 Jan 2007 10:33 GMT

Hi there,

I still do not get it completely with the report record source via the
'docmd.openreport'.

I created and sql statment in the variable stgFilter

stgFilter = "" _
& "SELECT tblProjects.[Location / Project] AS Loc,
tblProjects.ClientName, tblProjects.ContactPerson, tblProjects.StartDate,
tblProjects.Enddate, tblProjects.fProjectLocation, Left([Loc],2) AS Area" _
& "FROM tblProjects LEFT JOIN tblProjectText ON
tblProjects.[Location / Project] = tblProjectText.fProjectID" _
& "WHERE ((tblProjects.Enddate) >=#13-03-2004# And
((LocationCheck([tblProjects]![Location / Project])) = -1) And
((tblProjects.fDateCancel) Is Null)) Or (((tblProjects.Enddate) Is Null) And
((LocationCheck([tblProjects]![Location / Project])) = -1) And
((tblProjects.fDateCancel) Is Null))" _
& "ORDER BY tblProjects.[Location / Project]" _
& "WITH OWNERACCESS OPTION;"

Then i launch it to an report which does not have a record source like this

DoCmd.OpenReport stgReport, acViewPreview, stgFilter, stgWhere,
acWindowNormal, stgArgument

Still the report does not get this sql/stgFilter as record source?

If the report does have a record source the new one launched via the doopen
command from the form does not over rule the original one.

Idea is that the stgFilter is buildup from information which the user
completes on the form.

What am i doing wrong?

Thx,
Ludovic

Reply to this Message

Stefan Hoffmann - 12 Jan 2007 10:50 GMT

hi Ludovic,

> I created and sql statment in the variable stgFilter
> stgFilter = "" _
> & "SELECT tblProjects.[Location / Project] AS Loc,
> tblProjects.ClientName, tblProjects.ContactPerson, tblProjects.StartDate, ...

This is a complete SQL select statement.

> Then i launch it to an report which does not have a record source like this
> DoCmd.OpenReport stgReport, acViewPreview, stgFilter, stgWhere,
> acWindowNormal, stgArgument

The [FilterName] variable is the optional _name_ of a query, not a SQL
statement instead.

You may use the [OpenArgs] to pass your SQL statement:

DoCmd.OpenReport stgReport, , , , stgFilter

And use the following On Open event code in your report:

Private Sub Report_Open(Cancel As Integer)

If Len(Nz(OpenArgs)) > 0 Then
RecordSource = OpenArgs
End If

End Sub

to pass your customized SQL to it.

mfG
--> stefan <--

Reply to this Message

Vsn - 12 Jan 2007 18:19 GMT

Thx, Stefan,

I thought I could put a sql statment in to the filter variable of the
openreport method.

Ludovic

> hi Ludovic,
>
[quoted text clipped - 30 lines]
> mfG
> --> stefan <--

Reply to this Message

Larry Linson - 12 Jan 2007 18:22 GMT

> Hi there,
>
[quoted text clipped - 33 lines]
>
> What am i doing wrong?

If I recall correctly, in some earlier version(s?) of Access, what you want
to do would work -- a complete SQL statement or Query specified as Filter
would either be supplied to a Report without a RecordSource or would
override the Report's RecordSource. But, that was never the intent of the
Filter argument; it was to supply a "filter" or "criteria" to limit the
Records selected for display, and at some point in Access' history the
(admittedly very convenient) "defect" was corrected. Check the Help on
DoCmd.OpenReport for a description of the filter argument.

Larry Linson
Microsoft Access MVP

Reply to this Message