Re: Combo Box

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4AE2C398-62EB-4094-86CD-205B8BAE871E@xxxxxxxxxxxxxxxx
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" wrote:

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



--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EF595C7C-249E-4B11-A42C-83B26FF65CE6@xxxxxxxxxxxxxxxx
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" wrote:

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!

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:725EAD66-AE86-425D-B8C2-95D70016244E@xxxxxxxxxxxxxxxx
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" wrote:

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)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Dan @BCBS" <DanBCBS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:546550FF-0651-466B-8914-A08B19231458@xxxxxxxxxxxxxxxx
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




"strive4peace" wrote:

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 wrote:
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


.

Relevant Pages