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
------------------- ----- ---- -- -

Is TempComp an integer or is it a string value?
Is CompartmentText a string value?

Since you are not getting an error when you assign compartmentText to tempcomp
I am going to assume that they are both integers.

In which case your stLinkCriteria line should read
stLinkCriteria = "CompartmentText=" & TempComp

IF CompartmentText is a string then
stLinkCriteria = "CompartmentText=" & TempComp & "'"

In both cases you must concatenate the value of TempComp into your expression
and not the name of the control. IF you pass in the name of the control, your
expression becomes
CompartmentText = 'TempComp'
and I don't think you have any CompartmentText value that is tempcomp.

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

Slight correction to John's post: he missed a quote.

Exagerated for clarity, that's


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)



"John Spencer" <JSPENCER (AT) Hilltop (DOT) umbc> wrote

Thanks for the backup and correction.

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

Douglas J. Steele wrote:

That worked, it was a syntax issue, however, added "'" between the & as that was missed on the post. Works fine now..


stLinkCriteria = "[CompartmentText]=" & "'" & tempcomp & "'"

Thanks




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

Thanks for help guys, I had sussed the missing quotes in the middle, and the routine works fine now. I replied last night but my news client (Newsleecher) is very hit and miss when
it comes to posting. I shall try again

Regards, J


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

Thanks for help guys, I had sussed the missing quotes in the middle, and the routine works fine now. I replied last night but my news client (Newsleecher) is very hit and miss when
it comes to posting. I shall try again

Regards, J


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

Damn Newsleecher posting success lottery...

:-)



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