|
|
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" wrote:
[Quoted Text] > 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)
>
>
|
|
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" wrote:
[Quoted Text] > 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" wrote:
>
> > 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)
> >
> >
|
|
|