Combo Box

[Dan @BCBS]

The code below is fine - but I have been trying to add one more parameter to
it..
On the form is a combo box called "cboReviewer" I need only records for that
reviewer in the results. Can you please help.



Private Sub cmdEMailAudit_Click()
On Error GoTo Err_cmdAudit2_Click
Dim stDocName As String
Dim X As Integer
Dim stArea As Variant

stDocName = "rptQualityAuditList"
strReport = "Audit"

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
X = 0
For Each stArea In lstArea.ItemsSelected
If X = 0 Then
stAreaList = "In('" & lstArea.ItemData(stArea) & "'"
Else
stAreaList = stAreaList & ",'" & lstArea.ItemData(stArea) &
"'"
End If
X = X + 1
Next stArea

stAreaList = stAreaList & ")"
End If

stStart = Me.txtStart
stEnd = Me.txtEnd

If stAreaList = ")" Then
MsgBox "Please select a Location."
Else
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
End If

Exit_cmdAudit2_Click:
Exit Sub
Err_cmdAudit2_Click:
MsgBox err.Number & " " & err.Description
Resume Exit_cmdAudit2_Click
End Sub

 

[strive4peace]

Before you use
SendObject
you need to SAVE the filter to the report. Here is a
general procedure you can use:

'~~~~~~~~~~~~~~~~~~~~~

Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)

' written by Crystal
' Strive4peace2007 at yahoo dot com

' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string

' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"

On Error Goto Proc_Err

'---------- declare variables
Dim rpt As Report

'---------- open design view of report
' --- and set the report object variable

DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)

'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)

'---------- save and close the changed report
DoCmd.Save acReport, pReportName
DoCmd.Close acReport, pReportName

'---------- Release object variable
Set rpt = Nothing

Proc_Exit:
Exit Sub

Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"

'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error

resume Proc_Exit:

End Sub

'~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day

 

[Dan @BCBS]

Are you answering the same question I asked>????
If so, I'm not following, why I would need to add all this code..
All I need is "If a certain person is picked from a combo box print only
those records"
I have been trying combinations like this, but I keep getting everyone's
records..


If IsNull(Me.cboReviewer) Then
MsgBox "Please pick a reviewer."
Exit Sub
Else
stPerson = stPerson = " & Me.cboReviewer & "
End If

 

[Douglas J Steele]

Yes, Crystal's attempting to answer your question. You're trying to create a
filtered report, but nowhere are you passing the criteria you've generated
to the report. If you were using DoCmd.OpenReport, you could specific a
Where clause, but you don't have that option using DoCmd.SendObject to send
a report. That means you need to open the report in design mode and save the
criteria as a filter that will be used the next time you run the report.

Or is the code you posted not the actual code you're trying to get running?


For what it's worth,

stPerson = stPerson = " & Me.cboReviewer & "

is definitely incorrect.

You need something like:

If IsNull(Me.cboReviewer) Then
MsgBox "Please pick a reviewer."
Exit Sub
Else
stCriteria = "Person = " & Chr$(34) & Me.cboReviewer & Chr$(34)
End If

This assumes Me.cboReviewer returns a name. If it returns a number, remove
the two & Chr$(34)

 

[Dan @BCBS]

My appoligies to Crystal.....
Yes, the code I posted is the code I currently run.

As you noted, it uses the DoCmd.SendObject - so the user can just email the
results..
Based on your comments I have added a DoCmd.OpenReport - so the user can do
either.

But in both cases I am struggling with the results specific to the Reviewer
(which is the combo box "cboReviewer")

I'm not sure how to plug the where "cboReviewer" into:
"DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria"

 

[Douglas J Steele]

If you were using DoCmd.OpenReport, you'd add the reviewer details to what's
already defined for stLinkCriteria. However, since you're trying to mail the
report, you can't use DoCmd.OpenReport.

What Crystal's given you is how to associated a filter with a report. Once
you know what you would be passing as stLinkCriteria, you'd pass the name of
the report and stLinkCriteria to Crystal's routine. Then, when you open the
report, it will be limited to whatever was provided in stLinkCriteria. In
other words, that's all that would be on the report when you use
DoCmd.SendObject.

I don't for the life of me understand what your existing code is supposed to
be accomplishing. You're doing all sorts of fussing, checking whether a
start date and end date have been provided, and looking to see what's
selected in a list box of areas, but you're not using start date, end date
or area anywhere!

 

[Dan @BCBS]

First, please understand that I really appreciate you Crystal and this MSN
format to get help. I inharited many critial Access databases, some very
old, many people have worked on them. I completly understand your comments
about this code, I agree, but it produces an out that is being used with some
degree of correctness.

I just need to re-write it, bottom line is, I need output based on 2-List
boxes and 1-Combo Box...

Thanks for the help

 

[Douglas J. Steele]

You missed my point. Your code isn't doing anything! You're not trying to
tell me that you get different results depending on what values are in
txtStar or txtEnd, or what's been selected in lstArea are you?

Without knowing the names of your table fields and form controls, it's
difficult for me to give you a precise answer.

I'll make the following assumptions:

- you want to compare what's in numeric field Field1 to what's been selected
in list box Listbox1
- you want to compare what's in text field Field2 to what's been selected in
list box Listbox2
- you want to compare what's in text field Field3 to what's been selected in
combo box Combobox1
- you want to launch report MyReport, but only if at least 1 record has been
selected in Listbox1, at least one record has been selected in Listbox2 and
a record has been selected in Combobox1

That out of the way, you need code like:

Dim strError As String
Dim strWhere As String
Dim varItem As Variant

If Me.Listbox1.ItemsSelected.Count = 0 Then
strError = "Please pick an item in Listbox1." & vbCrLf
End If
If Me.Listbox2.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in Listbox2." & vbCrLf
End If
If IsNull(Me.Combobox1) = True Then
strError = strError & "Please pick an item in Combobox2." & vbCrLf
End If

If Len(strError) > 0 Then
Msgbox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.Listbox2.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem) & _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.Combobox1 & Chr$(34)
Call SetReportFilter("MyReport", strWhere)
DoCmd.SendObject acReport, "MyReport", acFormatRTF, , , , , , True
End If

 

[Dan @BCBS]

Thanks for your time and patience.
No, to your first paragraph.
Yes, your assumptions are correct.

Everything except the Call seems to work correctly..

I assume this is where you tell me to call my report (rptQuualityAuditList)..
I've tried a few combinations, most recent:

Dim stDocName As String
If.........
stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Which gives me a runtime error '3075'

 

[Douglas J Steele]

The text that accompanies the error should give some clue. What does it say?

 

[Dan @BCBS]

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList

 

[Dan @BCBS]

Because of this error I added the following.
Although I do not get any errors - a pop up flashes "now outputting reprot
to email.... So, replaced:
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
"with"
DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

Any Suggestions, I know I'm close with your help..


Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" & Me.txtEnd & "#"
End If

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList

The text that accompanies the error should give some clue. What does it say?

 

[Douglas J Steele]

Show the actual code you're using.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Because of this error I added the following.
Although I do not get any errors - a pop up flashes "now outputting reprot
to email.... So, replaced:
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
"with"
DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

Any Suggestions, I know I'm close with your help..


Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" & Me.txtEnd & "#"
End If

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList

The text that accompanies the error should give some clue. What does it say?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your time and patience.
No, to your first paragraph.
Yes, your assumptions are correct.

Everything except the Call seems to work correctly..

I assume this is where you tell me to call my report
(rptQuualityAuditList)..
I've tried a few combinations, most recent:

Dim stDocName As String
If.........
stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Which gives me a runtime error '3075'








:

You missed my point. Your code isn't doing anything! You're not trying
to
tell me that you get different results depending on what values are in
txtStar or txtEnd, or what's been selected in lstArea are you?

Without knowing the names of your table fields and form controls, it's
difficult for me to give you a precise answer.

I'll make the following assumptions:

- you want to compare what's in numeric field Field1 to what's been
selected
in list box Listbox1
- you want to compare what's in text field Field2 to what's been
selected in
list box Listbox2
- you want to compare what's in text field Field3 to what's been
selected in
combo box Combobox1
- you want to launch report MyReport, but only if at least 1 record has
been
selected in Listbox1, at least one record has been selected in Listbox2
and
a record has been selected in Combobox1

That out of the way, you need code like:

Dim strError As String
Dim strWhere As String
Dim varItem As Variant

If Me.Listbox1.ItemsSelected.Count = 0 Then
strError = "Please pick an item in Listbox1." & vbCrLf
End If
If Me.Listbox2.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in Listbox2." & vbCrLf
End If
If IsNull(Me.Combobox1) = True Then
strError = strError & "Please pick an item in Combobox2." & vbCrLf
End If

If Len(strError) > 0 Then
Msgbox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.Listbox2.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem)
& _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.Combobox1 & Chr$(34)
Call SetReportFilter("MyReport", strWhere)
DoCmd.SendObject acReport, "MyReport", acFormatRTF, , , , , , True
End If

 

[Dan @BCBS]

Private Sub cmdSelect_Click()
Dim strError As String
Dim strWhere As String
Dim varItem As Variant
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" & Me.txtEnd & "#"
End If

If Me.lstArea.ItemsSelected.Count = 0 Then
strError = "Please pick an item in lstArea." & vbCrLf
End If
If Me.lstProduct.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstProduct." & vbCrLf
End If
If IsNull(Me.cboReviewer) = True Then
strError = strError & "Please pick an item in cboReviewer." & vbCrLf
End If

If Len(strError) > 0 Then
MsgBox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.lstArea.ItemsSelected
strWhere = strWhere & Me.lstArea.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.lstProduct.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.lstArea.ItemData(varItem) & _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.cboReviewer & Chr$(34)

stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
'DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

End If
End Sub

Show the actual code you're using.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Because of this error I added the following.
Although I do not get any errors - a pop up flashes "now outputting reprot
to email.... So, replaced:
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
"with"
DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

Any Suggestions, I know I'm close with your help..


Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" & Me.txtEnd & "#"
End If

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "# and #" &
Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList & " and
[insurancetype] " & stProductList


:

The text that accompanies the error should give some clue. What does it say?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your time and patience.
No, to your first paragraph.
Yes, your assumptions are correct.

Everything except the Call seems to work correctly..

I assume this is where you tell me to call my report
(rptQuualityAuditList)..
I've tried a few combinations, most recent:

Dim stDocName As String
If.........
stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True

Which gives me a runtime error '3075'








:

You missed my point. Your code isn't doing anything! You're not trying
to
tell me that you get different results depending on what values are in
txtStar or txtEnd, or what's been selected in lstArea are you?

Without knowing the names of your table fields and form controls, it's
difficult for me to give you a precise answer.

I'll make the following assumptions:

- you want to compare what's in numeric field Field1 to what's been
selected
in list box Listbox1
- you want to compare what's in text field Field2 to what's been
selected in
list box Listbox2
- you want to compare what's in text field Field3 to what's been
selected in
combo box Combobox1
- you want to launch report MyReport, but only if at least 1 record has
been
selected in Listbox1, at least one record has been selected in Listbox2
and
a record has been selected in Combobox1

That out of the way, you need code like:

Dim strError As String
Dim strWhere As String
Dim varItem As Variant

If Me.Listbox1.ItemsSelected.Count = 0 Then
strError = "Please pick an item in Listbox1." & vbCrLf
End If
If Me.Listbox2.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in Listbox2." & vbCrLf
End If
If IsNull(Me.Combobox1) = True Then
strError = strError & "Please pick an item in Combobox2." & vbCrLf
End If

If Len(strError) > 0 Then
Msgbox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.Listbox2.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem)
& _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.Combobox1 & Chr$(34)
Call SetReportFilter("MyReport", strWhere)
DoCmd.SendObject acReport, "MyReport", acFormatRTF, , , , , , True
End If

 

[Douglas J Steele]

I don't see gbuLocation anywhere in that code.

Given you essentially copied the code I gave you, where's your call to
Crystal's routine:

Call SetReportFilter(stDocName, strWhere)

(and don't forget you need to copy Crystal's routine into your application)

Private Sub cmdSelect_Click()
Dim strError As String
Dim strWhere As String
Dim varItem As Variant
Dim stDocName As String

If IsNull(Me.txtStart)
strError = "Please enter a start date to run this report." & vbCrLf
End If
If IsNull(Me.txtEnd) Then
strError = strError & "Please enter an end date to run this report."
& vbCrLf
End If
If Me.lstArea.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstArea." & vbCrLf
End If
If Me.lstProduct.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstProduct." & vbCrLf
End If
If IsNull(Me.cboReviewer) = True Then
strError = strError & "Please pick an item in cboReviewer." & vbCrLf
End If

If Len(strError) > 0 Then
MsgBox strError
Else
strWhere = "[issueclosedate] between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & _
" And " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") & _
" Field1 In ("
For Each varItem In Me.lstArea.ItemsSelected
strWhere = strWhere & Me.lstArea.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.lstProduct.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.lstArea.ItemData(varItem) & _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field3 = " & Chr$(34) & Me.cboReviewer & Chr$(34)

stDocName = "rptQualityAuditList"
Call SetReportFilter(stDocName, strWhere)
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
End If

End Sub

You DO realize, I hope, that you need to replace Field1, Field2 and Field3
with the actual names of the fields you're trying to filter on?

If that doesn't work, what happens if you run:

DoCmd.OpenReport stDocName, acNormal, , strWhere

instead of the SendObject?

If still no joy, put

Debug.Print strWhere

instead of either the SendObject or OpenReport statement, then go to the
Immediate Window (Ctrl-G) and see what's printed there. Does it look
reasonable?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Private Sub cmdSelect_Click()
Dim strError As String
Dim strWhere As String
Dim varItem As Variant
Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart & "# and
#" & Me.txtEnd & "#"
End If

If Me.lstArea.ItemsSelected.Count = 0 Then
strError = "Please pick an item in lstArea." & vbCrLf
End If
If Me.lstProduct.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstProduct." & vbCrLf
End If
If IsNull(Me.cboReviewer) = True Then
strError = strError & "Please pick an item in cboReviewer." & vbCrLf
End If

If Len(strError) > 0 Then
MsgBox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.lstArea.ItemsSelected
strWhere = strWhere & Me.lstArea.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.lstProduct.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.lstArea.ItemData(varItem) & _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.cboReviewer & Chr$(34)

stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
'DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

End If
End Sub

Show the actual code you're using.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Because of this error I added the following.
Although I do not get any errors - a pop up flashes "now outputting reprot
to email.... So, replaced:
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
"with"
DoCmd.OpenReport stDocName, acNormal, , stLinkCriteria

Any Suggestions, I know I'm close with your help..


Dim stLinkCriteria As String

If IsNull(Me.txtStart) Or IsNull(Me.txtEnd) Then
MsgBox "Please enter a start and end date to run this report."
Exit Sub
Else
stLinkCriteria = "[issueclosedate] between #" & Me.txtStart &

"#
and

#" & Me.txtEnd & "#"
End If

:

Syntax error in date in query expression '[gbuLocation] and [issueclosedate]
between ## and ##'.

The [issueclosedate] = issueclosedate between #" & Me.txtStart & "#

and
#" &

Me.txtEnd & "#"

The [gbuLocation] = stLinkCriteria = "[gbulocation] " & stAreaList

& "
and

[insurancetype] " & stProductList


:

The text that accompanies the error should give some clue. What

does
it say?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks for your time and patience.
No, to your first paragraph.
Yes, your assumptions are correct.

Everything except the Call seems to work correctly..

I assume this is where you tell me to call my report
(rptQuualityAuditList)..
I've tried a few combinations, most recent:

Dim stDocName As String
If.........
stDocName = "rptQualityAuditList"
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , ,

,
True

Which gives me a runtime error '3075'








:

You missed my point. Your code isn't doing anything! You're

not
trying

to
tell me that you get different results depending on what

values
are in

txtStar or txtEnd, or what's been selected in lstArea are you?

Without knowing the names of your table fields and form

controls,
it's

difficult for me to give you a precise answer.

I'll make the following assumptions:

- you want to compare what's in numeric field Field1 to what's been
selected
in list box Listbox1
- you want to compare what's in text field Field2 to what's been
selected in
list box Listbox2
- you want to compare what's in text field Field3 to what's been
selected in
combo box Combobox1
- you want to launch report MyReport, but only if at least 1 record has
been
selected in Listbox1, at least one record has been selected in Listbox2
and
a record has been selected in Combobox1

That out of the way, you need code like:

Dim strError As String
Dim strWhere As String
Dim varItem As Variant

If Me.Listbox1.ItemsSelected.Count = 0 Then
strError = "Please pick an item in Listbox1." & vbCrLf
End If
If Me.Listbox2.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in Listbox2."

&
vbCrLf

End If
If IsNull(Me.Combobox1) = True Then
strError = strError & "Please pick an item in

Combobox2." &
vbCrLf

End If

If Len(strError) > 0 Then
Msgbox strError
Else
strWhere = "Field1 In ("
For Each varItem In Me.Listbox1.ItemsSelected
strWhere = strWhere & Me.Listbox1.ItemData(varItem) &

",
"

Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND

Field2
In ("

For Each varItem In Me.Listbox2.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.Listbox1.ItemData(varItem)
& _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field2 = " & Chr$(34) & Me.Combobox1 & Chr$(34)
Call SetReportFilter("MyReport", strWhere)
DoCmd.SendObject acReport, "MyReport", acFormatRTF, , ,

, ,
, True

End If

 

[strive4peace]

Thanks, Doug

Dan, the advice Doug gave you to first test your filter
string by using it in the WhereClause of the OpenReport
action is good. If your filter doesn't work, then you need
to fix that first.

DoCmd.OpenReport stDocName, acPreview, , strWhere

If you can change the parameters on your form and
successfully open a filtered report for different criteria,
then you are ready for the next step:

1. using code, save the filter in your report by calling the
SetReportFilter procedure. For parameters, it will need a
reportname and the WHERE clause (without the word WHERE) of
an SQL statement -- this is what you are calling strWhere.

2. use SendObject to eMail the report (which is now filtered)

3. optionally, use SetReportFilter with "" for the pFilter
parameter to remove the filter from the report definition.

'~~~~~~~~~~~~~~~~~

How to Create a General Module

1. from the databae window, click on the Module tab
2. click on the NEW command button
3. type (or paste) the code in

once the code is in the module sheet, do
Debug,Compile from the menu

if there are no syntax/reference errors, nothing will appear
to happen -- this is good

Whenever you write or paste code, your should ALWAYS compile
it before you attempt to run it.

during compilation, if something DOES happen ...

fix any errors on the yellow highlighted lines

keep compiling until nothing happens


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I don't see gbuLocation anywhere in that code.

Given you essentially copied the code I gave you, where's your call to
Crystal's routine:

Call SetReportFilter(stDocName, strWhere)

(and don't forget you need to copy Crystal's routine into your application)

Private Sub cmdSelect_Click()
Dim strError As String
Dim strWhere As String
Dim varItem As Variant
Dim stDocName As String

If IsNull(Me.txtStart)
strError = "Please enter a start date to run this report." & vbCrLf
End If
If IsNull(Me.txtEnd) Then
strError = strError & "Please enter an end date to run this report."
& vbCrLf
End If
If Me.lstArea.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstArea." & vbCrLf
End If
If Me.lstProduct.ItemsSelected.Count = 0 Then
strError = strError & "Please pick an item in lstProduct." & vbCrLf
End If
If IsNull(Me.cboReviewer) = True Then
strError = strError & "Please pick an item in cboReviewer." & vbCrLf
End If

If Len(strError) > 0 Then
MsgBox strError
Else
strWhere = "[issueclosedate] between " & _
Format(Me.txtStart, "\#mm\/dd\/yyyy\#") & _
" And " & Format(Me.txtEnd, "\#mm\/dd\/yyyy\#") & _
" Field1 In ("
For Each varItem In Me.lstArea.ItemsSelected
strWhere = strWhere & Me.lstArea.ItemData(varItem) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND Field2 In ("
For Each varItem In Me.lstProduct.ItemsSelected
strWhere = strWhere & Chr$(34) & Me.lstArea.ItemData(varItem) & _
Chr$(34) & ", "
Next varItem
strWhere = Left(strWhere, Len(strWhere) - 2) & ") AND " & _
"Field3 = " & Chr$(34) & Me.cboReviewer & Chr$(34)

stDocName = "rptQualityAuditList"
Call SetReportFilter(stDocName, strWhere)
DoCmd.SendObject acReport, stDocName, acFormatRTF, , , , , , True
End If

End Sub

You DO realize, I hope, that you need to replace Field1, Field2 and Field3
with the actual names of the fields you're trying to filter on?

If that doesn't work, what happens if you run:

DoCmd.OpenReport stDocName, acNormal, , strWhere

instead of the SendObject?

If still no joy, put

Debug.Print strWhere

instead of either the SendObject or OpenReport statement, then go to the
Immediate Window (Ctrl-G) and see what's printed there. Does it look
reasonable?