Problems re; calling report query from within a form

  • Thread starter Thread starter Alan
  • Start date Start date
A

Alan

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
 
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.
 
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

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
Click to expand...
.
Click to expand...
 
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
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
 
Back
Top