I have a report that requires 3 queries to generate the data required. I then have created a VBA routine to automate the generation of about 70 short reports, each one detailing members of a group. These are then emailed to the group leader for updating. This has worked well up until now, when I get an error 3079 on trying to evaluate an email field. Apparently the SQL parser cannot work out where to find the correct email address.
When I run each of the queries in turn they all work.
Q1 generates data for Q2 which in turn generates data for Q3 which is then the source for the report.
When I run the report it works.
When I run the VBA code I get the error message 3079. The VBA code detailed below with the offending line in red.
In debug mode, the value of zDocname is 'GroupMembership' and as I have mentioned earlier, when I run this report directly it works, and the VBA code has run successfully before.
Anyone have any ideas?
Dim dbName As Database
Dim rst As Recordset
Dim zWhere As String
Dim zDocname As String
Dim zGroupChoice
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Dim zOutputFileName
Dim zRootDirectory
Set dbName = CurrentDb()
Set rst = dbName.OpenRecordset("GroupLeaderEmail")
zDocname = "GroupMembership"
zRootDirectory = DLookup("[RootFileLocation]", "[FileLocations]", "[RecordNumber] = 1")
rst.MoveFirst
Do While Not rst.EOF
zWhere = "[GroupCode] = " & Chr(34) & (rst![GroupCode]) & Chr(34)
zOutputFileName = zRootDirectory & zDocname & rst![GroupCode] & ".pdf"
DoCmd.OpenReport zDocname, acViewPreview, , zWhere, acHidden
DoCmd.OutputTo acReport, zDocname, acFormatPDF, zOutputFileName, False
DoCmd.Close acReport, zDocname
rst.MoveNext '*** Move to Next Record ***
Loop
Set rst = Nothing '*** Close RecordSet ***
End Sub
When I run each of the queries in turn they all work.
Q1 generates data for Q2 which in turn generates data for Q3 which is then the source for the report.
When I run the report it works.
When I run the VBA code I get the error message 3079. The VBA code detailed below with the offending line in red.
In debug mode, the value of zDocname is 'GroupMembership' and as I have mentioned earlier, when I run this report directly it works, and the VBA code has run successfully before.
Anyone have any ideas?
Dim dbName As Database
Dim rst As Recordset
Dim zWhere As String
Dim zDocname As String
Dim zGroupChoice
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Dim zOutputFileName
Dim zRootDirectory
Set dbName = CurrentDb()
Set rst = dbName.OpenRecordset("GroupLeaderEmail")
zDocname = "GroupMembership"
zRootDirectory = DLookup("[RootFileLocation]", "[FileLocations]", "[RecordNumber] = 1")
rst.MoveFirst
Do While Not rst.EOF
zWhere = "[GroupCode] = " & Chr(34) & (rst![GroupCode]) & Chr(34)
zOutputFileName = zRootDirectory & zDocname & rst![GroupCode] & ".pdf"
DoCmd.OpenReport zDocname, acViewPreview, , zWhere, acHidden
DoCmd.OutputTo acReport, zDocname, acFormatPDF, zOutputFileName, False
DoCmd.Close acReport, zDocname
rst.MoveNext '*** Move to Next Record ***
Loop
Set rst = Nothing '*** Close RecordSet ***
End Sub
