Access 2003 - Report not obtaining record ID when using SendObject method

[callen82]

Hi all, hope you can help with this as I am a bit of a newbie when it comes to Access and VBA...

I am developing a database system at work that is used to record audits of work quality, and we want to be able to e-mail an Access report to workers where quality issues have been identified - nothing special, just a single record report.

The auditor selects an audit record from a listbox on the main form - the first column of which is the unique ID of the audit. Below the list box I have put three command buttons which allow the report to be viewed, printed or e-mailed, the first two of which are working very nicely thanks to the 'OpenArgs' attribute in DoCmd.OpenObject. The 'proforma' report is also called from two other forms which are used for creating the audit records in the back-end tables, and when a 'fail' record is added a module is called which saves a Snapshot Format copy of the report to a network drive. This too works fine.

I have a problem however when trying to generate the report when using DoCmd.SendObject - no matter what method I use to input it, the report simply will not pick up the unique audit record ID to complete the SQL command which populates the fields in the report. I have written a new function in a module for generating the e-mail, and the e-mail itself is generating fine, however the attachment generated by SendObject is just the report template.

On the main form the code behind the command button which attempts to send the e-mail is very simple:

run sendproforma(Me.LstHistory.Column(0))

The argument for getting the record ID into this function has been coded as follows:

Public Function sendproforma(auditID As String)

The first lines of the Reports 'On Activate' routine deal with picking up the audit record ID from the various inputs mentioned before. I am going from memory on this a bit, but is essentially as follows:

Dim strvariable, SQL As String
 
If forms!frmresults1.IsLoaded Then
   strvariable = forms!frmresults1.UniqueID
ElseIf forms!frmresults1view.IsLoaded Then
   strvariable = forms!frmresults1view.NewUniqueID
ElseIf Not IsNull(Me.OpenArgs) Then
   strvariable = Me.OpenArgs
Else
   strvariable = auditID
End If
 
SQL = "SELECT * FROM qryauditresults WHERE uniqueref = '" & strvariable & "'"

The last part of the group of IF's is the bit I'm trying to get to work when sending the report via e-mail. I have tried populating this from the listbox on the main form, a hidden text field which contains the same value as the selected record in the listbox in both a new form as well as the original main form, all to no avail thus far which is very frustrating to say the least! :banghead:

We are running Access 2003 (SP3) on Windows XP currently. I will try and get online here tomorrow at work and post up all the relevant code tomorrow but in the meantime maybe what I have posted here will be sufficient for one of you boffins to be able to spot where I am running into trouble

 

[AccessMSSQL]

I would remove your code from the OnActivate event of the report. Put it in the button you use to run the report and then instead of relying on passing the where clause to the SQL Statement - create another query called qryauditresultsReport with the following Syntax: Select * from qryAuditResults.

Then, in the button that runs your report, simply run your code that gets the auditID and then dynamically modify the reports SELECT Query as follows:

Dim qry as dao.querydef
Dim SQL as string

'do your get auditID routine

sql = "select * from qryAuditResults WHERE AuditID = " & auditID
set qry = currentdb.Querydefs("qryAudtResultsReport")
qry.SQL = sql


What happens is when you preview your report it runs fine, but when you then SendObject to email it, the report will try to create another instance of the report and this version will not have your variables populated.

 

[callen82]

Thanks for the suggestion AccessMSSQL. I guess as the current version of the report is working fine in the other instances, it's worth making a copy of my existing Report as well just for the e-mail routine?

Also just for clarity would it matter if the 'querydef' is included in the VB Module I've created rather than the button's onClick event which runs it?

 

[AccessMSSQL]

However you are running the report - whether it's from click event or module, just make sure you modify the querydef before each report print for each different auditID. It doesn't matter where the code resides. Hope it works. And, YES, I would keep a backup copy of the existing report.