Email report SNP view of current form record

[Lee83]

Hi,

I have A form that you fill in and when you hit the send button it sends a SNP view of the related report to an email. This works fine but the report doesn't send the current record of what has just been entered into the form. How can I tell it to send a report of this current record?

Any help very very greatly appreciated.. I'll spend weeks trying to fix this otherwise...

Cheers peoples

 

[Kempes]

One way of doing this is to do the following.

The query that your report is based on will have a criteria option at the bottom of each field.
You will need to set this to point to the identifier of your open form so it knows which record to export.

In an event based option of your form (depending on where you want to do it from), put in code to save the record to the table first, then export the report (which the query will now know the record to export for the report), and this will solve your problem.

I know this is a bit high level but it should point you in the right direction.

Let me know if you need a more in depth explanation.

Kempes

 

[Lee83]

Att: Kempes - Code for Setting Criteria

Thank you

This does point me in the right direction however, I am not sure exactly how to code it so that it filters only the current record in the Services Request table are you able to help me with the code for this?

This is what I have;

The Report “Service Request” queries the table “Service Request” where the data is stored from the entries in the form.

When you double click on the report to open it, it now asks for you to enter parameter values.

I am under the impression that if I enter the correct code in the criteria to point to the open form/current record that this will get rid of the parameter query? As it will then know where to get the information from is this correct?

Many thanks

 

[Kempes]

This is correct.

Within the query that your report is based on, find the unique identifier field which should also be present in the form (doesn't need to be visible in the form) and put in the following. (Adapt to suit.)

[Forms]![Formname]![Fieldname]

This should reference the value displayed on the active form for the query, hence reporting on that record alone.

When vb coding in your form, make sure you put in....

DoCmd.RunCommand acCmdSaveRecord

....before prompting for the report to be sent out.

Hope this helps.

Kempes

 

[sudhirhinduja]

Dummy report

Hi,

I have A form that you fill in and when you hit the send button it sends a SNP view of the related report to an email. This works fine but the report doesn't send the current record of what has just been entered into the form. How can I tell it to send a report of this current record?

Any help very very greatly appreciated.. I'll spend weeks trying to fix this otherwise...

Cheers peoples

Hi Lee83...
I just implemented something really simple.
See if it works for you.
Add a field Dummy which is a Yes/No field in the underlying table.
You know the underlying query of the report. Save it as a new query called qryDumyReport.
In the old as well as new query add the new Dummy field.
Now base the report on the new qryDummyReport which has a true condition for the Dummy check mark.
Open the form in design view. On the OnCurrent event type
Me.Dummy=True
On the OnClose as well as AfterUpdate events type
Me.Dummy=False
Then add a new button on the form. The command button wizard will provide you with report operations. Choose mail report from in there.
Boom.
Next you know it - with the current form open on screen you can email the current record.
I also struggled a lot with it
HTH.
Suds.

 

[Lee83]

Email Report

Thank you so much for all your help It is greatly appreciated you saved me a lot of headaches!

This all worked and the report was emailing with the current record that had been entered into the form however, after I updated a table with some information, and split the back end, now when you hit send on the form to email the report it is coming up with this message;

“The database engine could not lock table ‘Service Request’ because it is already in use by another person or process”

Even though I don’t actually have the table open what would be the reason behind it coming up with this message?

any ideas on how to fix this?..

Many Many Thanks