Automating the e-mailing of a report based on a parameter query

[wishlish]

I am going to be sending out report cards to our vendors. The report cards will be generated using Access 97. I have a table of the vendor's ID and e-maill address. I created a query off of a table of all our records that selects the records that match the ID #, then created a report based off that query.
In psuedo-code, I want to do the following:
If (Table of vendors not empty) Then
Get the e-mail and ID
Run the Query
Create the report based off the query
DoCmd.SendObject (send the report to the e-mail address in rtf format).
When I do this, however, the parameter query box for the report pops up, asking for the ID.
How the heck do I run the query and create the report without the parameter query box popping up when I create the report? How can I pass the ID to the report?

Thanks!

 

[KenReay]

Hi

There are several ways to do this, but the easiest to explain is probably:

Have aform from which the Report is run.

On the form have a txtControl for Id, make in visible = No

Amennd you report query so that the parameter is the textbox on the form (in the query grid, the builder will guide you through this if you need it to)

Your pseudo code the becomes:

If (Table of vendors not empty) Then
for each row in vendors
Get the e-mail and ID
put id in txtId

Create the report based off the query
DoCmd.SendObject (send the report to the e-mail address in rtf format).
next vendor
Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[wishlish]

Would it be easier to dump the parameter from the query and filter this report in the doCmd.OpenReport command? If so, how do I write the filter code?

Thanks, Ken!

 

[KenReay]

HI
Yes as I said there is more that one to do this

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

in Wherecondition put "Id = " & Id assuming ID is a number field if not then "Id = '" & Id & "'"

You need to put the correct variable names in place of Id


Ken Reay
Freelance Developer
kenneth.reay@talk21.com

 

[wishlish]

Ok, got this to work, but now I have a new problem. When I loop this to do this a second time, Access crashes. Why?

I'm going to put this problem in a different topic. Thanks, Ken!