Exporting multiple files from single Query

[kcn]

Hi,

I'm trying to set up a "single click" solution for exporting multiple excel files from a single query.

The query is based on tblProject and I'm using a field in a lookup table - tlkpProgramOfficer - as a way to pass criteria to the query to create a separate file that exports for each officer (basically listing all their projects).

I'm running into a bunch of problems with the code below.

1. I don't know how to trap for no records for a Program Officer (i.e. not to create a file if there are no records).

2.I'm getting "object variable or with block variable" not set for the sName reference. (But it worked *before* I put in the if statment).

3. Although the script runs (if I option out above mistakes) it prompts me with a parameter for each officer instead of automatically passing the officer's name. "Enter parameter value" (Displays the Name) and if i want it to work i have to type in the name.

Any help will be much appreciated!

Thanks,
kerry
*************************************************************************************

Public Function MyExport()
Dim db As DAO.Database
Dim rs As DAO.Recordset
'Dim strSQL As String
Set db = CurrentDb()
Dim strMyQry As String
Dim sName As String
sName = rs!LName & Month(Date) & Year(Date)
Set rs = db.OpenRecordset("tlkpProgramOfficer")



DoCmd.SetWarnings False

rs.MoveFirst ' go to first record

Do Until rs.EOF
'look for projects for this program officer and put them into temporary reports table

DoCmd.RunSQL ("INSERT INTO zttReports SELECT * FROM qryQuarterlyExportProject WHERE ProgramOfficer = " & rs!LName)

'if table has records, proceed to next step, otherwise go to next record.

If zttReports Is Not Empty Then


' now export that table as an Excel file with unique name

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "zttReports", "c:\DataProjects" & sName & ".xls"

' delete records in table
With DoCmd
.SetWarnings False
.RunSQL "DELETE * FROM zttReports"
.SetWarnings True
End With

Else

End If

rs.MoveNext 'move to the next record



Loop


End Function

 

[PHV]

sName = rs!LName & Month(Date) & Year(Date)
At this point rs isn't open
DoCmd.RunSQL ("INSERT INTO zttReports SELECT * FROM qryQuarterlyExportProject WHERE ProgramOfficer = " & rs!LName)
Try to replace it with this:
DoCmd.RunSQL "INSERT INTO zttReports SELECT * FROM qryQuarterlyExportProject WHERE ProgramOfficer = '" & rs!LName & "'"
If zttReports Is Not Empty Then
Try to replace it with this:
If NZ(DCount("*", "zttReports"), 0) > 0 Then

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884