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

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

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
.

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

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

.