Sending report about the current record in mail

[Amir]

Hi!

I want to add a button to the form so that if the user presses that button,
access will open a new email message in outlook with a report showing only
the record the user is currently on.
There is a primary field with uniqe value for each record called "Index" so
that it can be used in order to filter the form, but how can I "get" the
value of the "Index" field of the record the user is currently on, so that I
can make sure that only the current record will be in that report?

Regrads, Amir.

 

[Allen Browne]

As you found, there is no WhereCondition for SendObject, like there is for
OpenReport. The workaround is to create a public string variable to hold the
filter value, and then use the Open event of the report to apply it as a
filter so the report opens and is sent with just the record you need.

1. In a general module (Modules tab of Database window), add this line to
the General Declarations section of a module (at the top, with the Option
statements):

Public gstrReportFilter As String

2. In the code that calls SendObject, set the string to the filter value
first:

Private Sub cmdEmailReport_Click()
If Me.Dirty Then Me.Dirty = False
gstrReportFilter = "[Index] = " & Me.[Index]
DoCmd.SendObject acSendReport, "Report1"
End Sub

3. In the Open event of the report, apply the filter string, and then clear
it:

Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub


Note: If Index is a Text type field (not a Number field), you need extra
quotes:
gstrReportFilter = "[Index] = """ & Me.[Index] & """"

 

[Amir]

Thank you very much, Allen!

I will be grateful if you explain the purpose of the following line in your
solution:

If Me.Dirty Then Me.Dirty = False

Regards,
Amir.

As you found, there is no WhereCondition for SendObject, like there is for
OpenReport. The workaround is to create a public string variable to hold the
filter value, and then use the Open event of the report to apply it as a
filter so the report opens and is sent with just the record you need.

1. In a general module (Modules tab of Database window), add this line to
the General Declarations section of a module (at the top, with the Option
statements):

Public gstrReportFilter As String

2. In the code that calls SendObject, set the string to the filter value
first:

Private Sub cmdEmailReport_Click()
If Me.Dirty Then Me.Dirty = False
gstrReportFilter = "[Index] = " & Me.[Index]
DoCmd.SendObject acSendReport, "Report1"
End Sub

3. In the Open event of the report, apply the filter string, and then clear
it:

Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub


Note: If Index is a Text type field (not a Number field), you need extra
quotes:
gstrReportFilter = "[Index] = """ & Me.[Index] & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to add a button to the form so that if the user presses that
button,
access will open a new email message in outlook with a report showing only
the record the user is currently on.
There is a primary field with uniqe value for each record called "Index"
so
that it can be used in order to filter the form, but how can I "get" the
value of the "Index" field of the record the user is currently on, so that
I
can make sure that only the current record will be in that report?

Regrads, Amir.

 

[Allen Browne]

It just saves any edits that are in progress.

When you begin editing a record, Access sets the Dirty property of the form
to true. You can save the record by setting Dirty to False.

You could do the same with:
RunCommand acCmdSaveRecord
but that only works if the form has focus. I prefer to set the form's Dirty
property because that way I am specifying exactly which form is to have the
record saved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you very much, Allen!

I will be grateful if you explain the purpose of the following line in
your
solution:

If Me.Dirty Then Me.Dirty = False

Regards,
Amir.

As you found, there is no WhereCondition for SendObject, like there is
for
OpenReport. The workaround is to create a public string variable to hold the
filter value, and then use the Open event of the report to apply it as a
filter so the report opens and is sent with just the record you need.

1. In a general module (Modules tab of Database window), add this line to
the General Declarations section of a module (at the top, with the Option
statements):

Public gstrReportFilter As String

2. In the code that calls SendObject, set the string to the filter value
first:

Private Sub cmdEmailReport_Click()
If Me.Dirty Then Me.Dirty = False
gstrReportFilter = "[Index] = " & Me.[Index]
DoCmd.SendObject acSendReport, "Report1"
End Sub

3. In the Open event of the report, apply the filter string, and then clear
it:

Private Sub Report_Open(Cancel As Integer)
If Len(gstrReportFilter) > 0 Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub


Note: If Index is a Text type field (not a Number field), you need extra
quotes:
gstrReportFilter = "[Index] = """ & Me.[Index] & """"

I want to add a button to the form so that if the user presses that
button,
access will open a new email message in outlook with a report showing only
the record the user is currently on.
There is a primary field with uniqe value for each record called
"Index"
so
that it can be used in order to filter the form, but how can I "get"
the
value of the "Index" field of the record the user is currently on, so that
I
can make sure that only the current record will be in that report?

Regrads, Amir.