Had most of this done but company showed up. Might as well post it anyway. Assumes dates will come from a form and the names are based on employee numbers, not names, and come from a query or sql statement. acNormal should send the report directly to the printer. The window mode could open the report hidden, but it's not used here. I would also put in about a 2 second pause between print calls, otherwise it could be too fast for the printer spooler.
Code:
Sub PrintReports(dteStart As Date, dteEnd As Date)
Dim rs As DAO.Recordset
Dim strWhere As String
strWhere1 = "[fieldNameForDate] BETWEEN " & dteStart & " AND " & dteEnd
Set rs = CurrentDb.OpenRecordset "sql here to get a list of recipients by employee number"
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do While Not rs.EOF
DoCmd.OpenReport "reportName", acNormal,, "[RecipientFieldName] = " & rs.EmplNo & " And " & strWhere
Pause(2)
DoCmd.Close acReport "reportName"
rs.MoveNext
Loop
End If
End Sub
Next part in a standard module so that it can be used anywhere in the db
Code:
Sub Pause(intSec As Integer)
Dim varTime As Variant
varTime = Timer
Do Until Timer = varTimer + intSec
Loop
End Sub
Do While Not (rs.BOF And rs.EOF)
Never have tried that syntax. Would have thought that rs would never be both BOF and EOF after commencing a loop that actually contains records. It will be when it gets to EOF, but I don't see it being an AND situation when it gets there. I've been wrong before...