report record source

[deb]

Access 2003
I am trying to change the record source of my report using the below code.
I get error 2191. What is the correct way to set the record source of a
report?

If UnitNo = 0 Then
Dim strsql3 As String
Dim strsql4 As String
strsql3 = "SELECT t040Project.ProjectID,
t041ProjectDetails.ProjectDetailsID, " & _
" FROM t040Project INNER JOIN (t041ProjectDetails INNER JOIN t000Facts
" & _
" ON t041ProjectDetails.UnitID = t000Facts.UnitID) " & _
" ON t040Project.ProjectID = t041ProjectDetails.ProjectID " & _
" WHERE (((t040Project.ProjectID)=[Reports]![rClosure]![ProjectID])) " & _
" ORDER BY t000GFacts.Unit;"
Me.rClosureFuel.Report.RecordSource = strsql3
Me.rClosureFuel.Report.Requery

 

[Allen Browne]

Use the Open event of the report to set its RecordSource.
Any event after that is too late.

Unfortunately, it looks like you have some circular logic here. You are
referring to the value of the UnitNo. Access must have already fetched the
data to know the value, so that's going to be too late to change the
RecordSource (since its already fetched it.)

 

[Douglas J. Steele]

On what event are you trying to do this? The text associated with error 2191
says that you can't set the property after printing has started, and
suggests setting the property in the OnOpen event.

 

[John Spencer]

In addition, you have at least two errors in the SQL string. The first line
has an extraneous comma. The ORDER BY clause refers to a table t000GFacts
instead of t000Facts which is in the FROM clause.

strsql3 =
"SELECT t040Project.ProjectID, 041ProjectDetails.ProjectDetailsID " & _
" FROM t040Project INNER JOIN " & _
" (t041ProjectDetails INNER JOIN t000Facts " & _
" ON t041ProjectDetails.UnitID = t000Facts.UnitID) " & _
" ON t040Project.ProjectID = t041ProjectDetails.ProjectID " & _
" WHERE (((t040Project.ProjectID)=[Reports]![rClosure]![ProjectID])) " & _
" ORDER BY t000GFacts.Unit;"

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Duane Hookom]

Is there something you should be telling us about the two different reports?
Are they main and subreport?

I think having properties in one report depend on properties in another
report is a bad idea unless they are main and subreport.

 

[deb]

main report is rClosureContrReq
subreport is rClosureFuel (key is ProjectID) uses query qClosureFuel

If the Unit is 0 on the main report then show all Fuel for the ProjectID
else show only the Fuel where unit matches Unit on the Main report

The typo in the origional post should be t000Facts.
Will onOpen will work if I an using a MDE?

Thanks all!!
--
deb

Is there something you should be telling us about the two different reports?
Are they main and subreport?

I think having properties in one report depend on properties in another
report is a bad idea unless they are main and subreport.

--
Duane Hookom
Microsoft Access MVP

Access 2003
I am trying to change the record source of my report using the below code.
I get error 2191. What is the correct way to set the record source of a
report?

If UnitNo = 0 Then
Dim strsql3 As String
Dim strsql4 As String
strsql3 = "SELECT t040Project.ProjectID,
t041ProjectDetails.ProjectDetailsID, " & _
" FROM t040Project INNER JOIN (t041ProjectDetails INNER JOIN t000Facts
" & _
" ON t041ProjectDetails.UnitID = t000Facts.UnitID) " & _
" ON t040Project.ProjectID = t041ProjectDetails.ProjectID " & _
" WHERE (((t040Project.ProjectID)=[Reports]![rClosure]![ProjectID])) " & _
" ORDER BY t000GFacts.Unit;"
Me.rClosureFuel.Report.RecordSource = strsql3
Me.rClosureFuel.Report.Requery

 

[Duane Hookom]

You won't be able to change the record source of a subreport if it renders
more than once. I would use two copies of the same subreport and set the
visible properties based on the Unit being 0 or not.
--
Duane Hookom
Microsoft Access MVP

main report is rClosureContrReq
subreport is rClosureFuel (key is ProjectID) uses query qClosureFuel

If the Unit is 0 on the main report then show all Fuel for the ProjectID
else show only the Fuel where unit matches Unit on the Main report

The typo in the origional post should be t000Facts.
Will onOpen will work if I an using a MDE?

Thanks all!!
--
deb

Is there something you should be telling us about the two different reports?
Are they main and subreport?

I think having properties in one report depend on properties in another
report is a bad idea unless they are main and subreport.

--
Duane Hookom
Microsoft Access MVP

Access 2003
I am trying to change the record source of my report using the below code.
I get error 2191. What is the correct way to set the record source of a
report?

If UnitNo = 0 Then
Dim strsql3 As String
Dim strsql4 As String
strsql3 = "SELECT t040Project.ProjectID,
t041ProjectDetails.ProjectDetailsID, " & _
" FROM t040Project INNER JOIN (t041ProjectDetails INNER JOIN t000Facts
" & _
" ON t041ProjectDetails.UnitID = t000Facts.UnitID) " & _
" ON t040Project.ProjectID = t041ProjectDetails.ProjectID " & _
" WHERE (((t040Project.ProjectID)=[Reports]![rClosure]![ProjectID])) " & _
" ORDER BY t000GFacts.Unit;"
Me.rClosureFuel.Report.RecordSource = strsql3
Me.rClosureFuel.Report.Requery