Hi,


I want to use a subroutine to print a subset of records from a table. I tried an option box using select case, as there is a field called DANum which contains:

DA1
DA1
DA1
DA1
DA2
DA2
..
..
..
DA8

etc. and there is a corresponding ID, called CompartmentText against each one. So each DA comprises a variable number of CompartmentText entries.

I tried filtering using db.OpenRecordset(Select * From/Where) which worked OK, and checked this using a msgbox displaying the tempcomp within it as below.

For example, the msgbox correctly identified all CompartmentText Id's associated with DA8, using the report in acPreview however, the reports do not print correctly and stLinkCriteria
appears to do nothing, the loop cycles through correctly, and msgbox confirms that, but the report does not preview using the stLinkcriteria, and it shows the same report every
iteration. I tried using acNormal in case it was a bug with preview, but same results (wrote to file insteadof the printer)

What I do not understand is that the routine works perfectly well on a very similar report, using a routine similar to that below, and that report even uses subreports in a similar
fashion. It cannot be filtering the underlying report query up correctly, as there is a field within the query used for sorting and grouping and displayed in the header, and this is
also not visible in the preview either.

Is there a simpler way to pull a subset of data from a master table, and then filter an existing report's underlying query to do an unattended print rather than print each individual
record ??






Private Sub multicomprint_Click()
Dim db As Database
Set db = CurrentDb()
Dim RS As DAO.Recordset
Dim stDocName As String
Dim tempcomp As Integer
Dim stLinkCriteria As String
stDocName = "CompartmentReport"


Set RS = db.OpenRecordset("CompTable")
Do While Not RS.EOF
tempcomp = RS![CompartmentText]
stLinkCriteria = "[CompartmentText]=" & "'tempcomp'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

MsgBox tempcomp


DoCmd.Close acReport, stDocName

RS.MoveNext
Loop
RS.Close

Exit_MySub:
Set RS = Nothing
Set db = Nothing
End Sub


--
--------------------------------- --- -- -
Posted with NewsLeecher v3.9 Final
Web @ http://www.newsleecher.com/?usenet
------------------- ----- ---- -- -

What version of Access?
What operating system?

Can you post the actual code you are using?

As a *GUESS* based on the limited information, you may be constructing the
filter string incorrectly - incorrectly reference to a field, wrong data type,
etc.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

bezz wrote: