Sub report based on SQL

[TinaCa]

My main report shows data straight from the table - no filter needed. However my subreport should only show data for the month/year entered by the user. I want to use a docmd.runsql in the subreport to do this. Will this work? Will it have any effect on the linked child fields with the main report? (sub report filter will not affect the linked fields between the two).

Thanks

[June7]

Never tried that. If it works, doubt it will affect the linking as long as the link fields are in the RecordSource.

Think I would use the Filter and FilterOn properties of the subform instead.

[TinaCa]

Not sure yet if it will work. I am getting an error "....requires an argument consisting of an SQL statement". Do I have a syntax error?

Here is the code:


Dim SQLstr As String

SQLstr = "SELECT CrewInformation.CrewMemberId, tblCompanyExtras.ExtraDate, " _
& "tblExtras.ExtrasDescription, tblCompanyExtras.ExtraCost, " _
& "Format([ExtraDate],#mm/yy#) AS extDt, tblExtras.ExtrasCategory " _
& "FROM tblExtras INNER JOIN (CrewInformation INNER JOIN " _
& "tblCompanyExtras ON CrewInformation.CrewMemberId = tblCompanyExtras.ExtraWorkId) " _
& "ON tblExtras.ExtrasId = tblCompanyExtras.ExtraId " _
& "WHERE (((Format([ExtraDate],#mm/yy#))=Format(Date(),#mm/yyyy#)) AND ((tblExtras.ExtrasCategory) Not Like 's')) " _
& "ORDER BY tblCompanyExtras.ExtraDate DESC;"


DoCmd.RunSQL SQLstr

[boblarson]

You don't run a select statement. So RunSQL is not what you want in this case.

[June7]

bob is right and I should have noted in reply. SELECT is not an action query so RunSQL isn't applicable. I was probably thinking OpenQuery when I read the post. However, even that will not influence the RecordSource of form.

What you can do is set the RecordSource property of the form and then Requery.
Or set the Filter and FilterOn properties.

[TinaCa]

thanks guys, I decided to just go with a query statement and set the filter on the command button that opens the report.