report record source

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
0
Utf
5/10/2010 12:48:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
864 Views

Similar Articles

[PageSpeed] 20

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@discussions.microsoft.com> wrote in message 
news:7C4AAA55-5FAB-4E5D-85A7-CD2A6B000C9B@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 

0
Allen
5/10/2010 1:32:48 PM
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@discussions.microsoft.com> wrote in message 
news:7C4AAA55-5FAB-4E5D-85A7-CD2A6B000C9B@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 


0
Douglas
5/10/2010 1:36:15 PM
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.
> 
0
John
5/10/2010 2:42:24 PM
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
0
Utf
5/10/2010 4:38:01 PM
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


"Duane Hookom" wrote:

> 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
0
Utf
5/10/2010 5:31:02 PM
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


"deb" wrote:

> 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
> 
> 
> "Duane Hookom" wrote:
> 
> > 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
0
Utf
5/10/2010 6:43:01 PM
Reply:

Similar Artilces:

SQL Reporting Services and GP 10
Hello: Is it possible to associate SQL Reporting Services reports with SOP invoices in GP 10.0 rather than with Report Writer? I know that Accountable Software has Forms Printer, but I believe that that only works with Crystal. And, I know that there are SRS reports written to display data from GP but I do not think that these reports will work in terms of printing invoices or orders from SOP. Thanks! childofthe1980s No not really.. The problem is that when an SOP or POP document is printed, GP needs to be updated. Accountable does that with their Crystal interface but I dont kno...

Reporting Hardware Specs for a 20TB Oracle DB
Hello, I have a requirement for a Reporting Solution for an Enterprise Reporting Solution for a 20TB Oracle (running on Unix) Datawarehouse that does about 150M Transactions a day. There are about 300 users and not more than 30 concurrent users. They already have a MOSS 2007 Installation so i need specs for the SSAS Server which will sit on its own server. My questions are; 1) What should the specs (RAM, Processor, Disk Space) of this SSAS Server be? 2) What would the specs of the SSRS Server be? 3) Any other special considerations? Thanks I'd buy the highest machi...

Report of sales by month based on a cross tab query
Hi, I have a cross tab query that will provide sales by month for 2008. Right now it is sales for January08. As there are sales for future months, they will appear as well. I want to create a table that has all of the months listed out already. Right now, on the first day of the month, I go into the report and add the new month. I want it so that all month are listed which I have done. The problem is that when I run the report, I get an error that says "the Microsoft Jet does not recognize February as a valid field name. After today, there will be February data but no March ...

Unique records in Pivot Tables
Excel 2000 I have data that stores Client Invitation to Tender and Supplier Contract details. Each Client ITT is uniquely identified. However we send out the same ITT to many suppliers so each request for service is given the same identifier, eg Reference Client/Supplier Contract Name Value Status AC/001 Client blah 4,000 pend AC/002 Client blah2 5,000 pend AS/001 Supplier blah3 3,000 pend AS/001 Supplier blah3 3,000 pend When I create my table by Client to get the total value pending ...

Does the New Worth Report have a fault
When I calculate my new worth in MS Money 2000 (old version I know) it adds my assets and liabilities instead of subtracting them. For example, if my savings, checking, investments total $10,000.00 and my liabilities total $5000.00, MS Money calculates my net worth as $15,000.00. Shouldn't my net worth be $5,000.00? Shouldn't it subtract my liabilities instead of add them? Please let me know if there is a glich in the software and how I can fix it. Also please let me know if I am financially inept. Thanks, -k In your example, the net worth should be $5,000.00. You may hav...

Records will not open
I am experiencing a problem with certain records within CRM that will not open. The system will try and try to open the record (pinging out two processors) until it finally times out. This is only with certain records but not others... Any suggestions? We experienced a similar problem. We created those records again (Leads) and deleted the old ones... all worked fine then "Phillip Sperry" <phillip.sperry@mxsecure.com> wrote in message news:9FeSb.10804$QJ3.6760@fed1read04... > I am experiencing a problem with certain records within CRM that will not > open. The syst...

Problems with reports on Money 2004
I am having problems getting onto the reports page as everytime it crashes. I have tried to repair using the disk and uninstalled and re-installed and it still doesn't work. I am told that there isn't a new UK version available so I cannot upgrade. Can anybody help In microsoft.public.money, Jayne Morris <Jayne Morris@discussions.microsoft.com> wrote: >I am having problems getting onto the reports page as everytime it crashes. I >have tried to repair using the disk and uninstalled and re-installed and it >still doesn't work. I am told that there isn't a ne...

Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths. Is this possible? -The 1st sub-report has 1 column that occupies the entire width of the page -The 2nd sub-report can fit 2 columns in the page width -The 3rd sub-report can fit 3 columns in the page width Subreports can have any number of columns that don't have to be the same from one to another. Typically your columns should display across then down in order to render properly as a subreport. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > I want to create a r...

Report Sum
I am working on a cost study form for a body shop. Each vehicle will have its own page. I have to be able to add up to 15 different list part totals as well as 15 different net part totals. I then need to add the 7% tax of the list part total to the net parts total. I would love some input on what would be the easiest way to achieve this because I then have to generate a report with by the different companies showing total parts, total labor and total profit ratio. Usually, you want to text put boxes in footer of the form. You have to pull the footer down on the bottom to have...

Categories & Reports in MM 2002? #2
I created a Business classification to keep track of business expenses. Yet, I cannot use any Reports for my Business classification. How do I just limit the Reports for the Business classification? Christopher Paul Billows http://www.accidentaldesign.com "Life is not fair, it is interconnected" -- myself ...

Deleting Linked Source(s)
In Excel 2000, how do you delete a link Source(s)? I know you can 'clear contents' in each destination cell to achieve this, but is there a simpler way. Can be difficult to determine which cells are in fact destination cells. Thanks, Jim This may help you http://support.microsoft.com/default.aspx?scid=kb;en- us;214127 >-----Original Message----- >In Excel 2000, how do you delete a link Source(s)? I know >you can 'clear contents' in each destination cell to >achieve this, but is there a simpler way. Can be difficult >to determine which cells are in fac...

Print employee record summary Inquiry report
How do i print the employee record summary Inquiry report? There's not a report you can print from the window. You can either do a screen shot of it or create a custom report. -- Charles Allen, MVP "Hazvi" wrote: > How do i print the employee record summary Inquiry report? ...

Report on a specific printer, bug in access 2007?
Dear all, in access 2007, I can click the "page setup" menu then the "page setup" button, and choose to print the report on a specific printer. However, after saving the report and re-opening it, he still prints on default printer. The specific printer setting is not saved with the report (this worked with all previous versions of access, it seems the specific printer setting has no effect anymore). Does someone know where I could find a patch or a workaround for this? (no updates are found through office update, and this might be critical for some applications). Regard...

Filtering data by custom fields in pivot tables from Visual Report
I have recently upgraded to MS Project 2007 and like the visual reporting feature, except I really need to filter and group the resulting pivot table in Excel by custom fields. For example, for task usage, I cannot filter my pivot table to show only the tasks that have costs > $0, thus my table ends up showing too many lines and a lot of 0's. I would also like to group by department/ function which I put in a custom text field. How do I do this? I saw related posts about saving the file as an Access database and then creating the pivot table by connecting to the datab...

sales report by split sales persons
we have situations where 2 different sales persons sell to the same customer, depending on product group. We select the specific sales person when we enter the sales order. Unfortunately, later when we want to run a sales report, it assigns all the sales to whatever salesperson is shown in the Cards as the default sales person. We need a report that assigns sales to sales persons based on the data from the individual sales orders ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click th...

HELP! Record Macro in TOOLS > MACRO is gone!
I was in Customize function trying to enable the Stop Recording toolbar and now in TOOLS > MACRO the Record Macro option is gone. I'm working on a project and I need this back. Can someone help? I'm using Excel 2003. Thanks, Joe M. Try this: In the customize window > click the Options tab > push the reset toolbars & menus button. Exit excel and re-load it Micky "Joe M." wrote: > I was in Customize function trying to enable the Stop Recording toolbar and > now in TOOLS > MACRO the Record Macro option is gone. I'm working on a ...

How to record sell of asset & see true cost?
I had bought a car 4 years ago on loan and some downpayment. And recently I sold it. I had the following accounts setup in Money 2004 Delux: 1. Car Asset with only Opening Balance of $30,000 2. Car Loan account which tracked Principal and Interest amounts (the loan is fully paid) 3. Auto category as Expense category with sub categories such as Purchase Cost (downpayment), Fuel costs, Insurance, etc. All car expenses were tracked under one of these sub categories. I would like to record the sell of the car. Where would I record it and how would I categorize so that I can get...

Cost Variance Report and Smartlist
We currently use the Enter/Match Invoice screen to enter payable invoices. I understand that when prices differ, a cost variance report is created/printed. Is there a way to create this report in Smartlist? We currently link the SOP / POP by using the purchase button. What is happening is, if the linked Purchase Order line is received at an incorrect price (found while keying the vendor invoice), when the link is used to drill back from the sales order to the purchase order the incorrect price shows. We would like to use Smartlist to see the original purchase order price and the ...

Error Message when viewing CRM 4.0 Reports
We recieve the following event error when attempting to access any report. http://<reportserver>/reportserver failed. Error: The request failed with HTTP Unauthorized 401 Any suggestions? This is a new install. "Chris Timms" <ChrisTimms@discussions.microsoft.com> wrote in message news:0E4DDBE2-58EC-408F-82E8-3B7EF531D392@microsoft.com... > We recieve the following event error when attempting to access any report. > > http://<reportserver>/reportserver failed. Error: The request failed with > HTTP Unauthorized 401 > > Any suggestions? Thi...

Explanation of Money Performance Report
If i run a perfomance report there are 2 columns for judging performance - "%Rate of Return" & "Annual % Return". I am having a problem understanding the meaning of each column. If I run the report for exactly 1 year then both columns show the exact same numbers for indexes such as Dow Jones Indutrial Average or Nasdaq Composite which I have in my watch list. In the same report for exactly 1 year the 2 columns have very different numbers for my stocks and mutual funds. For example I have a mutual fund which shows 1.28 for % Rate of Return and 21.32 for Annu...

Q: How to paste HTML source directly into HTML message?
I'm in a situation where I want to take HTML source from a web page and send it to someone so that the email looks like the web page. I can't figure out any way to get the source into an HTML as *source* and not text. For example I want to copy & paste this: <strong>Hi there!</strong> And have it show up like this (in bold, of course): Hi there! Any thoughts? Thanks in advance! ...

Continuous Form, Window Top Record Ordinal
On continuous view forms, there should be a form property that has the recordset ordinal of the top-most record currently displayed in the form’s window. For example, if a recordset contains 100 records and the CurrentRecord is 5 and the user scrolls down until the CurrentRecord disappears off the top of the screen, what is the recordset ordinal for the record that is now at the top of the window? Left clicking on the scrollbar slider will display “Record: x of y”, but there should be a property that contains the value of x for code purposes. Perhaps call it “WindowTopRecord...

Extracting records from a list
I would like to extract records from a long list of 500 rows. The list is already sorted by the first column. There is a maximum of four possible values for the first column with multiple values for the other ones. I would like to create four different lists, each with only one of the possible value in its first column by extracting them from the "master" list. Any help or pointer will be gratefully appreciated. Here is some very simple generic code Code: -------------------- Set SourceSht = Sheets("Sheet1") Set DestSht = Sheets("Sheet1") ...

Laying out a Report
I have a database with two tables called Fillers and issues linked. I want to make a report on issues under two different sections of the filler. Fillers Table Model = 6000 S/N 235 J/N 12 Date started Date complete Issues Table Fed/sep Date Part issue drawing issue bill of material issue assingned to plan of action Filler Date Part issue drawing issue bill of material issue assingned to plan of action Any suggestions would be appreciated What field is linking Fillers and issues? It looks like you want two records from issuses for every filler record. Is this correct? If so, h...

Report to show totals for each day of month
Greetings, I have an Excel spreadsheet that I use to capture: Column A) Day of the month (1-May, 2-May, etc.) Column B) How many clients were in residence on each day (Occupancy) Column C) Total Capacity (27) Column D) Shows a "1" if the Occupancy is 90% or more of the Total Capacity, else "0" The Totals row shows: Column B) The average Occupancy for the month Column D) Totals the times there is a "1" in Column D Is there a way for me to capture this data in a Report? I've got a query set up that uses these expressions: For AdmitDate: <=[Dat...