Anybody out there?

J

Joanne

I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
 
D

Duane Hookom

Do you expect us to know anything about the report? What do you expect to
happen with "strSQL"?

What are you attempting to do? Can't you base your report on
tblPatentCaseInformation and use code like:
Dim strWhere as String
strWhere = "[Parties-Appellant]='test'"
DoCmd.OpenReport "CaseInformation", acViewPreview, , strWhere

Please don't assume we know your situation.
 
K

Ken Snell \(MVP\)

Does the report have a RecordSource already? If yes, can you just filter
that query on the [Parties-Appellant] field? If yes, the only code you need
is this:

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "[Parties-Appellant]='test'"
DoCmd.OpenReport "CaseInformation", acViewPreview, , strSQL
End Sub


If you truly want to give the report a new RecordSource, then you'll need
two different program groups. The first will be to open the report and
provide the desired SQL statement to the report in the OpenArgs argument
(assumes you're using ACCESS 2002 or 2003):


Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
End Sub

Then you'll need this code in the report's Open event procedure to assign
the SQL statement to the report's RecordSource:

Private Sub Report_Open(Cancel As Integer)
If Len(Me.OpenArgs & "") > 0 Then
Me.RecordSource = Me.OpenArgs
End If
End Sub


The reason your code didn't work is because DoCmd.Close closes the active
object when you don't specify the object's name that is to be closed, and
that may or may not have been your report at that time. Also, it's not
normal programming practice to open your report in design view and make
changes to it this way; better to do what is outlined above in order to
avoid any inadvertent changes to your report that you didn't want to have
happen.
 
F

fredg

I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
Click to expand...

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
 
J

Joanne

Thank you fredg and Ken, for trying to answer my questions civilly. Whoever
Duane is, I don't need you chastising me for not posting the way you want me
to and I certainly don't need to be belittled in a public forum. If that's
all you want to do, just don't bother answering me. I really needed help
yesterday. I'm a newbie to this and was in a bit over my head. I didn't
need your "help"

fredg said:
I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
Click to expand...

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
 
J

Joanne

This may just be hopeless. I tried both suggestions from Fred and Ken, and
nothing is working. The report does open but it is completely blank. If I
run the Select statement separately, i.e. from a query, it works just fine.
THere is definitely a "test" entry in parties-appellant. It seems as if
somehow the SQL script is not getting to the report. I so appreciate your
help and I'm sorry if I seem impatient. Can you suggest any other
possibilities?

Joanne said:
Thank you fredg and Ken, for trying to answer my questions civilly. Whoever
Duane is, I don't need you chastising me for not posting the way you want me
to and I certainly don't need to be belittled in a public forum. If that's
all you want to do, just don't bother answering me. I really needed help
yesterday. I'm a newbie to this and was in a bit over my head. I didn't
need your "help"

fredg said:
I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
Click to expand...

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
Click to expand...
 
L

lewie

Joanne said:
This may just be hopeless. I tried both suggestions from Fred and Ken, and
nothing is working. The report does open but it is completely blank. If I
run the Select statement separately, i.e. from a query, it works just fine.
THere is definitely a "test" entry in parties-appellant. It seems as if
somehow the SQL script is not getting to the report. I so appreciate your
help and I'm sorry if I seem impatient. Can you suggest any other
possibilities?

Joanne said:
Thank you fredg and Ken, for trying to answer my questions civilly. Whoever
Duane is, I don't need you chastising me for not posting the way you want me
to and I certainly don't need to be belittled in a public forum. If that's
all you want to do, just don't bother answering me. I really needed help
yesterday. I'm a newbie to this and was in a bit over my head. I didn't
need your "help"

fredg said:
On Mon, 31 Jul 2006 20:34:01 -0700, Joanne wrote:

I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
Click to expand...
Click to expand...
 
L

lewie

seems the simpliest would be
1. well if it runs in a query run it there
2. have the query be the recordsource for the report
3. have the button open the report and if the query is the recordsource
it will run automatically.
or you could have the btn run a macro which does everything 1 line at a
time.
Joanne said:
This may just be hopeless. I tried both suggestions from Fred and Ken, and
nothing is working. The report does open but it is completely blank. If I
run the Select statement separately, i.e. from a query, it works just fine.
THere is definitely a "test" entry in parties-appellant. It seems as if
somehow the SQL script is not getting to the report. I so appreciate your
help and I'm sorry if I seem impatient. Can you suggest any other
possibilities?

Joanne said:
Thank you fredg and Ken, for trying to answer my questions civilly. Whoever
Duane is, I don't need you chastising me for not posting the way you want me
to and I certainly don't need to be belittled in a public forum. If that's
all you want to do, just don't bother answering me. I really needed help
yesterday. I'm a newbie to this and was in a bit over my head. I didn't
need your "help"

fredg said:
On Mon, 31 Jul 2006 20:34:01 -0700, Joanne wrote:

I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
Click to expand...
Click to expand...
 
J

Joanne

Hello again,
I put a break point where the report is supposed to open and if I hover over
that line it says that the name of the report is wrong. The report name is
CaseInformation. Is there a limit as to how many characters I can use?
Again, thank you for your help. It is so appreciated.

fredg said:
I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
Click to expand...

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
 
F

fredg

Hello again,
I put a break point where the report is supposed to open and if I hover over
that line it says that the name of the report is wrong. The report name is
CaseInformation. Is there a limit as to how many characters I can use?
Again, thank you for your help. It is so appreciated.

fredg said:
I have the following which runs on the click event of a command button in a
form. The SQL part of it doesn't run at all. If I place this code into a
query, it runs fine and i get the results I want. What is wrong? It doesn't
error out, the report is just blank!

Public Sub btnSearch_Click()
Dim strSQL As String
strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& "FROM tblPatentCaseInformation" _
& "WHERE [Parties-Appellant]='test'" _
& "ORDER BY CaseName"
DoCmd.Echo True
DoCmd.OpenReport "CaseInformation", acViewDesign, , , , strSQL
DoCmd.Close , , acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview, , , , strSQL
DoCmd.Echo True
Click to expand...

You're passing strSQL to the report's OpenArgs property in Design
View, then again in Preview. Why?

Are you trying to set the report's Recordsource to the strSQL?

DoCmd.OpenReport "CaseInformation", acViewDesign, , , acHidden
Reports!CaseInformation.RecordSource = strSQL
DoCmd.Close acReport, "CaseInformation", acSaveYes
DoCmd.OpenReport "CaseInformation", acViewPreview

Also, you haven't included a space between the various concatenated
parts of the string, therefore it will all run together and fail, i.e.
"SELECT Citation, CaseName,[Parties-Appellant]FROM
tblPatentCaseInformationWHERE [Parties-Appellant]='test'"ORDER BY
CaseName"

It should be:

strSQL = "SELECT Citation, CaseName,[Parties-Appellant]" _
& " FROM tblPatentCaseInformation" _
& " WHERE [Parties-Appellant]='test'" _
& " ORDER BY CaseName"
Click to expand...
Click to expand...

Are you absolutely sure that you have the name of the report spelled
correctly? CaseInformation is not the same as Case Information.

If you just write:
DoCmd.OpenReport "CaseInformation", acViewPreview
does the report open (even without data)?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

The record source does not exist 1
Incorrect SQL statement for a report 3
Value of a variable 0
Using a SQL statement as a record source 9
Application defined or object defined error on report 8
Variables in report header 2
Running Report via VBA 5
Print and Collate 2 seperate reports 0

Top