Emailing Report Question

[robbie9]

How do I change the file name of a report I am sending.. it defaults to the Query name that the report is based on.. I would like to change it to either a default name(Robs Report.snp) or have it use a filed name from the report(jobname.snp)..

Here is the code I am using...
Dim stDocName As String
Dim strFilter As String

stDocName = "2004 Quote Corian"

strFilter = "[Quote_Number] = Forms![2004 Quote Form Sqft price]![Quote_Number]"
DoCmd.OpenReport stDocName, acPreview, , strFilter
DoCmd.SendObject acReport, stDocName, acFormatSNP, , , , ([Homeowner_name] + " Corian Quote"), "Thank you for the opportunity to quote you on your upcoming Corian job."


Any Ideas?? or should I pound salt??

Thanks...

 

[Fizzio]

Wouldn't pounding salt sting a little?

Here are a few ways you could skin this one.

1. Hard code the report name as you already have here
stDocName = "2004 Quote Corian"

2. Store a value in a field in a separate table and use a Dlookup to extract that Data

3. Base the name on a field but that is a little more tricky.

Which you would prefer will dictate your path.

 

[robbie9]

Which ever is easiest...

 

[Fizzio]

1. is easiest but less flexible. If you ever want to change the name, it means dabbling with the code.

2. A little more tricky but more flexible as changing a field in a table will change the name for you (or even more flash - change the name according to who is logged into the Db)

3. A bit more tricky again but again leaves you with a little less flexibility.

My choice would be 2 (in whatever fashion). If you are not familiar with DLookup, look in access help - basics are eg in your code example

stDocName = DLookup("[FieldName]","TableName",Where Condition {eg "[UserName] = " & LoginName) 'where LoginName can be a global variable, a field on a hidden form etc.

 

[robbie9]

No..the report I am opening is fine... its the File name of the send object I want to change.. stDocName is correct.. I don't need to change that... Right now it defaults to the Query the report is base on..."2004 query quote.snp" is the file name I am getting... I want to change that to something..