Printing Report Based on Filtered Form Data

[businessman]

I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!

 

[Sergeant]

Here is what the command button wizard writes for 'preview report', notice the part I added in red...

Private Sub Command4_Click()
On Error GoTo Err_Command4_Click

    Dim stDocName As String

    stDocName = "Report2"
    DoCmd.OpenReport stDocName, acPreview[COLOR="Red"], , Me.Filter[/COLOR]

Exit_Command4_Click:
    Exit Sub

Err_Command4_Click:
    MsgBox Err.Description
    Resume Exit_Command4_Click
    
End Sub

That's the WhereCondition argument.

 

[businessman]

that didn't work. it still prints all the records.

 

[Sergeant]

It should work if you are applying a native Access filter to your form. Looking back at my suggestion, though, you should check the forms 'FilterOn' property before applying the filter, as when you remove a filter, it just sets the FilterOn to false.

 

[businessman]

when i do your first suggestion, it keeps asking me for a parameter to a field in a query and i don't have anything sourced to that query, how do i get rid of that. and what am i suppose to do with the 'FilterOn' property? I don't understand how that works

 

[Sergeant]

OK, here's what you need to do...
- Open the report.
- Filter the report.

How do we do that?
Open Report...

DoCmd.OpenReport "MyReportName", acPreview

Filter the report...
(This assumes that the filtered form field exists in the report, by the same name.)

If Me.FilterOn Then
  With Reports![MyReportName]
    .Filter = Me.Filter  'Use the filter from the calling form. 
    .FilterOn = True
  End With
End If

Or, use the 'where condition' argument of the OpenReport method all in one go...as described above.

 

[businessman]

Is this what you mean or am I still not getting this?

Private Sub ReportOpen_Click()

DoCmd.OpenReport "Check Filter", acPreview

If Me.FilterOn Then
With Reports![Check Filter]
.Filter = Me.Filter 'Use the filter from the calling form.
.FilterOn = True
End With
End If

End Sub

 

[Sergeant]

Did you try it? Did it work? Tell us what it did if it didn't work...not just "It didn't work."

 

[businessman]

I tried it and it still shows every record. the way that i'm filtering my form is:

Dim test1 As String
test1 = "SELECT Probs.IssueCategory, Probs.ProblemID, Probs.Description, Probs.Comments," & _
" Probs.Status, Probs.IssueCategory, Probs.[Product Line], Probs.Assigned, Probs.[LOE/SRD], Probs.[Legacy/Epic] " & _
"FROM Probs " & _
"WHERE (((Probs.IssueCategory)='" & strCL & "' OR (Probs.IssueCategory)='" & strME & "' OR (Probs.IssueCategory)=" & _
"'" & strVE & "' OR (Probs.IssueCategory)='" & strCR & "' OR (Probs.IssueCategory)='" & strPAR & "') AND " & _
"((Probs.Status)='" & strCLOSED & "' OR (Probs.Status)='" & strOPEN & "' OR (Probs.Status)='" & strHOLD & "') AND " & _
"((Probs.[Product Line])='" & strRADAR & "' OR (Probs.[Product Line])='" & strRADIO & "' OR " & _
"(Probs.[Product Line])='" & strSATCOM & "') AND ((Probs.[LOE/SRD])='" & strLOE & "' " & _
"OR (Probs.[LOE/SRD])='" & strSRD & "')) " & _
"ORDER BY Probs.Assigned;"

RecordSource = test1
Assigned.FontWeight = NORMAL
Priority.Visible = False
PrioritySeq.Enabled = False
ProblemID.FontWeight = NORMAL
ListingBy.Caption = "CL Status"

When i put the code you suggested in the report code or in the code for the report button it doesn't filter. If do your first suggestion it asks me for a parameter. The filter above has a bunch of strings. each string is set through more code when a checkbox is checked. if a value does not need to be searched for, it's string will be set to something that is being looked for.

 

[Sergeant]

That's what I was saying back here...

It should work if you are applying a native Access filter to your form.

...and you are not using a native Access 'Filter'...you are filtering by way of altering the select statement.

Providing all the fields are the same from your form record source to your report record source, you can just apply the form's record source to your report.

 

[businessman]

can you add fields to the report that are not in the form?

 

[Banana]

Well, yes and no.

By that, you can set any recordsource you want to any form or report, but only controls that are bound to certain fields will show up. The fields that does not have a bound control will not show up at all.

 

[businessman]

Well then since my recordsource for my form is set from a string, how do i set my report recordsource to that same sting?

 

[Banana]

In same event where you open your report,

Docmd.OpenReport........
Reports![YourReportName].RowSource= "Your string here"

This assume that you are not opening it as acDialog otherwise the code will not run the next line.

 

[businessman]

Whats the difference between RowSource and RecordSource

 

[Banana]

Oops.

My mind was still stuck with rowsource.

I meant recordsource


Recordsource is where the form or reports get their field's binding from.

Rowsource is for combobox or listbox, to be used as a lookup.

Sorry for confusion.

 

[businessman]

I typed:
Private Sub ReportOpen_Click()
DoCmd.OpenReport "Check Filter", acPreview
Reports![Check Filter].RowSource = strRecordSource
End Sub

strRecordSource is the string name that holds the SQL string thats doing the filter. where the string gets set i set the sub to a Public to make sure it would be accessable from everything else.

When i click the button to open the report it gives me an error saying
"Run-time error '2465':
Application-defined or object-defined error

 

[Banana]

That's because I gave you wrong properties; It should be Recordsource, not Rowsource

See my previous post.

Again, sorry for mixing it up.

 

[businessman]

is there no way to update the recordsource of a report without it being open than creating a macro to do it?

 

[Banana]

I'm not sure why you want to do that, but if you really wanted to, you could use this code...

DoCmd.OpenForm "X", acNormal, , , , acHidden
Report!X.Recordsource= "Your String"
DoCmd.CloseForm "X"

However I can't tell whether closing the form will cause it to lose the recordsource you just put in. However this will do the trick and let you do other things, like printing it out, without actually opening the report.