filter report based on list box selection

M

M

I have copied some code from the Northwind sample database and amended it to
suit, but the code won't work for Case 3. Can anyone help?

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the SelectReports form.

Dim strWhereCategory As String

strWhereCategory = "Original Site Name =
Forms![SelectReport]!SelectSiteName"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rptSite Details", PrintMode
Case 2
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Case 3
If IsNull(Forms![SelectReport]!SelectSiteName) Then
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Else
DoCmd.OpenReport "rptAll County Tasks", PrintMode, ,
strWhereCategory
End If
End Select
DoCmd.Close acForm, "frmSelectReport"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

The field "Original Site Name" and the source of SelectSiteName list box are
both text. The list box is unbound and the report is based on a query
grouped by Original Site Name.

Case 1 and 2 are OK (simple report) but this Case 3 using the WhereCategory
doesn't do anything. Any help appreciated.
 
D

Douglas J Steele

Because you have blanks in your field names, you must enclose the name of
the field in square brackets:

strWhereCategory = "[Original Site Name] =
Forms![SelectReport]!SelectSiteName"
 
M

M

Doug

I have added the square brackets but still nothing happens.

Douglas J Steele said:
Because you have blanks in your field names, you must enclose the name of
the field in square brackets:

strWhereCategory = "[Original Site Name] =
Forms![SelectReport]!SelectSiteName"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


M said:
I have copied some code from the Northwind sample database and amended it to
suit, but the code won't work for Case 3. Can anyone help?

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the SelectReports form.

Dim strWhereCategory As String

strWhereCategory = "Original Site Name =
Forms![SelectReport]!SelectSiteName"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rptSite Details", PrintMode
Case 2
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Case 3
If IsNull(Forms![SelectReport]!SelectSiteName) Then
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Else
DoCmd.OpenReport "rptAll County Tasks", PrintMode, ,
strWhereCategory
End If
End Select
DoCmd.Close acForm, "frmSelectReport"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

The field "Original Site Name" and the source of SelectSiteName list box are
both text. The list box is unbound and the report is based on a query
grouped by Original Site Name.

Case 1 and 2 are OK (simple report) but this Case 3 using the WhereCategory
doesn't do anything. Any help appreciated.
Click to expand...
Click to expand...
 
D

Douglas J Steele

Okay, so what happens? ("won't work" doesn't give much to go on...)

Given the name of the field, I'm assuming it's text. You could try:

strWhereCategory = "[Original Site Name] = " & _
Chr$(34) & _
Forms![SelectReport]!SelectSiteName & _
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


M said:
Doug

I have added the square brackets but still nothing happens.

Douglas J Steele said:
Because you have blanks in your field names, you must enclose the name of
the field in square brackets:

strWhereCategory = "[Original Site Name] =
Forms![SelectReport]!SelectSiteName"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


M said:
I have copied some code from the Northwind sample database and amended
Click to expand...
it
to
suit, but the code won't work for Case 3. Can anyone help?

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the SelectReports form.

Dim strWhereCategory As String

strWhereCategory = "Original Site Name =
Forms![SelectReport]!SelectSiteName"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rptSite Details", PrintMode
Case 2
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Case 3
If IsNull(Forms![SelectReport]!SelectSiteName) Then
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Else
DoCmd.OpenReport "rptAll County Tasks", PrintMode, ,
strWhereCategory
End If
End Select
DoCmd.Close acForm, "frmSelectReport"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

The field "Original Site Name" and the source of SelectSiteName list
Click to expand...
box
are
both text. The list box is unbound and the report is based on a query
grouped by Original Site Name.

Case 1 and 2 are OK (simple report) but this Case 3 using the WhereCategory
doesn't do anything. Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
M

M

As I know next to nothing about VBA, and only a little about Access, I
suspect I am missing something obvious/basic.

I have a list box [SelectSiteName] based on a table [tblAll County Tasks].
The field selected from this table is called [Original Site Name]. This is a
text field. I would like the Where statement to compare the original site
name selected from this list box, to the site name in the report so that only
this site name is displayed in the report. The report is based on a query
[qryReport for Operations Tasks Schedule] which is based on the same table as
the list box i.e [tblAllCounty Tasks]. Without the selection of a site name
the report displays all records grouped by site for all sites (20000 records).

The form from which I select the report, is called frmSelectReport. Case 1
and 2 work OK. I select the report from an Option Group (called
ReportToPrint) press the command button for either preview or print and they
appear. Case 3, nothing happens. No error message, nothing. I have tried
chaging the source record for the list box and adding criteria in the report
but no luck.

Douglas J Steele said:
Okay, so what happens? ("won't work" doesn't give much to go on...)

Given the name of the field, I'm assuming it's text. You could try:

strWhereCategory = "[Original Site Name] = " & _
Chr$(34) & _
Forms![SelectReport]!SelectSiteName & _
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


M said:
Doug

I have added the square brackets but still nothing happens.

Douglas J Steele said:
Because you have blanks in your field names, you must enclose the name of
the field in square brackets:

strWhereCategory = "[Original Site Name] =
Forms![SelectReport]!SelectSiteName"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have copied some code from the Northwind sample database and amended it
to
suit, but the code won't work for Case 3. Can anyone help?

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the SelectReports form.

Dim strWhereCategory As String

strWhereCategory = "Original Site Name =
Forms![SelectReport]!SelectSiteName"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rptSite Details", PrintMode
Case 2
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Case 3
If IsNull(Forms![SelectReport]!SelectSiteName) Then
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Else
DoCmd.OpenReport "rptAll County Tasks", PrintMode, ,
strWhereCategory
End If
End Select
DoCmd.Close acForm, "frmSelectReport"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

The field "Original Site Name" and the source of SelectSiteName list box
are
both text. The list box is unbound and the report is based on a query
grouped by Original Site Name.

Case 1 and 2 are OK (simple report) but this Case 3 using the
WhereCategory
doesn't do anything. Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Try putting a break-point in the routine, and step through it line by line
to see where execution is actually going.
--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


M said:
As I know next to nothing about VBA, and only a little about Access, I
suspect I am missing something obvious/basic.

I have a list box [SelectSiteName] based on a table [tblAll County Tasks].
The field selected from this table is called [Original Site Name]. This
is a
text field. I would like the Where statement to compare the original site
name selected from this list box, to the site name in the report so that
only
this site name is displayed in the report. The report is based on a query
[qryReport for Operations Tasks Schedule] which is based on the same table
as
the list box i.e [tblAllCounty Tasks]. Without the selection of a site
name
the report displays all records grouped by site for all sites (20000
records).

The form from which I select the report, is called frmSelectReport. Case
1
and 2 work OK. I select the report from an Option Group (called
ReportToPrint) press the command button for either preview or print and
they
appear. Case 3, nothing happens. No error message, nothing. I have
tried
chaging the source record for the list box and adding criteria in the
report
but no luck.

Douglas J Steele said:
Okay, so what happens? ("won't work" doesn't give much to go on...)

Given the name of the field, I'm assuming it's text. You could try:

strWhereCategory = "[Original Site Name] = " & _
Chr$(34) & _
Forms![SelectReport]!SelectSiteName & _
Chr$(34)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


M said:
Doug

I have added the square brackets but still nothing happens.

:

Because you have blanks in your field names, you must enclose the
name of
the field in square brackets:

strWhereCategory = "[Original Site Name] =
Forms![SelectReport]!SelectSiteName"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have copied some code from the Northwind sample database and
amended it
to
suit, but the code won't work for Case 3. Can anyone help?

Sub PrintReports(PrintMode As Integer)
On Error GoTo Err_Preview_Click
' This procedure used in Preview_Click and Print_Click Sub procedures.
' Preview or print report selected in the ReportToPrint option group.
' Then close the SelectReports form.

Dim strWhereCategory As String

strWhereCategory = "Original Site Name =
Forms![SelectReport]!SelectSiteName"

Select Case Me!ReportToPrint
Case 1
DoCmd.OpenReport "rptSite Details", PrintMode
Case 2
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Case 3
If IsNull(Forms![SelectReport]!SelectSiteName) Then
DoCmd.OpenReport "rptAll County Tasks", PrintMode
Else
DoCmd.OpenReport "rptAll County Tasks", PrintMode,
,
strWhereCategory
End If
End Select
DoCmd.Close acForm, "frmSelectReport"

Exit_Preview_Click:
Exit Sub

Err_Preview_Click:
Resume Exit_Preview_Click

End Sub

The field "Original Site Name" and the source of SelectSiteName
list box
are
both text. The list box is unbound and the report is based on a
query
grouped by Original Site Name.

Case 1 and 2 are OK (simple report) but this Case 3 using the
WhereCategory
doesn't do anything. Any help appreciated.
Click to expand...
Click to expand...
Click to expand...
 

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

Option Button 4
Using Parameters in a Form 2
need help with multi-selct / option group 4
Preview and print report problems 1
User entering search criteria 1
Problems with "All" option in combo box 4
What am I doing Wrong? 1
Can anyone tell me what is wrong here? 2

Top