Report not pulling up the current record...instead it pulls all 2

[julius1]

report problem is that I have a form that incorporates several fields including fields from other forms, the probelm is, is that when I try to print or email the form off I get all of the records in the database instead of just the current form that is populated. I have created the report based on the field from the form itself so it can populate all of the fields the same. The distinct items would be PON, ASR, and ordersspecialsid key field. I am just trying to get the current order to go to the report. Can I set up a query to pull each record individually based on the current record in the form? Or some way to set the report to pull the current record only? If there is a way to do this please let me know and where I need to add this. I have wondered about trying to apply a filter but I do not know where I would apply that.

 

[PaulBricker]

Are PON, ASR and ordersspecialsid each a key field or do we need all three to make it a unique record. If you only need one then try this (I'm assuming they are all text datatype)

Dim strVal as String
strVal = Forms!FormName!PON
DoCmd.OpenReport "ReportName",acViewPreview,,"[PON]= '" & strVal & "'"

If you need all of them to identify your record it would look like this.

Dim strPON as String
Dim strASR as String
Dim strordersspecialsid as String

DocCmd.OpenReport "ReportName",acViewPreview,,"[Pon] = '" & strPON & '" And [ASR] = '" & strASR & '" And [ordersspecialsid] = '" & strordersspecialsid & "'"

Right now I just have it opening up to preview, but if it works, you can change the acViewPreview to acViewNormal which will print the Report. Post back if you have any problems.

Paul

 

[julius1]

Paul, thanks very much for you time!!

 

[c0i0nes]

The report should get its data from a query that selects a single set of data using the parameter which is the key field on the current record on the form.
With the form open on a valid record, design the query that selects the data you want to appear in the report, using as the criterion the key field(s) for that form, like in your case: ordersspecialsid key field - use the query builder for help in getting this right. Save the query. When you are happy that all the data is there, design the report based on this query as the record source. Finally after saving the report, add a button to the form that runs the report using the wizard. No coding needed!

 

[julius1]

Thanks a bunch guys for all the help. I can get th report the way I want, now I am failing on modifying the code to reflect the report to go to email. I keep getting a compile error using the code set from above. I am not sure as to what i should change to get it to go to the email process withe the current record and report.

 

[PaulBricker]

After the OpenReport line use the
DoCmd.SendObject acSendReport, "ReportName", acFormatRTF, "whoever@attbi.com", , , , , False

This will send the current filtered report to whomever is listed in the To and CC arguments. Then you can add
DoCmd.Close acReport, "ReportName"

to close the report.

Paul

 

[julius1]

Can I omit the last part of the command to allow the user to choose who to send the report to?
The rep will need to send the report to one group for updates then add the updated remarks and resend to another department.

 

[julius1]

Cool I got it to work. I removed the email address and added stDocName. It worked great! Thanks Paul!!!!

 

[PaulBricker]

You can leave out the To argument and it will open the send box in Outlook Express and you can fill in that information. Also, if you leave off the False argument at the end or put in True, the email is opened so you can edit anything you want.

Paul