Report output based on search results

[sokolln]

I have seen the other posts in the same vein as this question but I've been unable to solve my problem.
I have a search function and when the search is executed I would like to view reports based solely on the search results.
The code I am using is as follows:

 Dim stDocName As String
stDocName = "Organizational Profile"
If Me.FilterOn And Len(Me.Filter & "") > 0 Then
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=Me.Filter
Else
DoCmd.OpenReport stDocName, acViewPreview
End If
 
Exit_Preview_report_Click:
Exit Sub
 
End Sub

This works fine if I "filter by form" but it does not work with my search function. When I run the search, the total record number decreases but the word "(filtered)" does not appear after it.
This is the filtering code for my search:

         Forms![Master Form by Ult Parent].RecordSource = "select * from [Master Form by Ult Parent Query] where " & GCriteria
        Forms![Master Form by Ult Parent].Caption = LCaption
        Me.FilterOn = True

Any help is much appreciated

 

[namliam]

        Forms![Master Form by Ult Parent].RecordSource = "select * from [Master Form by Ult Parent Query] where " & GCriteria
        Forms![Master Form by Ult Parent].Caption = LCaption
        Me.FilterOn = True

The me.filteron in above code is useless as you are not filtering you are changing the rowsource....

For the filter to your report in this case you should use GCriteria...

 

[sokolln]

My appologies, but I am lost. How do I use GCriteria when filtering the report?

 

[namliam]

Like so:
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=GCriteria

 

[sokolln]

Thanks. Ok that's what I thought. I had tried that and a report for all records was displayed. Any other thoughts?

 

[namliam]

If your GCriteria contains a valid where clause, then only the records that meet these requirements should be displayed...

Certainly not all.
I think GCriteria is empty instead of containing a valid where clause.
Try adding:
Msgbox Gcriteria
in front of the docmd.OpenReport

 

[sokolln]

I may not be doing this right. The code I'm working with is:

Private Sub cmdPrntFltr_Click()

Dim stDocName As String

stDocName = "Organizational Profile"

If Me.FilterOn And Len(Me.Filter & "") > 0 Then
MsgBox GCriteria
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=GCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview
End If


Exit_Preview_report_Click:
Exit Sub

The true response to the if/then statement isn't be triggered

 

[namliam]

That is because both FilterOn is False and Me.Filter is Null/Blank.

despite your setting me.filteron = true, it reverts back to false because the filter is blank.

You will have check if GCriteria is set...

Or... change this:
Forms![Master Form by Ult Parent].RecordSource = "select * from [Master Form by Ult Parent Query] where " & GCriteria
to:
Me.filter = GCriteria
me.filteron = true

Oh, PS:
You really shouldnt use spaces or any special characthers _)(*&^%$ etc in query/form/any names

 

[sokolln]

Oh yes, I've learned the hard way about those spaces. Never again.

Anyways, the result of the modified code is the search does not work....

 

[namliam]

Perhaps you have to replace Me with Forms![Master Form by Ult Parent] ??

I am stabing in the dark here.

 

[sokolln]

The search works with that but the report function is still pulling up all records

 

[namliam]

Offcourse in the report part you need to change the me as well...

 

[sokolln]

Yes I caught that one myself. The code is:

Dim stDocName As String
stDocName = "Organizational Profile"
If Forms![Master Form by Ult Parent].FilterOn And Len(Forms![Master Form by Ult Parent].Filter & "") > 0 Then
MsgBox GCriteria
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=GCriteria
Else
DoCmd.OpenReport stDocName, acViewPreview
End If

Exit_Preview_report_Click:
Exit Sub

 

[namliam]

And offcourse change the way you want to pass along what criteria...
Change:
MsgBox GCriteria
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=GCriteria
To
MsgBox Forms![Master Form by Ult Parent].Filter
DoCmd.OpenReport stDocName, acViewPreview, WhereCondition:=Forms![Master Form by Ult Parent].Filter

 

[sokolln]

Brilliant! It works! Thank you!

Would having this command open a form that displayed all the info together be as simple as changing the report references to form references?

 

[namliam]

*uhm* yes... it would... offcourse you have to create a form for it first.... but yes, opening a form or a report is basicaly the same.

Tho... You have a form displaying it allready?? So i dont completely understand where you are comming from / moving to with this question????!!!!!

Basicaly... yes... it is exactly the same except for "OpenReport" I guess would have to be .... *erm* Changed

 

[sokolln]

A curve ball yes. The form the data is displayed in is quite data heavy - one record takes up multiple pages. The idea with the new form is just the essential information so matches can basically be shown in a list format.

 

[sokolln]

Ugh. It couldn't be that easy.

Do you see anything silly I'm overlooking here?

Private Sub cmdPrntFrm_Click()
Dim stDocName As String
stDocName = "Auto-Generate UP Form"
If Forms![Master Form by Ult Parent].FilterOn And Len(Forms![Master Form by Ult Parent].Filter & "") > 0 Then
DoCmd.OpenForm stDocName, acViewPreview, WhereCondition:=Forms![Master Form by Ult Parent].Filter
Else
DoCmd.OpenForm stDocName, acViewPreview
End If
Exit_Preview_form_Click:
Exit Sub

End Sub

 

[namliam]

acViewPreview is a property to be used in conjunction with reports not forms

Check out the help on Opendform command... it tells all

 

[sokolln]

works wonderfully. thank you