Re: Combo Box

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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







.

Relevant Pages

  • Re: How do I change a report label control from a form code modual
    ... Microsoft Access MVP 2006 ... remote programming and training ... ByVal pReportName As String, _ ... Dim rpt As Report ...
    (microsoft.public.access.modulesdaovba)
  • Re: Combo Box
    ... filtered report, but nowhere are you passing the criteria you've generated ... MsgBox "Please pick a reviewer." ... Sub SetReportFilter(_ ... ByVal pReportName As String, _ ...
    (microsoft.public.access.formscoding)
  • RE: Comparison Report based on a query of only calculations.
    ... Dave Hargis, Microsoft Access MVP ... "Amy Schmid" wrote: ... Client number Client Name Avg # of Cows Production Livestock ... And you want them to appear on the report like: ...
    (microsoft.public.access.reports)
  • Re: In a report -Using a calculated field in Select Case causes Er
    ... Dave Hargis, Microsoft Access MVP ... "Maverick" wrote: ... should open on its own and suspend execution of the report. ... The line has not executed, but it will be the next to execute. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Report parameters
    ... Microsoft Access MVP ... and you'll never work a day in your life." ... and make sure you use cboEmpID value in your Open Report ...
    (microsoft.public.access.reports)