Trouble generating Filter for emailing report...

  • Thread starter Thread starter Lynndyhop
  • Start date Start date
L

Lynndyhop

Hi there,

I am creating a basic helpdesk database that tracks problem calls. I want to
be able to have the call taker assign a ticket to anyone by having a button
that generates an email (ie. Sendobject). I've created a report for the Send
Object to send, but am having trouble getting the filter to work.

It appears that it is not filtering the report - I still get all records,
but the form is filtering and is not turning off.

I tried to create a filter in the report (ie. Filter field: "CallID=" &
Me![CallID] instead of creating filter code on the button on the form, but it
also wasn't filtering.

Here is what I have:

Dim RptName As String
Dim strSQL As String

RptName = "EmailTicketReport"
strSQL = "[CallID] = " & Me.CallID

Me.Filter = strSQL
Me.FilterOn = True

DoCmd.SendObject acSendReport, RptName, acFormatTXT, , , , "ESR Ticket
Assignment " & Me.CallID

Me.FilterOn = False

Many thanks for your help :0)
 
Lynndy,

Filtering your form, will not filter a report.

In my experience, I've found that in order for SendObject to send a filtered
report, the filter has to be applied to the report ahead of time, not in the
SendObject. You can do this in the reports query, by referring to a field in
your form. In your case, the REPORTS query might have a WHERE clause that
looks something like:

WHERE [CallID] = [Forms]![yourFormName].[CallID]

The down side of this technique is that it makes that report good only when
that specific form is open. So if you want to be able to use that report in
other places in your application, you have to copy it, and change the query.

I prefer to create a function which I can call from anywhere in my
application (including a query). You will need to create a new code module,
and store the function below in that module.

Then, in the reports query, I would add a column that looks like:

Expr1:fnCallID()
Criteria: NULL OR [CallID]

Then, before you run the report, you can either set the value of the
function to NULL:

Call fnCallID(Reset:=True)

or set its value by passing a single CallID

Call fnCallID(me.CallID)

Then, when you run the report, if fnCallID() returns a NULL, all of the
records will be seen, but if it returns a single ID #, then only that report
record will be visible.

Public Function fnCallID(Optional SomeValue As Variant = Null, _
Optional Reset As Boolean = False) As
Variant

Static myCallID As Variant

If Reset = True Then
myCallID = Null
ElseIf Not IsNull(SomeValue) Then
myCallID = SomeValue
ElseIf IsEmpty(SomeValue) Then
myCallID = Null
End If

fnCallID = myCallID

End Function

BTW, you can also test this without having to open a form. Just go to the
Immediate (debug) window and type: ?fnCallID(Reset:=True).

Then when open your report in preview mode. You should see all of your
records.

Then type: ?fnCallID(23) 'use a valid CallID

Now when you open your report, you should only see that one record.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Lynndyhop said:
Hi there,

I am creating a basic helpdesk database that tracks problem calls. I want to
be able to have the call taker assign a ticket to anyone by having a button
that generates an email (ie. Sendobject). I've created a report for the Send
Object to send, but am having trouble getting the filter to work.

It appears that it is not filtering the report - I still get all records,
but the form is filtering and is not turning off.

I tried to create a filter in the report (ie. Filter field: "CallID=" &
Me![CallID] instead of creating filter code on the button on the form, but it
also wasn't filtering.

Here is what I have:

Dim RptName As String
Dim strSQL As String

RptName = "EmailTicketReport"
strSQL = "[CallID] = " & Me.CallID

Me.Filter = strSQL
Me.FilterOn = True

DoCmd.SendObject acSendReport, RptName, acFormatTXT, , , , "ESR Ticket
Assignment " & Me.CallID

Me.FilterOn = False

Many thanks for your help :0)
Click to expand...
 
Thanks Dale!

I ended up filtering on the query as you said, but also on the form. The
good news is, it works! Though I have a feeling I am doing some messy VB
here, and probably doubling up my filter.... Here is what I ended up with:

Under my Query:
[Forms]![TblCalls]![CallID]

And my Button Control:
Private Sub EmailForm_Click()
On Error GoTo Err_EmailForm_Click

Const errUserCanceledAction As Long = 2501
Dim RptName As String
Dim strSQL As String
Dim Recip As String

RptName = "ESRHelpdeskTicketAssignment"
strSQL = "[CallID] = " & Me.CallID
Recip = Me.EmailAddy

Me.Filter = strSQL
Me.FilterOn = True

DoCmd.SendObject acSendReport, RptName, acFormatTXT, Recip, , , "ESR
Ticket Assignment " & Me.CallID, "A new ticket has been assigned to you.
Please view the attached report."

Me.Form.FilterOn = False


Exit_EmailForm_Click:
Exit Sub
Me.Filter = False

Err_EmailForm_Click:
If Err.Number = errUserCanceledAction Then
Me.Form.FilterOn = False
'Do nothing
Else
MsgBox Err.Description
Resume Exit_EmailForm_Click
End If


End Sub


Dale Fye said:
Lynndy,

Filtering your form, will not filter a report.

In my experience, I've found that in order for SendObject to send a filtered
report, the filter has to be applied to the report ahead of time, not in the
SendObject. You can do this in the reports query, by referring to a field in
your form. In your case, the REPORTS query might have a WHERE clause that
looks something like:

WHERE [CallID] = [Forms]![yourFormName].[CallID]

The down side of this technique is that it makes that report good only when
that specific form is open. So if you want to be able to use that report in
other places in your application, you have to copy it, and change the query.

I prefer to create a function which I can call from anywhere in my
application (including a query). You will need to create a new code module,
and store the function below in that module.

Then, in the reports query, I would add a column that looks like:

Expr1:fnCallID()
Criteria: NULL OR [CallID]

Then, before you run the report, you can either set the value of the
function to NULL:

Call fnCallID(Reset:=True)

or set its value by passing a single CallID

Call fnCallID(me.CallID)

Then, when you run the report, if fnCallID() returns a NULL, all of the
records will be seen, but if it returns a single ID #, then only that report
record will be visible.

Public Function fnCallID(Optional SomeValue As Variant = Null, _
Optional Reset As Boolean = False) As
Variant

Static myCallID As Variant

If Reset = True Then
myCallID = Null
ElseIf Not IsNull(SomeValue) Then
myCallID = SomeValue
ElseIf IsEmpty(SomeValue) Then
myCallID = Null
End If

fnCallID = myCallID

End Function

BTW, you can also test this without having to open a form. Just go to the
Immediate (debug) window and type: ?fnCallID(Reset:=True).

Then when open your report in preview mode. You should see all of your
records.

Then type: ?fnCallID(23) 'use a valid CallID

Now when you open your report, you should only see that one record.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Lynndyhop said:
Hi there,

I am creating a basic helpdesk database that tracks problem calls. I want to
be able to have the call taker assign a ticket to anyone by having a button
that generates an email (ie. Sendobject). I've created a report for the Send
Object to send, but am having trouble getting the filter to work.

It appears that it is not filtering the report - I still get all records,
but the form is filtering and is not turning off.

I tried to create a filter in the report (ie. Filter field: "CallID=" &
Me![CallID] instead of creating filter code on the button on the form, but it
also wasn't filtering.

Here is what I have:

Dim RptName As String
Dim strSQL As String

RptName = "EmailTicketReport"
strSQL = "[CallID] = " & Me.CallID

Me.Filter = strSQL
Me.FilterOn = True

DoCmd.SendObject acSendReport, RptName, acFormatTXT, , , , "ESR Ticket
Assignment " & Me.CallID

Me.FilterOn = False

Many thanks for your help :0)
Click to expand...
Click to expand...
 
Back
Top