If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problems re; calling report query from within a form
I am getting a problem while trying to call the following
report query called "zel_test" in the application: SELECT transactions.period, transactions.year, transactions.tran_date, transactions.description, transactions.debit_amt, transactions.credit_amt, transactions.balance FROM transactions, maintab WHERE (((transactions.rserial)=(maintab.rserial)) And ((maintab.tenant)=forms!frmarrearsStatement! txt_frm_tenant)) ORDER BY transactions.tran_date DESC; from within a VBA program in a form called "frmarrearsstatement" using the stament: DoCmd.OpenQuery "zel_test", , acReadOnly The value for "txt_frm_tenant" is assigned somewhere in the program as Txt_frm_tenant = mrstctx!tenant where mrstctx is a recordset. (I have double-checked that the value is assigned correctly!!) The problem shows up as soon as program execution is to generate the report on the basis of the "zel_test" query. A message box with "Enter Parameter Value" opens asking a value for: "forms!frmarrearsStatement!txt_frm_tenant" It looks as if the report query cannot read the "txt_frm_tenant" value from the parent forms, and I could not find any solution to it. thanks. Alan |
#2
|
|||
|
|||
Problems re; calling report query from within a form
Alan,
Is the frmarrearsStatement form still open at the point where you are trying to print the report? In any case, what is the purpose of opening the query? Normally DoCmd.OpenQuery would only ever be used to run an action query, such as an Append Query or an Update Query. This is not directly related to your problem/question, but I have also noticed an unusual structure within the query... I think it would be better and more efficient to join the tables on the rserial field, so the SQL would look like this: SELECT transactions.period, transactions.tran_year, transactions.tran_date, transactions.description, transactions.debit_amt, transactions.credit_amt, transactions.balance FROM transactions INNER JOIN maintab ON transactions.rserial = maintab.rserial WHERE maintab.tenant = [Forms]![frmarrearsStatement]![txt_frm_tenant] Notice I have also changed the name of the year field... "year" is a reserved word in Access (i.e. has a special meaning), and as such should not be used as the name of a field or control or database object. And notice that I have removed the ORDER BY clause from the query... if the purpose of the query is to be the record source for your report, the sorting should be done in the report, not the query. -- Steve Schapel, Microsoft Access MVP Alan wrote: I am getting a problem while trying to call the following report query called "zel_test" in the application: SELECT transactions.period, transactions.year, transactions.tran_date, transactions.description, transactions.debit_amt, transactions.credit_amt, transactions.balance FROM transactions, maintab WHERE (((transactions.rserial)=(maintab.rserial)) And ((maintab.tenant)=forms!frmarrearsStatement! txt_frm_tenant)) ORDER BY transactions.tran_date DESC; from within a VBA program in a form called "frmarrearsstatement" using the stament: DoCmd.OpenQuery "zel_test", , acReadOnly The value for "txt_frm_tenant" is assigned somewhere in the program as Txt_frm_tenant = mrstctx!tenant where mrstctx is a recordset. (I have double-checked that the value is assigned correctly!!) The problem shows up as soon as program execution is to generate the report on the basis of the "zel_test" query. A message box with "Enter Parameter Value" opens asking a value for: "forms!frmarrearsStatement!txt_frm_tenant" It looks as if the report query cannot read the "txt_frm_tenant" value from the parent forms, and I could not find any solution to it. thanks. Alan |
#3
|
|||
|
|||
Problems re; calling report query from within a form
Thanks Steve,
Yes, the frmarrearsStatement form is open at the point the report query is called. The purpose of opening the query is to display on the screen the set of transaction records relating to the value the "txt_frm_tenant" field assumes at the time the form was run. Thanks about your comments on the SQL structure as well and I will modify it accordingly. I simply picked it up from the sql generated by ACCESS's report wizard. Alan -----Original Message----- Alan, Is the frmarrearsStatement form still open at the point where you are trying to print the report? In any case, what is the purpose of opening the query? Normally DoCmd.OpenQuery would only ever be used to run an action query, such as an Append Query or an Update Query. This is not directly related to your problem/question, but I have also noticed an unusual structure within the query... I think it would be better and more efficient to join the tables on the rserial field, so the SQL would look like this: SELECT transactions.period, transactions.tran_year, transactions.tran_date, transactions.description, transactions.debit_amt, transactions.credit_amt, transactions.balance FROM transactions INNER JOIN maintab ON transactions.rserial = maintab.rserial WHERE maintab.tenant = [Forms]![frmarrearsStatement]! [txt_frm_tenant] Notice I have also changed the name of the year field... "year" is a reserved word in Access (i.e. has a special meaning), and as such should not be used as the name of a field or control or database object. And notice that I have removed the ORDER BY clause from the query... if the purpose of the query is to be the record source for your report, the sorting should be done in the report, not the query. -- Steve Schapel, Microsoft Access MVP Alan wrote: I am getting a problem while trying to call the following report query called "zel_test" in the application: SELECT transactions.period, transactions.year, transactions.tran_date, transactions.description, transactions.debit_amt, transactions.credit_amt, transactions.balance FROM transactions, maintab WHERE (((transactions.rserial)=(maintab.rserial)) And ((maintab.tenant)=forms!frmarrearsStatement! txt_frm_tenant)) ORDER BY transactions.tran_date DESC; from within a VBA program in a form called "frmarrearsstatement" using the stament: DoCmd.OpenQuery "zel_test", , acReadOnly The value for "txt_frm_tenant" is assigned somewhere in the program as Txt_frm_tenant = mrstctx!tenant where mrstctx is a recordset. (I have double-checked that the value is assigned correctly!!) The problem shows up as soon as program execution is to generate the report on the basis of the "zel_test" query. A message box with "Enter Parameter Value" opens asking a value for: "forms!frmarrearsStatement!txt_frm_tenant" It looks as if the report query cannot read the "txt_frm_tenant" value from the parent forms, and I could not find any solution to it. thanks. Alan . |
#4
|
|||
|
|||
Problems re; calling report query from within a form
Alan,
Since I can't immediately spot any reason for the problem, I need to clarify. I am not familiar with the term "report query", but I assumed you were referring to the quey in this way, since it serves as the Record Source of your report. Yo8u have described the process of opening the datasheet of this query. But then your problem, as stated, refers to the parameter when you try to print the report, which really has nothing to do with the opening of the query datasheet. So my question was, Is the frmarrearsStatement form still open at the point where you are trying to print the report? By the way, I understand your reason for opening the query, to see the records. Normal practice would be to open a form based on the query for this purpose, or else open the report in Preview. -- Steve Schapel, Microsoft Access MVP alan wrote: Thanks Steve, Yes, the frmarrearsStatement form is open at the point the report query is called. The purpose of opening the query is to display on the screen the set of transaction records relating to the value the "txt_frm_tenant" field assumes at the time the form was run. Thanks about your comments on the SQL structure as well and I will modify it accordingly. I simply picked it up from the sql generated by ACCESS's report wizard. Alan |
#5
|
|||
|
|||
Problems re; calling report query from within a form
Steve
Yes, by "Report Query", I meant to refer to the query that generates the report. All I wanted to do is displaying the records on the VDU using datasheet views. The program would not do that unless I supply the "tenant" field interactively. I am therefore not sure that the problem starts at the point of printing the report. It rather looks to me that the SQL statement is not receiving the parameter from the form's field. As you rightly said "Normal practice would be to open a form based on the query for ...", and that is what I exacly did. The SQL statement I sent to you was generated obtained by ACCESS' report wizard and what I attempted to do in my VBA program was to call this report by name from within the form providing the parametre for report generator. That is when the "Parameter value request" box pops up asking to enter "Forms!...!Txt_frm_tenant" ---This is the bug. If I supply the value for this parameter the program execution resumes and completes successfully and I can view the records in datasheet view. Alan -----Original Message----- Alan, Since I can't immediately spot any reason for the problem, I need to clarify. I am not familiar with the term "report query", but I assumed you were referring to the quey in this way, since it serves as the Record Source of your report. Yo8u have described the process of opening the datasheet of this query. But then your problem, as stated, refers to the parameter when you try to print the report, which really has nothing to do with the opening of the query datasheet. So my question was, Is the frmarrearsStatement form still open at the point where you are trying to print the report? By the way, I understand your reason for opening the query, to see the records. Normal practice would be to open a form based on the query for this purpose, or else open the report in Preview. -- Steve Schapel, Microsoft Access MVP alan wrote: Thanks Steve, Yes, the frmarrearsStatement form is open at the point the report query is called. The purpose of opening the query is to display on the screen the set of transaction records relating to the value the "txt_frm_tenant" field assumes at the time the form was run. Thanks about your comments on the SQL structure as well and I will modify it accordingly. I simply picked it up from the sql generated by ACCESS's report wizard. Alan . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
6 Tables, 1 Report, W/O 6 Qrys | Andy | Setting Up & Running Reports | 9 | June 29th, 2004 09:52 PM |
Open a report based on which query I select | Sierras | Setting Up & Running Reports | 8 | June 24th, 2004 05:43 PM |
Is my problem Query,Form or a Macro? | Rebecca | General Discussion | 5 | June 15th, 2004 08:17 PM |
running a query in a form | juliec | Running & Setting Up Queries | 1 | June 8th, 2004 02:39 AM |
Query Form: Print Report | Dennis | Running & Setting Up Queries | 1 | June 6th, 2004 01:08 PM |