Selecting the current record for a report

[hargy]

I am developing a Purchase Request system for my company.

Once the details of the request have been entered, then I wish an email to be sent to the first nominated authoriser is the authorisers list using

DoCmd.SendObject acSendReport, "rptPurchaseRequest", acFormatSNP, "1stauthoriser@company", , , "Purchase Request RAISED", "body text", , False

This will give them a summary of the request raised. This will help in requests being missed when the authoriser is busy, if a request is urgently required.

I would like the report however to just show the request just raised and NOT all records raised in the system. There is probably a really easy way to do this.

If its possible to put the request number in the SendObject command also, that would be really helpful

 

[JerichoJ]

You are so very close. Basically, you need to add only 1 (one) line of code!

You can tell the .sendobject method which report to send in two different ways.

The first way is to do like you have done. By explicitly naming the report that you wish to send.

The second is to leave the objectname argument BLANK. By doing this, .sendobject will send the currently active report to the user specified (read the online help for the .sendobject method, not the action).

So, what you want to do is go ahead and open your report in preview mode on the screen, then issue your .sendobject command, leaving the objectname argument blank.

You need to do it this way because .sendobject does not suppport giving any criteria to the report. However, DoCmd.OpenReport does allow you to specify criteria.

So, for our example we will say that you have a field on the form called IssueID that is a unique identifier (primary key) for your Issues table that is feeding this report. You would do something like the following:

DoCmd.OpenReport "rptPurchaseRequest", acViewPrevew, ,"[IssueID] = 315"

This will open your report to the screen and restrict it to just the Issue record where IssueID = 315.

Then your next line of code would be your exising .sendobject command, but exclude the name of the report "rptPurchaseRequest". For example:

DoCmd.SendObject acSendReport, , acFormatSNP, ....

Voila, the report on the screen is sent as desired

Actually, you would want one more line of code immediately after the .sendobject, namely:

DoCmd.Close, acReport, "rptPurchaseRequest"

 

[hargy]

Thats a really good process - and I understand your methods too.

My only question, is that its OK when you set the [IssueID] primary key to a value. How would you return a value of the current record. There should be a property that could be read and the value returned and used in the statement to read that record as a variable rather than a constant

 

[JerichoJ]

I am afraid that I don't quite understand your question.

"How would you return a value of the current record."

Return the value of the current record from what? Your form that launches the report? Or the report itself?

If you are talking about the form that launches the report, yes, you can set "[IssueID] = " to be equal to a field on your form. For example:

.... , "[IssueID] = " & Me!IssueID

This will pass the value of the IssueID field on the current record of your form.

This is what I really meant to imply in my original post, but I forgot to mention that you could substitute a field on the form for the hardcoded "315".

If this is not what you were referring to, please clarify and I will help.

 

[hargy]

JerichoJ

Everything works well now and I put the Request Number in the Subject which is smart too - so thankyou for helping me to learn - I used to be a BASIC programmer years ago and then onto Fortran 77 at Uni - its good to get back into it. VBA is a cool one to learn, I am finding