Re: Combo Box

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Combo Box
    ... you need to SAVE the filter to the report. ... Sub SetReportFilter(_ ... ByVal pReportName As String, _ ... Dim stArea As Variant ...
    (microsoft.public.access.formscoding)
  • Re: Assigning a query to a new report in VBA
    ... string in a hidden text box on your form. ... Although the report can be created and saved by ... DoCmd.OpenReport pReportName, acViewDesign ... Sub CreateName() ...
    (microsoft.public.access.reports)
  • 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)
  • Assigning a query to a new report in VBA
    ... I'm trying to pass a SQL string in access vba to a report which I ... DoCmd.OpenReport pReportName, acViewDesign ... Sub CreateName() ...
    (microsoft.public.access.reports)
  • Re: Report prints data twice on two different pages
    ... SQL record source from Part Certification Report: ... Record Source from Cert Order Sub: ... "Duane Hookom" wrote: ... Part Certification with the key field of PartCertificationID with a one to ...
    (microsoft.public.access.reports)