report record source

[Vsn]

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

 

[Stefan Hoffmann]

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 <--

 

[Vsn]

Thx, Stefan,

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

Ludovic

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 <--

 

[Larry Linson]

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?

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