Prev: Hyperlinks in command button
Next: Access 2010 Forms - Converting 2007 Client into 2010 Web
From: Patrick Hatton on 26 Mar 2010 15:00
Yes, the form is open. No crosstabs. Yes, I can run the sub-report query
without a problem. Form closed, no data. Form open, good data.

"Daryl S" wrote:

> Patrick -
>
> When you run the reports, is the form open? If not, open it before running
> the reports.
>
> Also, are you using any cross-tab queries in your reports? They have issues
> with some parameters which require you to define the fieldtype within the
> queries with the parameters (even though you are pulling the data from a
> form, you can define these as parameters, e.g.
> [forms]![frmreportform]![startmonth] can be a parameter name.
>
> Just to clarify. If the form is open, you can run the query that contains
> the parameters with no problem. Can you run the query that is in the
> subreport without a problem (the one based on the initial query)? The report
> itself never runs without asking for the parameters, even if the form is open?
>
> --
> Daryl S
>
>
> "Patrick Hatton" wrote:
>
> > Daryl,
> >
> > My apologies, I am using the term code too loosely. There is code that runs
> > a report. The report is made up of 8 sub-reports. The sub-report control
> > source is a query that uses another query that tries to pull dates from an
> > open form. When the query is run during the report process it will prompt
> > for data vs running the query manually.
> >
> > "Daryl S" wrote:
> >
> > > Patrick -
> > >
> > > Can you post the statement for running the query?
> > >
> > > --
> > > Daryl S
> > >
> > >
> > > "Patrick Hatton" wrote:
> > >
> > > > Yes, the form is open when running.
> > > >
> > > > The query is using: Between [forms]![frmreportform]![startmonth] And
> > > > [forms]![frmreportform]![endmonth]
> > > >
> > > > This is the strange part. I can run the query with the form closed, no data.
> > > > Open the form, run the query, works fine. When this same thing is done from
> > > > the code, it doesn't work.
> > > >
> > > > "Daryl S" wrote:
> > > >
> > > > > Patrick -
> > > > >
> > > > > Is the form open when you are trying to just run it through the code? If
> > > > > not, then open the form first. You can make the form inivisible if you don't
> > > > > want the users to see it.
> > > > >
> > > > > --
> > > > > Daryl S
> > > > >
> > > > >
> > > > > "Patrick Hatton" wrote:
> > > > >
> > > > > > I have a routine that uses data from a form. Each time I run it, it asks for
> > > > > > input on any field contained in the form.
> > > > > >
> > > > > > If I open the form then manually run the query it works fine. When I let it
> > > > > > run throught the code, it cannot see the fields.
> > > > > >
> > > > > > Your suggestions are greatly appreciated.
> > > > > >
> > > > > > Patrick
From: Marshall Barton on 26 Mar 2010 15:05
All I can think of is that something is closing the form.
Probably, the first place to look is the procedure that
opens the report or in the report's Open event.
--
Marsh
MVP [MS Access]


Patrick Hatton wrote:
>I used the one as an example. The sub-report control source is a query.
>That query uses another query that has the parameter listed. If I run this
>query manully with the form open, it pulls back good data. Letting the query
>run as the report is opened prompts for the data. This same problem exist on
>other queries in other sub-reports that are looking for data contained in
>another form. By bombing, I am refering to the prompt, as in, not working as
>expected.
>
>"Marshall Barton" wrote:
>
>> Originally, you said "it asks for input on any field
>> contained in the form", but now you are saying that only one
>> subreport (record source query?) is exhibiting the problem.
>> Please try to be precise when describing what you have and
>> what is happening.
>>
>> I still don't see the code in your "routine"
>>
>> When you say the query "is bombing", what actually happens?
>>
>> If you are prompted for something, it means that that
>> something is misspelled or the name of the form or control
>> can not be found.
>> --
>> Marsh
>> MVP [MS Access]
>>
>>
>> Patrick Hatton wrote:
>> >not sure how to explain this. I have a large report with many subreports.
>> >The query that is bombing is part of a sub-report. The process requires the
>> >form in question to be open but still the data fields are not recognized.
>> >
>> >Between [forms]![frmreportform]![startmonth] And
>> >[forms]![frmreportform]![endmonth]
>> >
>> >
>> >"Marshall Barton" wrote:
>> >> Patrick Hatton wrote:
>> >> >I have a routine that uses data from a form. Each time I run it, it asks for
>> >> >input on any field contained in the form.
>> >> >
>> >> >If I open the form then manually run the query it works fine. When I let it
>> >> >run throught the code, it cannot see the fields.
>> >>
>> >> We'll need to see the code that runs the query and the query
>> >> to have a chance to figure out what might be wrong.
>> >>
>> >> At this point, all I can say is that when you run a query
>> >> with form based parameters, the form MUST be open or you
>> >> have to use a method that supplies the parameter values in
>> >> the code.
>> .
>>

From: John W. Vinson on 26 Mar 2010 17:22
On Fri, 26 Mar 2010 12:00:09 -0700, Patrick Hatton
<PatrickHatton(a)discussions.microsoft.com> wrote:

> Form closed, no data

Exactly. That's what you're asking for.

If the form is closed the query upon which the report is based has no criteria
and cannot return any records.
--

John W. Vinson [MVP]
From: Daryl S on 29 Mar 2010 10:18
Patrick -

What are you expecting from the query/report if the form isn't open?

If the form isn't open, then Access treats those as parameters that have not
been provided, and it will ask the user for the values. This is expected
behavior (as John pointed out).

--
Daryl S


"Patrick Hatton" wrote:

> Yes, the form is open. No crosstabs. Yes, I can run the sub-report query
> without a problem. Form closed, no data. Form open, good data.
>
> "Daryl S" wrote:
>
> > Patrick -
> >
> > When you run the reports, is the form open? If not, open it before running
> > the reports.
> >
> > Also, are you using any cross-tab queries in your reports? They have issues
> > with some parameters which require you to define the fieldtype within the
> > queries with the parameters (even though you are pulling the data from a
> > form, you can define these as parameters, e.g.
> > [forms]![frmreportform]![startmonth] can be a parameter name.
> >
> > Just to clarify. If the form is open, you can run the query that contains
> > the parameters with no problem. Can you run the query that is in the
> > subreport without a problem (the one based on the initial query)? The report
> > itself never runs without asking for the parameters, even if the form is open?
> >
> > --
> > Daryl S
> >
> >
> > "Patrick Hatton" wrote:
> >
> > > Daryl,
> > >
> > > My apologies, I am using the term code too loosely. There is code that runs
> > > a report. The report is made up of 8 sub-reports. The sub-report control
> > > source is a query that uses another query that tries to pull dates from an
> > > open form. When the query is run during the report process it will prompt
> > > for data vs running the query manually.
> > >
> > > "Daryl S" wrote:
> > >
> > > > Patrick -
> > > >
> > > > Can you post the statement for running the query?
> > > >
> > > > --
> > > > Daryl S
> > > >
> > > >
> > > > "Patrick Hatton" wrote:
> > > >
> > > > > Yes, the form is open when running.
> > > > >
> > > > > The query is using: Between [forms]![frmreportform]![startmonth] And
> > > > > [forms]![frmreportform]![endmonth]
> > > > >
> > > > > This is the strange part. I can run the query with the form closed, no data.
> > > > > Open the form, run the query, works fine. When this same thing is done from
> > > > > the code, it doesn't work.
> > > > >
> > > > > "Daryl S" wrote:
> > > > >
> > > > > > Patrick -
> > > > > >
> > > > > > Is the form open when you are trying to just run it through the code? If
> > > > > > not, then open the form first. You can make the form inivisible if you don't
> > > > > > want the users to see it.
> > > > > >
> > > > > > --
> > > > > > Daryl S
> > > > > >
> > > > > >
> > > > > > "Patrick Hatton" wrote:
> > > > > >
> > > > > > > I have a routine that uses data from a form. Each time I run it, it asks for
> > > > > > > input on any field contained in the form.
> > > > > > >
> > > > > > > If I open the form then manually run the query it works fine. When I let it
> > > > > > > run throught the code, it cannot see the fields.
> > > > > > >
> > > > > > > Your suggestions are greatly appreciated.
> > > > > > >
> > > > > > > Patrick
First  |  Prev  | 
Pages: 1 2 3
Prev: Hyperlinks in command button
Next: Access 2010 Forms - Converting 2007 Client into 2010 Web