subreport not recognizing query reference

[dfeigen115]

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.

Any assistance woudl be appreciated.
Dan

 

[Marshall Barton]

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.

Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[dfeigen115]

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.

Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[Marshall Barton]

If you are certain that the parameter is spelled correctly,
then either the form is being closed before the main report
is closed or there is something seriously out of whack or
corrupted.
--
Marsh
MVP [MS Access]

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.

Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[Duane Hookom]

Explain what you mean by "I keep the completed form open until after the
report has successfully displayed." How/when do you close the form? Try just
set its visible property to false rather than closing to see if the error
goes away.

--
Duane Hookom
Microsoft Access MVP

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.

Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[dfeigen115]

once the form has been filled in, the user clicks the submit button at which
time all of the data is validated and processed. The use is prompted to
correct any invalid or missing data conditions. Once the forom passes the
validation various tables are updated to reflect the information gathered.
The last sequence of events (see below) is to display a msgbox, followed by
opening and displaying the report and it's subreport. Once the report is
displayed, the orginal data entry form is closed.

MsgBox ("CACR " & Me.CACR_Number & " was successfully added")
DoCmd.OpenReport "CACR-form", acViewPreview, , strwhere
DoCmd.Close acForm, "addcacrform"

Dan

Explain what you mean by "I keep the completed form open until after the
report has successfully displayed." How/when do you close the form? Try just
set its visible property to false rather than closing to see if the error
goes away.

--
Duane Hookom
Microsoft Access MVP

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

dfeigen115 wrote:

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.


Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[Duane Hookom]

Don't close the form. As Marsh has suggested, the form is not available when
needed. Hide it.

--
Duane Hookom
Microsoft Access MVP

once the form has been filled in, the user clicks the submit button at which
time all of the data is validated and processed. The use is prompted to
correct any invalid or missing data conditions. Once the forom passes the
validation various tables are updated to reflect the information gathered.
The last sequence of events (see below) is to display a msgbox, followed by
opening and displaying the report and it's subreport. Once the report is
displayed, the orginal data entry form is closed.

MsgBox ("CACR " & Me.CACR_Number & " was successfully added")
DoCmd.OpenReport "CACR-form", acViewPreview, , strwhere
DoCmd.Close acForm, "addcacrform"

Dan

Explain what you mean by "I keep the completed form open until after the
report has successfully displayed." How/when do you close the form? Try just
set its visible property to false rather than closing to see if the error
goes away.

--
Duane Hookom
Microsoft Access MVP

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

:

dfeigen115 wrote:

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.


Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

 

[dfeigen115]

I commented out the close form statement and still get the reference error
indicating it can't find either the form or the control on the form.
Dan

If you are certain that the parameter is spelled correctly,
then either the form is being closed before the main report
is closed or there is something seriously out of whack or
corrupted.
--
Marsh
MVP [MS Access]

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

dfeigen115 wrote:

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.


Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.

.

 

[Marshall Barton]

Can you now see the form while the report is open? (You may
have to use the Window menu.)

FYI, Your VBA code continues to execute after **initiating**
the OpenReport. The report has not even processed any
records by the time the OpenReport method is done and the
code moves on.

I strongly suggest that in the future you use Copy/Paste to
post any code, queries and expressions. This way we can see
exactly what you have and be sure any typos were not caused
by retyping. At least, we won't have to guess what might be
in the real code. E.g, it might help if you posted the code
that sets the strWhere variable and the variable's value
when the report is opened.

It;s probably not the issue, but you should understand that
a report can cause a prompt if a control is bound to, or
uses an expression with a nonexistent field. Sorting and
Grouping is another place that can generate a prompt.

If the query runs by itself while the form is open but the
report does not, then check the prompt carefully to make
sure it is what you think it is.
--
Marsh
MVP [MS Access]

I commented out the close form statement and still get the reference error
indicating it can't find either the form or the control on the form.

If you are certain that the parameter is spelled correctly,
then either the form is being closed before the main report
is closed or there is something seriously out of whack or
corrupted.

Yep, I'm aware of that and have checked (and rechecked) the spelling etc. I
aslso completed one additional test, that is to have the form treat the
subreport as a seperate report, ie opening it directly using the same query.
This test worked, therefore validating the query format.

when i try to open it as a true subreport, it fails (prompts for the data)
Dan

:

dfeigen115 wrote:

I have a form that once filled in completed opens a report. the report has a
subreport. I keep the completed form open until after the report has
successfully displayed. The main report is opened using the DoCmd.OpenReport
function.

The subreport contains data from an existing table in the DB. The subreport
content is selected based on a query that contains a reference to a data
value on the still open form. The format for the reference is:
[forms]![formname]![fieldname]

When the subrport is being opened it doesn't recognize the reference and
prompts for the data value.


Whenever you get a prompt from a query, it means that Access
can not find whatever you are prompted to enter. It's
commonly just a misspelling of something.