I want the email send to be able to stop at a particular place in the record

[shyley]

Hello everyone, please i need your help, i have a code in the module and am trying to be able to set the vba code to able to stop at the end of record, i have tried the the Myset.EOF which is not working for me because it keeps runing with no ending, i don't maybe theres other ways to this kindly help
this is the code
Function SendMyEmail()

Dim MySet As ADODB.Recordset

Set MySet = New ADODB.Recordset
MySet.Open "qryStaffSalary", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF


DoCmd.SendObject acReport, "rptStaffSalary", "SnapshotFormat(*.snp)", , "", "", "Payslip", "Please find attached your payslip", True, ""

MySet.MoveNext
Loop

End Function

i don't know how to explain this but if you can relate kindly help
many thanks

 

[oleronesoftwares]

Hi, the record you want it to stop at must have an identifier that will be referenced in the vba code.

Alternatively if the record has a value different from others e.g date field that is today, while others have date field before today, you can have the query "qryStaffSalary" exclude the record, by putting it in the where clause.

 

[Uncle Gizmo]

My guess is that you have a table containing your staff payslip information.

You have a single report which shows the payslip details for all staff members.
It shows every payslip, but you want to send only the page that relates to the email recipient.

Is that correct? If not, please provide more information....

 

[isladogs]

Filter your query to just show the specific record required

 

[Gasman]

Another way is open the report with criteria, send, then close, repeat until EOF ?

It has been asked many times here, so a search here should bring up the method?

Edit: This is the method I was thinking of

Output to separate PDF files based on field

Hi there, A bit of background on this report. It is a payroll report that for security reasons I can't build in Cognos. The data is coming from an Excel sheet which will be updated by payroll staff and they wish the output to be in pdf format on a network drive based on the department. They...

www.access-programmers.co.uk

 

[shyley]

Hi, the record you want it to stop at must have an identifier that will be referenced in the vba code.

Alternatively if the record has a value different from others e.g date field that is today, while others have date field before today, you can have the query "qryStaffSalary" exclude the record, by putting it in the where clause.

yes, am a beginner in access, i don't know how to right it, can you help please

many thanks

 

[shyley]

My guess is that you have a table containing your staff payslip information.

You have a single report which shows the payslip details for all staff members.
It shows every payslip, but you want to send only the page that relates to the email recipient.

Is that correct? If not, please provide more information....

yes sir, its correct

 

[shyley]

Filter your query to just show the specific record required

i have filter but its not saving when i close and reopen its still go back to show everything and also the reports won't get the filter

 

[Uncle Gizmo]

yes sir, its correct

also the reports won't get the filter

I answered a very similar question a while back and I made a blog about the answer on my website here:-

Generate Multiple Reports - Nifty Access

Generate Multiple Reports This blog came about because of an answer I gave to a particular OP's question. Therefore the code isn't generic, it is designed for a specific situation, like most examples! However I hope there's enough information here for you to follow what's happening. Multiple...

www.niftyaccess.com

In this example I did not apply filters I modified the report record source.

The modified record source is extracted by the report from the calling form.

This techniques avoids some of the problems you can have in generating reports based on changing filters / criteria.

 

[Uncle Gizmo]

In this YouTube video I demonstrate how to pass a filter into a Report from a Form no code required in the Report:-

 

[Gasman]

In this YouTube video I demonstrate how to pass a filter into a Report from a Form no code required in the Report:-

@Uncle Gizmo However in this case the criteria is coming from a recordset?

 

[Uncle Gizmo]

criteria is coming from a recordset

Hi Paul,
In this example the Filter is applied to the Report, no need to change the report record source.

 

[Uncle Gizmo]

Further to the video on demonstrating how to apply a filter to a Report, you might find my blog here of interest, as it demonstrates an easy way of creating Filters:-

Easily Apply Filters - Nifty Access

Filtering - MS Access Forms This video should be of interest to anyone who wants to easily apply filters to an MS Access form in datasheet view or continuous form view. In this demonstration I show how you can apply filters in a Form presented in Continuous view and also in Datasheet view. When you

www.niftyaccess.com

 

[Gasman]

Hi Paul,
In this example the Filter is applied to the Report, no need to change the report record source.

Nice, another way of doing it.
I like the way you are prepared to show your mistakes as well. Well done!