Using a SQL statement as a record source

[Guest]

Hello, I am trying to have a report run from a form. The user enters
information into the form and then, based on that information a report will
run when the user clicks a command button. I am testing with some hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing, and also
it is treating it as a parameter query and asking me for the parameter for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Jeff L]

If you are looking for records where Parties-Appellant has the value
"Philip Roth" then you need to put single quotes around Philip Roth in
your query and not the [].

Hope that helps!

 

[Jeff Boyce]

Do you have a field named [Philip Roth], or is the value in the field =
"Philip Roth". Your SQL statement claims the value of [Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you so much for your help. I fixed the SQL statement but I still don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property sheet or
macro is misspelled or refers to a report that doesn't exist. I checked
everywhere. I even deleted the report and started from scratch. Every time
I create a report I get this error. It's driving me nuts. All I want to do
is have a search form come up (it does), and based on information that the
user puts in a text box, a report should generate from that information. I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should I be
creating the report on the fly? I thought using a generic, empty report
would be a good idea. And then it keeps saying it's printing. I am so, so
frustrated. Please,c an you help?

Do you have a field named [Philip Roth], or is the value in the field =
"Philip Roth". Your SQL statement claims the value of [Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user enters
information into the form and then, based on that information a report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing, and also
it is treating it as a parameter query and asking me for the parameter for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Jeff Boyce]

Access is telling you it cannot find a report with that exact spelling for a
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you so much for your help. I fixed the SQL statement but I still
don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property sheet
or
macro is misspelled or refers to a report that doesn't exist. I checked
everywhere. I even deleted the report and started from scratch. Every
time
I create a report I get this error. It's driving me nuts. All I want to
do
is have a search form come up (it does), and based on information that the
user puts in a text box, a report should generate from that information.
I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should I
be
creating the report on the fly? I thought using a generic, empty report
would be a good idea. And then it keeps saying it's printing. I am so,
so
frustrated. Please,c an you help?

Do you have a field named [Philip Roth], or is the value in the field =
"Philip Roth". Your SQL statement claims the value of
[Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user enters
information into the form and then, based on that information a report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing, and
also
it is treating it as a parameter query and asking me for the parameter
for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Guest]

That's what's so frustrating. That is the report name, it's not misspelled.
When I do an "OpenReport" and use that name, the code works fine.

Access is telling you it cannot find a report with that exact spelling for a
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you so much for your help. I fixed the SQL statement but I still
don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property sheet
or
macro is misspelled or refers to a report that doesn't exist. I checked
everywhere. I even deleted the report and started from scratch. Every
time
I create a report I get this error. It's driving me nuts. All I want to
do
is have a search form come up (it does), and based on information that the
user puts in a text box, a report should generate from that information.
I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should I
be
creating the report on the fly? I thought using a generic, empty report
would be a good idea. And then it keeps saying it's printing. I am so,
so
frustrated. Please,c an you help?

Do you have a field named [Philip Roth], or is the value in the field =
"Philip Roth". Your SQL statement claims the value of
[Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user enters
information into the form and then, based on that information a report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing, and
also
it is treating it as a parameter query and asking me for the parameter
for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Guest]

Hello Jeff,
I'm sorry to keep writing to you, but I ran the following:

Dim strReportName As String

strReportName = Forms(0).Name
MsgBox "The report name is " & strReportName

and the message I get is that Report(0) is the name of the form that I'm
using to create the report. That's why I've been getting the error. Even
though I have the form open, do I have to give it focus or in some way tell
access that I want to run my SQL against the report and not against the form?
Thank you very much.

Access is telling you it cannot find a report with that exact spelling for a
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you so much for your help. I fixed the SQL statement but I still
don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property sheet
or
macro is misspelled or refers to a report that doesn't exist. I checked
everywhere. I even deleted the report and started from scratch. Every
time
I create a report I get this error. It's driving me nuts. All I want to
do
is have a search form come up (it does), and based on information that the
user puts in a text box, a report should generate from that information.
I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should I
be
creating the report on the fly? I thought using a generic, empty report
would be a good idea. And then it keeps saying it's printing. I am so,
so
frustrated. Please,c an you help?

Do you have a field named [Philip Roth], or is the value in the field =
"Philip Roth". Your SQL statement claims the value of
[Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user enters
information into the form and then, based on that information a report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing, and
also
it is treating it as a parameter query and asking me for the parameter
for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Guest]

Hello,
I realized that I mistyped the code. But when I tried to get the name of
the report by using its index number and then having the name of the report
display in a message box, I was told that the report didn't exist. It does!
I've edited it, but it does not show up anyware in my project. Should I have
VBA create the report on the fly?

 

[Jeff Boyce]

Joanne

There may be some confusion here (maybe mine!) about reports and forms.
Reports are not based on forms, so

strReportName = Forms.Name

doesn't make much sense to me.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello Jeff,
I'm sorry to keep writing to you, but I ran the following:

Dim strReportName As String

strReportName = Forms(0).Name
MsgBox "The report name is " & strReportName

and the message I get is that Report(0) is the name of the form that I'm
using to create the report. That's why I've been getting the error. Even
though I have the form open, do I have to give it focus or in some way
tell
access that I want to run my SQL against the report and not against the
form?
Thank you very much.

Access is telling you it cannot find a report with that exact spelling
for a
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you so much for your help. I fixed the SQL statement but I still
don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property
sheet
or
macro is misspelled or refers to a report that doesn't exist. I
checked
everywhere. I even deleted the report and started from scratch. Every
time
I create a report I get this error. It's driving me nuts. All I want
to
do
is have a search form come up (it does), and based on information that
the
user puts in a text box, a report should generate from that
information.
I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should
I
be
creating the report on the fly? I thought using a generic, empty
report
would be a good idea. And then it keeps saying it's printing. I am
so,
so
frustrated. Please,c an you help?

:

Do you have a field named [Philip Roth], or is the value in the field
=
"Philip Roth". Your SQL statement claims the value of
[Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user
enters
information into the form and then, based on that information a
report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing,
and
also
it is treating it as a parameter query and asking me for the
parameter
for
"philip roth". Please help. I just don't see what I'm doing wrong.

 

[Larry Linson]

Do you actually have spaces in the Report name? If so, then you'll have to
enclose it in square brackets, or in quotes if the context requires text. My
suggestion is don't ever use spaces in the name of an Access object, Field,
or Control because sooner or later, it'll come back to haunt you.

Do you think you could back up, and describe in modest detail what you have
in your database and what you are trying to accomplish? It appeared to me
that you are simply wanting to display/print a report for one Record in a
Table or Query and that you are having some difficulties at the most
detailed level of coding.

I know it must be frustrating to you to not get a workable answer, but
believe me, it is frustrating to us to not quite know what question to ask
you about the details to be able to assist you, too.

Larry Linson
Microsoft Access MVP

That's what's so frustrating. That is the report name, it's not
misspelled.
When I do an "OpenReport" and use that name, the code works fine.

Access is telling you it cannot find a report with that exact spelling
for a
name.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you so much for your help. I fixed the SQL statement but I still
don't
know if my code is working because now I get the error:

the report name Case Information you entered in either the property
sheet
or
macro is misspelled or refers to a report that doesn't exist. I
checked
everywhere. I even deleted the report and started from scratch. Every
time
I create a report I get this error. It's driving me nuts. All I want
to
do
is have a search form come up (it does), and based on information that
the
user puts in a text box, a report should generate from that
information.
I
can't even get to capturing the variable information from the text box
because I keep getting this error that my report doesn't exist. Should
I
be
creating the report on the fly? I thought using a generic, empty
report
would be a good idea. And then it keeps saying it's printing. I am
so,
so
frustrated. Please,c an you help?

:

Do you have a field named [Philip Roth], or is the value in the field
=
"Philip Roth". Your SQL statement claims the value of
[Parties-Appellant]
are to be found in a field named [Philip Roth].

How are you launching the report (i.e., the code)?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello, I am trying to have a report run from a form. The user
enters
information into the form and then, based on that information a
report
will
run when the user clicks a command button. I am testing with some
hard-coded
parameters and have the following:
SQL = "SELECT tblPatentCaseInformation.Citation,
tblPatentCaseInformation.CaseName,tblPatentCaseInformation.[Parties-Appellant]
from tblPatentCaseInformation WHERE
(((tblPatentCaseInformation.[Parties-Appellant])=[Philip Roth]))"

The report seems to run, but it keeps saying that it is printing,
and
also
it is treating it as a parameter query and asking me for the
parameter
for
"philip roth". Please help. I just don't see what I'm doing wrong.