Filter in report as in form

[johnbrugman]

I want help about the following: I have a db with a main form to which I can set filters by the normal access way. When I push the print button I get a print of the form of all selected records. Is there a way to apply this form-filter to a report which I want to open/print from the mainform with a certain selection of records. I search for things like ApplyActualFormFilter.

Can anyone help me out?

John.

 

[RickSpr]

Make sure your report has the same Record Source as the form. Then all you have to do is specify Me.Filter (the form's Filter property) as the where condition argument on your OpenReport. For example:
DoCmd.OpenReport "My Report", , , Me.Filter

About the only time this wouldn't work is if you have set the form's Filter property in code using a shortcut reference to one of its controls. For instance, if you have an unbound combo box named "cboCategory" on the form that you use to select a category of records to be displayed, you might have VBA code that sets the form filter to:
[Category ID] = [cboCategory]
This won't work for the report, because it won't have any idea what "cboCategory" is. To fix this, you'd have to use the fully qualified name in the filter, so it would be:
[Category ID] = Forms![My Form]![cboCategory] Rick Sprague

 

[FEDSEC]

In the Design veiw of your form , open Properties. Go to the Date tab, and you find fields called "Filter" and "Filter On". Putyour filter here and turn the filter on.

The Filter should look something like this;

((Not qryYourQuery.yourfield=0))

( I dont know what you want to filter)

 

[johnbrugman]

Rick,

I think your suggestion is very helpful. However the record source of the report is different from the main form. Main form frmMain is based on qryMain and the report rptReport is based on qryReport. qryReport on his case is also based on qryMain (and some others). Any further suggestions?

Regards,

John.

 

[RickSpr]

I was being too restrictive. The report doesn't actually have to have an identical record source, it only has to have matching fields for each field that might appear in the form's filter. If qryReport is based on qryMain, it should be easy to make sure qryReport outputs all the same fields.

However, the from's Filter property may have "qryMain" appearing in it one or more times. You'll have to scan it and replace that with "qryReport", before you use it in the OpenReport. Use InStr() to find "qryMain", and then use Left$() and Mid$() to extract the parts on either side of it, then concatenate "qryReport" between them. Repeat this until you can't find "qryMain" any more.

    Dim filter As String, i as Integer

    filter = Me.Filter
    Do
        i = InStr(filter, "qryMain")
        If i = 0 Then Exit Do
        filter = Left$(filter, i - 1) & "qryReport" _
            & Mid$(filter, i + Len("qryMain"))
    Loop
    DoCmd.OpenReport "My Report", , , filter
[code]
     Rick Sprague

 

[johnbrugman]

Another way I want to explore: can I use the For Each Next construction to print the report for each Main-Id in the current filter. This must result in something like:

Dim Records As ?
Dim FilteredRecords As ? (Recordset?)

For Each Record In FilteredRecords
DoCmd.OpenReport "rpt", , "Records.Key.Value=[qryReport]![Key]"
Next

I want the exact syntax, if possible, I was struggling with the Recordsset.

Greetings, John.

 

[RickSpr]

The For Each statement iterates through the items in a Collection. What you want to do is iterate through the records in a Recordset, which is not a Collection class, so no, you can't use For Each.

But it's quite easy to iterate through a Recordset. I'm not familiar with ADO recordsets, so I'll assume you're using DAO (you'll need to add the DAO 3.6 Object Library to your References, if it's not already there). Here's a sample.

    Dim rst As DAO.Recordset
    
    Set rst = Me.RecordsetClone
    rst.MoveFirst
    Do Until rst.EOF
        DoCmd.OpenReport "rpt", , "[KeyField] = " & rst![KeyField]
        ' If KeyField is type Text, change the above to
        DoCmd.OpenReport "rpt", , "[KeyField] = '" & rst![KeyField] & "'"
        rst.MoveNext
    Loop
    Set rst = Nothing

Rick Sprague

 

[johnbrugman]

Thank you, this is very helpful. The only thing left is that I want to filter my recordset to the current filter. In the Help I read that exactly that property is ignored.

So the construction with the recordset works, only I get all records in the recordsource and I want the filtered records only.

Any suggestion?

 

[RickSpr]

"In the Help I read that exactly that property is ignored." - Where did you read that? I don't know anything about the Filter property being ignored, either in Forms or in Reports. However, you do have to set the FilterOn property to True if you're setting Filter in code.

If you need more help with this, give me some details about the Record Source properties and how you're setting the Filter. I'm kind of confused, because it seems to me we've talked about two different ways of using the where condition argument when opening the report. Are you talking about combining them? Rick Sprague

 

[johnbrugman]

I think I want to combine 2 things. I implemented your code but instead of printing the filtered records in frmMelding all records of the Recordset are printed. So the basic question is how to assigne a filtered recordset to rst. In the help I search for this and it says that on a recordset properties like Filter are not applied. I hope you are a little bit less confused.

John.

The code:

Dim stDocName As String
Dim rst As DAO.Recordset

stDocName = "rptMeldinghistoriePerMelding"
Set rst = Application.Forms.frmMelding.RecordsetClone

rst.MoveFirst
Do Until rst.EOF
DoCmd.OpenReport stDocName, acPreview, "[qryMeldinghistoriePerMelding]![MRS-nr] = " & rst![Meldingnummer]
' If KeyField is type Text, change the above to
'DoCmd.OpenReport "rpt", , "[KeyField] = '" & rst![KeyField] & "'"
rst.MoveNext
Loop

 

[RickSpr]

Do you really want to print a separate report for each record displayed on frmMelding? That's what this code would do, but I don't think that's what you really want. I think what you really want is to print ALL the records shown on frmMelding in a single report, right? We got distracted into talking about using For Each with a recordset. I assumed you were asking a separate question, but I can see now that you may have thought that was the solution to the original problem.

Does the report's query contain all the fields that the form contains? If so, you can use my original suggestion:
DoCmd.OpenForm stDocName, , , Me.Filter
If not, would you be willing to add the missing fields to qryMeldinghistoriePerMelding? You only need to add them to the query, not to the report.
Rick Sprague