report record source
in [General]
|
From: deb on 10 May 2010 08:48 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 -- deb
From: Allen Browne on 10 May 2010 09:32 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.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "deb" <deb(a)discussions.microsoft.com> wrote in message news:7C4AAA55-5FAB-4E5D-85A7-CD2A6B000C9B(a)microsoft.com... > 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 > -- > deb
From: Douglas J. Steele on 10 May 2010 09:36 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. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "deb" <deb(a)discussions.microsoft.com> wrote in message news:7C4AAA55-5FAB-4E5D-85A7-CD2A6B000C9B(a)microsoft.com... > 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 > -- > deb
From: John Spencer on 10 May 2010 10:42 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 Douglas J. Steele wrote: > 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. >
From: Duane Hookom on 10 May 2010 12:38 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 "deb" wrote: > 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 > -- > deb
|
Next
|
Last
Pages: 1 2 Prev: How to convert AVI to iPad on Mac Next: Referencing an unbound text control in a footer |