filter for a report

[CHEECO]

I need help with writing a filter code for a report ID number. I want to print a report based on the report ID number. The report name is "rptMonthlyCircleLog" I am lost on this one.

Thanks



Cheeco

[June7]

What is 'report ID number'? ID number of what - customer, account, project?

Whatever this ID number is for, select it from an UNBOUND combobox list or set focus to a record on form.

Then code can open report filtered to that value like:

DoCmd.OpenReport "rptMonthlyCircleLog", , , "ID=" & Me.cbxID

Review http://allenbrowne.com/ser-62.html

[CHEECO]

I applied the filter to my Report and then went to set it up in the button control where is says apply filter then I get this

Filter Name

Where Condition

Control Name

I have no idea what these are oe what I am suppose to type in these boxes.

[June7]

What do you mean by 'applied the filter to my report'? Exactly what did you do?

Set up the button how - with button wizard? Looks like it is opening macro builder.

This is all basic Access functionality. Any introductory tutorial book should have guidance on this.

[CHEECO]

I found this code for opening and emailing a report. Opening the form works fine however I keep getting a error with the line "Dim oApp As New Outlook.Application". The error message is " Compile Error, User-Defined type not defined" Any ideas.

Private Sub Command334_Click()    On Error GoTo ErrHandler
    DoCmd.OpenReport ReportName:="rptMonthlyCircleLog", View:=acViewPreview, _
        WhereCondition:="ID=" & Me.ID
    Exit Sub


ErrHandler:
    ' Don't show error message if report was canceled
    If Err <> 2501 Then
        MsgBox Err.Description, vbCritical
    End If
    
   
Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String


'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.path & "\rptMonthlyCircleLog_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "rptMonthlyCircleLog", acFormatPDF, fileName, False


'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add "cheecochongo@outlook.com@email.com"
    .Subject = "Training Roster"
    .Body = "Roster Information"
    .Attachments.Add fileName
    .Send
End With


MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

[isladogs]

Add the VBA Outlook reference library for your Outlook version e.g. Microsoft Outlook 16.0 Object Library if using Outlook 2016

[June7]

Or maybe try the DoCmd.SendObject method. Don't need an Outlook object and therefore no Outlook library.

[CHEECO]

I added the outlook library and now it is working but it is not filtering the report as it should. It is suppose to filter the report according to the report ID. But we are making progress. That is a good thing.

[Micron]

Is ID a number? If not, you need single quotes before and after the variable part.

For those who know more about vba, is this colon thing a valid construct that allows you to ignore the placeholders for comma separated parameters? I'm not used to seeing colon delimited parameters in DoCmd methods. The above is missing the FilterName parameter that normally you see between View and Where Condition when using commas.

[CHEECO]

The id is a number

[Micron]

The id is a number

I see I missed that from #1. You're not getting a parameter prompt, so ID must be a field in the report's underlying query. Suggest you open the query in design view and put a valid ID number in the correct query field and run it. If you still get all records, then there is something amiss with the query for the report.

Another test might be to put Msgbox Me.ID right after your on error line (there are other ways but they take longer to explain). If you get an empty message box, ID isn't being picked up (i.e. it is Null or an empty string).

[CHEECO]

OK I got it. The below code works great. Thanks for all the help.

Private Sub cmdEmail_Click()

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String


'filter report
'DoCmd.OpenReport "rptMonthlyCircleLog", , , "ID=" & Me.cbxID
DoCmd.OpenReport "rptMonthlyCircleLog", acViewPreview, , "ID = " & ID




'Export report in same folder as db with date stamp
todayDate = Format(Date, "MMDDYYYY")
fileName = Application.CurrentProject.path & "\rptMonthlyCircleLog_" & todayDate & ".pdf"
DoCmd.OutputTo acReport, "rptMonthlyCircleLog", acFormatPDF, fileName, False


'Email the results of the report generated
Set oEmail = oApp.CreateItem(olMailItem)
With oEmail
    .Recipients.Add "cheecochongo@outlook.com"
    .Subject = "Training Roster"
    .Body = "Roster Information"
    .Attachments.Add fileName
    .Send
End With




 DoCmd.OpenReport ReportName:="rptMonthlyCircleLog", View:=acViewPreview, _
        WhereCondition:="ID=" & Me.ID
    Exit Sub




ErrHandler:
    ' Don't show error message if report was canceled
    If Err <> 2501 Then
        MsgBox Err.Description, vbCritical
    End If
    
   


MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"
End Sub

[Micron]

Are we to assume the issue was using the colon syntax for your parameters? Or was it something else? The info might help someone else in the future.
Thanks.

[June7]

Yes, I have seen the colon syntax. This is 'named arguments'. Allows not having to put the arguments in a specific order. Definitely works.

[isladogs]

Yes, I have seen the colon syntax. This is 'named arguments'. Allows not having to put the arguments in a specific order. Definitely works.

I'd never seen the point of the named arguments approach as its just extra typing.
However, I wasn't aware that you could then do any order you like & presumably therefore omit unused items