OpenReport from form Problem

  • Thread starter Thread starter Ken Ivins
  • Start date Start date
K

Ken Ivins

I have run into this before and thought it was time to learn the right way.
I have one report and many queries. The queries all have the same fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to the new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query and the
other for the other. In both cases I got a report with the data for the
query in the recordsource field. If I leave the recordsource field on the
report blank I get no data.

Do I need to change my code or something in the report? Access help makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
 
Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings in
the report itself. If the only difference between all your reports is the
sort order (the queries otherwise return exactly the same records and same
fields), then changing the record source is not the solution that you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not going to
work either. The "Filter" argument allows you to designate a query that will
filter the report's query's results. Quite honestly, none of us ACCESS MVPs,
when we last surveyed ourselves, use this argument for filtering a report --
instead, we use the fourth argument, WhereCondition, to filter a report's
recordsource. If you wanted to pass the recordsource string to the report,
you could use the OpenArgs argument [in ACCESS 2002 and 2003 only] to pass
it to the report, and then run code in the report's Open event to read that
value and set the report's RecordSource to that string.)

Post back with more information about exactly what differences you have for
all the varied reports, and let's see if we can assist you to a solution.
 
Dear Ken,

Thanks for your response. I need to look over you thoughts closer. Hopefully
tomorrow. I'll then lay out what I am trying to do and how I thought I could
select the query. Actually filtering with code would actually be better. I
tried that and ran into other problems. I'll outline my errors as well.
Please do not give up on me but check after the weekend for more details.

Thanks,
Ken



Ken Snell said:
Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings in
the report itself. If the only difference between all your reports is the
sort order (the queries otherwise return exactly the same records and same
fields), then changing the record source is not the solution that you
need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not going
to work either. The "Filter" argument allows you to designate a query that
will filter the report's query's results. Quite honestly, none of us
ACCESS MVPs, when we last surveyed ourselves, use this argument for
filtering a report -- instead, we use the fourth argument,
WhereCondition, to filter a report's recordsource. If you wanted to pass
the recordsource string to the report, you could use the OpenArgs argument
[in ACCESS 2002 and 2003 only] to pass it to the report, and then run code
in the report's Open event to read that value and set the report's
RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


Ken Ivins said:
I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to the
new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query and
the
other for the other. In both cases I got a report with the data for the
query in the recordsource field. If I leave the recordsource field on the
report blank I get no data.

Do I need to change my code or something in the report? Access help makes
it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
 
Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to Enter
the Paramater Value which is listed just above it and the report runs as
expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Ken Snell said:
Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings in
the report itself. If the only difference between all your reports is the
sort order (the queries otherwise return exactly the same records and same
fields), then changing the record source is not the solution that you
need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not going
to work either. The "Filter" argument allows you to designate a query that
will filter the report's query's results. Quite honestly, none of us
ACCESS MVPs, when we last surveyed ourselves, use this argument for
filtering a report -- instead, we use the fourth argument,
WhereCondition, to filter a report's recordsource. If you wanted to pass
the recordsource string to the report, you could use the OpenArgs argument
[in ACCESS 2002 and 2003 only] to pass it to the report, and then run code
in the report's Open event to read that value and set the report's
RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


Ken Ivins said:
I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to the
new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query and
the
other for the other. In both cases I got a report with the data for the
query in the recordsource field. If I leave the recordsource field on the
report blank I get no data.

Do I need to change my code or something in the report? Access help makes
it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
 
Syntax problem (note: the code is actually all one line, but it will wrap in
the newsreader):

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1 &
" Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3

You must build a "WHERE clause" string (just without the WHERE), so you need
to "repeat" the ilIssue = stuff for each value.
--

Ken Snell
<MS ACCESS MVP>



Ken Ivins said:
Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to Enter
the Paramater Value which is listed just above it and the report runs as
expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Ken Snell said:
Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings in
the report itself. If the only difference between all your reports is the
sort order (the queries otherwise return exactly the same records and
same fields), then changing the record source is not the solution that
you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not going
to work either. The "Filter" argument allows you to designate a query
that will filter the report's query's results. Quite honestly, none of us
ACCESS MVPs, when we last surveyed ourselves, use this argument for
filtering a report -- instead, we use the fourth argument,
WhereCondition, to filter a report's recordsource. If you wanted to pass
the recordsource string to the report, you could use the OpenArgs
argument [in ACCESS 2002 and 2003 only] to pass it to the report, and
then run code in the report's Open event to read that value and set the
report's RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


Ken Ivins said:
I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same
fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to the
new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query and
the
other for the other. In both cases I got a report with the data for the
query in the recordsource field. If I leave the recordsource field on
the
report blank I get no data.

Do I need to change my code or something in the report? Access help
makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Ken,

Thanks I'm still learning the proper syntax.

I did get another error though. "Syntax Error (missing operator) in query
expression '(ilIssue = Barn or ilIssue = Building Codes or ilIuuse =
Cemetery)'.

It looks like I am close but am missing something. Below is what I have
now:

I really appreciate your help on this. I will use this a lot on this project
and many future prijects as well.

Ken Ivins


Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1 &
" Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub


Ken Snell said:
Syntax problem (note: the code is actually all one line, but it will wrap
in the newsreader):

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3

You must build a "WHERE clause" string (just without the WHERE), so you
need to "repeat" the ilIssue = stuff for each value.
--

Ken Snell
<MS ACCESS MVP>



Ken Ivins said:
Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to
Enter the Paramater Value which is listed just above it and the report
runs as expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Ken Snell said:
Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings
in the report itself. If the only difference between all your reports is
the sort order (the queries otherwise return exactly the same records
and same fields), then changing the record source is not the solution
that you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not going
to work either. The "Filter" argument allows you to designate a query
that will filter the report's query's results. Quite honestly, none of
us ACCESS MVPs, when we last surveyed ourselves, use this argument for
filtering a report -- instead, we use the fourth argument,
WhereCondition, to filter a report's recordsource. If you wanted to pass
the recordsource string to the report, you could use the OpenArgs
argument [in ACCESS 2002 and 2003 only] to pass it to the report, and
then run code in the report's Open event to read that value and set the
report's RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same
fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to
the new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query and
the
other for the other. In both cases I got a report with the data for the
query in the recordsource field. If I leave the recordsource field on
the
report blank I get no data.

Do I need to change my code or something in the report? Access help
makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
ilIssue is a text field, so you must delimit the string values with '
characters:

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = '" & Me.cmbIssues1 &
"' Or ilIssue = '" & Me.cmbIssues2 & "' Or ilIssue = '" & Me.cmbIssues3 &
"'"

--

Ken Snell
<MS ACCESS MVP>


Ken Ivins said:
Ken,

Thanks I'm still learning the proper syntax.

I did get another error though. "Syntax Error (missing operator) in query
expression '(ilIssue = Barn or ilIssue = Building Codes or ilIuuse =
Cemetery)'.

It looks like I am close but am missing something. Below is what I have
now:

I really appreciate your help on this. I will use this a lot on this
project and many future prijects as well.

Ken Ivins


Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub


Ken Snell said:
Syntax problem (note: the code is actually all one line, but it will wrap
in the newsreader):

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3

You must build a "WHERE clause" string (just without the WHERE), so you
need to "repeat" the ilIssue = stuff for each value.
--

Ken Snell
<MS ACCESS MVP>



Ken Ivins said:
Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to
Enter the Paramater Value which is listed just above it and the report
runs as expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " &
Me.cmbIssues1 Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings
in the report itself. If the only difference between all your reports
is the sort order (the queries otherwise return exactly the same
records and same fields), then changing the record source is not the
solution that you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not
going to work either. The "Filter" argument allows you to designate a
query that will filter the report's query's results. Quite honestly,
none of us ACCESS MVPs, when we last surveyed ourselves, use this
argument for filtering a report -- instead, we use the fourth
argument, WhereCondition, to filter a report's recordsource. If you
wanted to pass the recordsource string to the report, you could use the
OpenArgs argument [in ACCESS 2002 and 2003 only] to pass it to the
report, and then run code in the report's Open event to read that value
and set the report's RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same
fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to
the new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query
and the
other for the other. In both cases I got a report with the data for
the
query in the recordsource field. If I leave the recordsource field on
the
report blank I get no data.

Do I need to change my code or something in the report? Access help
makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Since ilIssue is a text field, you need quotes around the strings:

(ilIssue = ''Barn" or ilIssue = "Building Codes" or ilIuuse = "Cemetery")

In terms of what Ken gave you, try:

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = '" & Me.cmbIssues1 &
"' Or ilIssue = '" & Me.cmbIssues2 & "' Or ilIssue = '" & Me.cmbIssues3 &
"'"

Exagerating for clarity, that's

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = ' " & Me.cmbIssues1 &
" ' Or ilIssue = ' " & Me.cmbIssues2 & " ' Or ilIssue = ' " & Me.cmbIssues3
& " ' "


If there's a chance that any of the values might have apostrophes in them,
try

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Chr$(34) &
Me.cmbIssues1 & Chr(34) &
" Or ilIssue = " & Chr$(34) & Me.cmbIssues2 & Chr$(34) & " Or ilIssue = " &
Chr$(34) & Me.cmbIssues3 & Chr$(34)

Another alternative would be:

DoCmd.OpenReport stDocName, acPreview, , "ilIssue In ('" & Me.cmbIssues1 &
"', '" & Me.cmbIssues2 & "', '" & Me.cmbIssues3 & "')"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Ken Ivins said:
Ken,

Thanks I'm still learning the proper syntax.

I did get another error though. "Syntax Error (missing operator) in query
expression '(ilIssue = Barn or ilIssue = Building Codes or ilIuuse =
Cemetery)'.

It looks like I am close but am missing something. Below is what I have
now:

I really appreciate your help on this. I will use this a lot on this
project and many future prijects as well.

Ken Ivins


Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub


Ken Snell said:
Syntax problem (note: the code is actually all one line, but it will wrap
in the newsreader):

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3

You must build a "WHERE clause" string (just without the WHERE), so you
need to "repeat" the ilIssue = stuff for each value.
--

Ken Snell
<MS ACCESS MVP>



Ken Ivins said:
Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to
Enter the Paramater Value which is listed just above it and the report
runs as expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " &
Me.cmbIssues1 Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings
in the report itself. If the only difference between all your reports
is the sort order (the queries otherwise return exactly the same
records and same fields), then changing the record source is not the
solution that you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not
going to work either. The "Filter" argument allows you to designate a
query that will filter the report's query's results. Quite honestly,
none of us ACCESS MVPs, when we last surveyed ourselves, use this
argument for filtering a report -- instead, we use the fourth
argument, WhereCondition, to filter a report's recordsource. If you
wanted to pass the recordsource string to the report, you could use the
OpenArgs argument [in ACCESS 2002 and 2003 only] to pass it to the
report, and then run code in the report's Open event to read that value
and set the report's RecordSource to that string.)

Post back with more information about exactly what differences you have
for all the varied reports, and let's see if we can assist you to a
solution.
--

Ken Snell
<MS ACCESS MVP>


I have run into this before and thought it was time to learn the right
way.
I have one report and many queries. The queries all have the same
fields,
just different sorting based on the input from the user. In the past I
would just copy and paste the report and change its record source to
the new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query
and the
other for the other. In both cases I got a report with the data for
the
query in the recordsource field. If I leave the recordsource field on
the
report blank I get no data.

Do I need to change my code or something in the report? Access help
makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
That did it. A big thanks to both of you.

Ken Ivins



Ken Snell said:
ilIssue is a text field, so you must delimit the string values with '
characters:

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = '" & Me.cmbIssues1
&
"' Or ilIssue = '" & Me.cmbIssues2 & "' Or ilIssue = '" & Me.cmbIssues3 &
"'"

--

Ken Snell
<MS ACCESS MVP>


Ken Ivins said:
Ken,

Thanks I'm still learning the proper syntax.

I did get another error though. "Syntax Error (missing operator) in query
expression '(ilIssue = Barn or ilIssue = Building Codes or ilIuuse =
Cemetery)'.

It looks like I am close but am missing something. Below is what I have
now:

I really appreciate your help on this. I will use this a lot on this
project and many future prijects as well.

Ken Ivins


Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " & Me.cmbIssues1
& " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " & Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub


Ken Snell said:
Syntax problem (note: the code is actually all one line, but it will
wrap in the newsreader):

DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " &
Me.cmbIssues1 & " Or ilIssue = " & Me.cmbIssues2 & " Or ilIssue = " &
Me.cmbIssues3

You must build a "WHERE clause" string (just without the WHERE), so you
need to "repeat" the ilIssue = stuff for each value.
--

Ken Snell
<MS ACCESS MVP>



Ken,

Okay I tried to filter by openreport. See code below.

If I run it as as I get a "Type Mismatch error." If I take out the "or"
itmes and just grab the info from the single combo box I am asked to
Enter the Paramater Value which is listed just above it and the report
runs as expected.
ilIssue and cmbIssues1 are both text values.

References for DAO and Ado are both checked.

So I am stumped. Is my format not right?

Thanks for your help.

Ken Ivins



--------------------------------------------------------------------------------------------------------------
Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String

stDocName = "rptCountyIssuesAll-All"
DoCmd.OpenReport stDocName, acPreview, , "ilIssue = " &
Me.cmbIssues1 Or Me.cmbIssues2 Or Me.cmbIssues3


Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub



Reports do not use their "record source" queries as the source of the
sorting order. Instead, they rely on the Sorting and Grouping settings
in the report itself. If the only difference between all your reports
is the sort order (the queries otherwise return exactly the same
records and same fields), then changing the record source is not the
solution that you need.

(Note that your code is trying to use the "Filter" argument of the
OpenReport action to change the report's RecordSource. This is not
going to work either. The "Filter" argument allows you to designate a
query that will filter the report's query's results. Quite honestly,
none of us ACCESS MVPs, when we last surveyed ourselves, use this
argument for filtering a report -- instead, we use the fourth
argument, WhereCondition, to filter a report's recordsource. If you
wanted to pass the recordsource string to the report, you could use
the OpenArgs argument [in ACCESS 2002 and 2003 only] to pass it to the
report, and then run code in the report's Open event to read that
value and set the report's RecordSource to that string.)

Post back with more information about exactly what differences you
have for all the varied reports, and let's see if we can assist you to
a solution.
--

Ken Snell
<MS ACCESS MVP>


I have run into this before and thought it was time to learn the
right way.
I have one report and many queries. The queries all have the same
fields,
just different sorting based on the input from the user. In the past
I
would just copy and paste the report and change its record source to
the new
query but I thought I could change the record source with code.

On a form I created two buttons (see code below). One for one query
and the
other for the other. In both cases I got a report with the data for
the
query in the recordsource field. If I leave the recordsource field on
the
report blank I get no data.

Do I need to change my code or something in the report? Access help
makes it
look easy but I am missing something.

Thanks,
Ken



Private Sub cmdAllCountiesAllIssues_Click()
On Error GoTo Err_cmdAllCountiesAllIssues_Click

Dim stDocName As String
Dim stQryName As String


stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountyAllIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesAllIssues_Click:
Exit Sub

Err_cmdAllCountiesAllIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesAllIssues_Click

End Sub



Private Sub cmdAllCountiesSelectedIssues_Click()
On Error GoTo Err_cmdAllCountiesSelectedIssues_Click

Dim stDocName As String
Dim stQryName As String

stDocName = "rptCountyIssues"
stQryName = "qryCountyByIssue-AllCountySelectIssuesRpt"
DoCmd.OpenReport stDocName, acPreview, stQryName

Exit_cmdAllCountiesSelectedIssues_Click:
Exit Sub

Err_cmdAllCountiesSelectedIssues_Click:
MsgBox Err.Description
Resume Exit_cmdAllCountiesSelectedIssues_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top