Recordset is not filtering Report- version 2

[johnnywinter via AccessMonster.com]

Thought I would start a new thread since where the forum has pointed me is
different.

I want to filter a Report by using a Recordset to filter the table
"CurrMoCollectionsByRep" on which the Report is based.

All I want to do is to print a separate Report for each value of [REP] in the
table "REPMASTER" (the Recordset.

My code loops through the Recordset, but does not filter out any records, so
it ends up printing an unfiltered report for each record in the recordset.

Help please. I am having a problem getting this? A senior moment?



*****************************************************************************************
Private Sub Command2_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("REPMASTER")

With rst
Do Until .EOF

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rst!REP =
CurrMoCollectionsByRep!REP =True"

doCmd.Close acReport, "Cash Collections By Rep", acSaveNo

..MoveNext
Loop
..Close
End With

End Sub

 

[pietlinden]

Thought I would start a new thread since where  the forum has pointed me is

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rst!REP =
CurrMoCollectionsByRep!REP =True"

the filter is not set up properly. It should be of the form
"[RepresentativeID] = rsCollections.Fields("RepID")

or similar.

 

[johnnywinter via AccessMonster.com]

PietLinden, Here is the full Code you previously gave me.

I have same problem with that- it does not filter either.

I am running this code from a command button on a form. The source of the
form is the table used in this report.

Sorry if I appear dense, but it appears simple but I cannot get it to work.


Thanks,

JW


*************************************************************
Private Sub Command0_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)
Do Until rsRep.EOF

doCmd.OpenReport "Curr Mo Collections By Rep", , "[Rep]=' " & rsRep.Fields
("Rep") & " ' "
'DoCmd.PrintOut...
doCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub
***********************************************************************************************

Thought I would start a new thread since where  the forum has pointed me is

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rst!REP =
CurrMoCollectionsByRep!REP =True"

the filter is not set up properly. It should be of the form
"[RepresentativeID] = rsCollections.Fields("RepID")

or similar.

 

[JerryData]

Hi!
Try this:
"[Rep]= '" & rsRep.Fields!Rep & "' "

--
-Jerry
JR Data Inc.

PietLinden, Here is the full Code you previously gave me.

I have same problem with that- it does not filter either.

I am running this code from a command button on a form. The source of the
form is the table used in this report.

Sorry if I appear dense, but it appears simple but I cannot get it to work.


Thanks,

JW


*************************************************************
Private Sub Command0_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)
Do Until rsRep.EOF

doCmd.OpenReport "Curr Mo Collections By Rep", , "[Rep]=' " & rsRep.Fields
("Rep") & " ' "
'DoCmd.PrintOut...
doCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub
***********************************************************************************************

Thought I would start a new thread since where the forum has pointed me is

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, "rst!REP =
CurrMoCollectionsByRep!REP =True"

the filter is not set up properly. It should be of the form
"[RepresentativeID] = rsCollections.Fields("RepID")

or similar.

 

[johnnywinter via AccessMonster.com]

Jerry: same result with your code- prints report with all records and prints
report for each record in recordset (38 records in recordset, so 38 reprots
with all records)

Addtional information if needed:
table"REPMASTER": field [REP] is primary key
table"CurrMoCollectionsByRep": [REP] is not primary key

Form:"By Rep":
Record Source: table"CurrMoCollectionsByRep":
Filter:

Hi!
Try this:
"[Rep]= '" & rsRep.Fields!Rep & "' "

PietLinden, Here is the full Code you previously gave me.

[quoted text clipped - 41 lines]

 

[johnnywinter via AccessMonster.com]

Jerry:
same result with your code- prints report with all records and prints
report for each record in recordset (38 records in recordset, so 38 reports
with all records).


Addtional information if needed: all names are unique
table"REPMASTER": field [REP] is primary key
table"CurrMoCollectionsByRep": [REP] is not primary key


Form:"By Rep":
Record Source: table"CurrMoCollectionsByRep":
Filter:
Order By:
Allow Filters: Yes

Report: "Curr Mo Collections By Rep"
Record source: "CurrMoCollectionsByRep"
Filter:
Filter ON: Yes
Order By:
Order On: Yes


Any thoughts?

Hi!
Try this:

[quoted text clipped - 5 lines]

 

[johnnywinter via AccessMonster.com]

I got it working with following code.

Any idea why I had to define Me.Filter and use it and where in the OpenReport
line ?

What do I not understand?

Thanks to everyone for their help.
***************************************************************************************************
Private Sub Command0_Click()

Dim rsRep As DAO.Recordset
Set rsRep = DBEngine(0)(0).OpenRecordset("REPMASTER", dbOpenTable,
dbForwardOnly)

Me.Filter = "[Rep]= '" & rsRep.Fields!REP & "' "
Me.FilterOn = True


Do Until rsRep.EOF

doCmd.OpenReport "Curr Mo Collections By Rep", acViewNormal, Me.Filter, "
[Rep]= '" & rsRep.Fields!REP & "' "
doCmd.Close acReport, "Curr Mo Collections By Rep", acSaveNo
rsRep.MoveNext
Loop

rsRep.Close
Set rsRep = Nothing

End Sub