OutputTo command renders recordset invalid

[jhaganjr]

Hi,

I'm generating a vacation report for each of my employees to be e-mailed to them in house. The code is below.

The recordset is rendered invalid by the OutputTo command. (I originally thought it was the call to the e-mail function, but it's not.)

Becuase the report is different for each record in the recordset I need the OutputTo inside the loop to generate a unique report for each record ... OR I need another method of generating the report for e-mail delivery.

Does anybody have any ideas?? I've tried using DoEvents and moving the MoveNext command above the DoCmd line. It doesn't matter. Once the OutputTo is executed, the next line that references the recordset returns the invalid recordset error.

Thanks!
Joe

Private Sub cmdEmailAllRpts_Click()

    'To send a vacation update report by email to all employees
    'MsgBox "This code doesn't work yet. Call out to Outlook renders recordset invalid."
    'Exit Sub
    
    Dim MyDB As Database, MyRecs As Recordset
    Dim strSQL, strSubject, strMessage, strAlias As String
    
    If IsNull(Me.txtBalanceDate) Then
        MsgBox "You must choose a balance date for the end of the report."
        Me.txtBalanceDate.SetFocus
        Exit Sub
    End If
    
    strSubject = "Vacation Balance Update Report"
    strMessage = "Please review the attached report and report any inaccuracies immediately."
    
    strSQL = "SELECT EmployeeID, ExchangeAlias FROM Employees"
    strSQL = strSQL & " WHERE Current = True AND MoVacHrsAccrue > 0"
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set MyRecs = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
    MyRecs.MoveFirst
    
    Do Until MyRecs.EOF
        strAlias = MyRecs![ExchangeAlias]
        Me.cboEmp = MyRecs![EmployeeID]
        DoCmd.OutputTo acOutputReport, "rptVacationHours", acFormatRTF, "c:\VacationRpt.htm", False
        fctnOutlook , strAlias, , , strSubject, strMessage, , , False, "c:\VacationRpt.htm"
        MyRecs.MoveNext
    Loop
            
    MyRecs.Close
    MyDB.Close
    
End Sub

 

[BillPower]

Hi jhaganjr, Your code works, obviously had to REM fctnOutlook.

1. Is your control cboEmp unbound and updateable.

2. Otherwise, can you post the code for your Function fctnOutlook.

 

[mp9]

Bizarrely, I think I've just posted a solution to this in another (offsite) forum at

http://www22.brinkster.com/accessory/forum/topic.asp?TOPIC_ID=16

http://www22.brinkster.com/accessory/

 

[jhaganjr]

To billpower,

cboEmp is bound and updateable. The report uses it and another field on the form, called txtBalanceDate, to query the correct records for the report.

You can find the Outlook function at

http://www22.brinkster.com/accessory/modules/005.shtml,

but it has nothing to do with the problem.

If I remark out the OutputTo command the loop completes with no problems - other than the employees don't get the correct vacation report.

Joe

 

[jhaganjr]

To mp9,

That is me at brinkster, too, and I tried your fix. But the same problem still occurs.

Regardless of how the report gets created, the OutputTo command renders the recordset invalid.

Joe

 

[mp9]

Hi Joe,

There's yet another solution suggestion at

http://www22.brinkster.com/accessory/forum/topic.asp?TOPIC_ID=16

now, if it helps.

http://www22.brinkster.com/accessory/

 

[BillPower]

I've posted the DB that I tested your code in at

http://www.wpower.fsbusiness.co.uk/

. It's called jhaganjr.zip

Out of interest could you see if the OutputTo works ok with you, I've used your code. As I said it runs perfectly here on both 97 and 2000

Please let me know,

Regards Bill

 

[jhaganjr]

billpower and mp9,

I started from scratch with very detailed testing changing only one component at a time. Here's what I found ...

I am using the report, rptVacationHours, in another click event where I send the report to the selected employee on the form - one at a time as needed.

That continued to work perfectly as the problems with the loop were investigated.

I copied the rptVacationHours report and renamed it to rptTest. I used rptTest in the loop and it works perfectly. I thought rptVacationHours got corrupted or something, but it continues to work perfectly when sent only one at a time from the other click event.

There were no other changes other than making a copy of the report, renaming it and using the new report in the loop. Both reports even take their data from the same query!

So, I have no idea what kind of internal setting Access is using that's causing a conflict, but my loop works now. The solution was to have one report for the event that sends only one e-mail and report, and have a second identical report with a different name used in the loop that sends the report to all employees.

I was thinking about importing all the database objects into a new database and see if that is still needed. Maybe there's some database corruption that's causing this. I have compacted the database several times.

Go figure ... Thanks much for your help.
Joe

 

[jhaganjr]

Final update,

It appears the real problem was some underlying database corruption. Once the solution was rebuilt in a fresh database I had no problems at all. Funny part is the rest of the database ran with no problems.

I've had problems before with database corruption in Access, where repair tools make no difference (at least the ones I know about).

Lesson learned (again) ... when problems come up that absolutely SHOULD work, start a fresh database and duplicate the problem. At least then you'll be able to eliminate database corruption as a potential cause.

Joe