Basing a report on "Filter by Form" 1

[chappie2]

Let me start with an etiquette question - I posted a message similar to this one a week ago that got only one response. I have searched the site to no avail. Is there a "correct" way to ask for more input?

My question is: How do I get the records selected through a "Filter by Form" query to be carried into a report? The form is based on a query linking several tables and any report coming from that form seems to look at the query, rather than the results of the "Filter by Form".

I have placed a command button in the footer of the form (the form displays one line per record) and it works fine if the form is based on a table. But when it is based on a query, the Filter by Form is ignored and all records are retrieved.

Thanks for your help.

Lloyd

 

[LaurelLee]

I think you would have an easier time if you set up the filter selection within your form by if you are using a query. I have never had luck with filter by form, but that is just my experience.


___________________________________________________________
With your thoughts you create the world--Shakyamuni Buddha

 

[dhookom]

I don't use the filter by form but I think it places a "where" clause in the filter of the form. This same syntax can be used in the OpenReport line:
DoCmd.OpenReport "rptYourReport", acPreview, , Me.Filter

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

Thanks for the lead, but I still can't get this to work.

When I use the Filter by Form feature, Access creates a query with the apparent name of MyFormFilter. Is there a way to save this "temporary" query and use it as the basis for the report? When I use language similar to yours, it simply opens the report with the query that is the basis of the originating form?

Lloyd

 

[dhookom]

Try
DoCmd.OpenReport "rptYourReport", acPreview,"MyFormFilter"

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

It seems to recognize the filter name, but opens a dialogue box labeled "Enter parameter value". If nothing is filled in, no records display in the report. If a specific value is entered, all records are shown in the report.

Lloyd

 

[dhookom]

I just "played" with the filter by form a little and there is a filter property value. For instance, I set a filter and then opened the debug window

? forms!zfrmLines.filter
((tblLineTrans.LINNAM Like "Ln 3*"))

A where clause like:
LINNAM Like "Ln 3*"
in DoCmd.OpenReport should work. YOu might have to write code that eliminates the tablename and period.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

Duane,

Thanks for the FAQ reference, it is quite helpful in keeping me on track.

A couple of followups to your post:

1) You mention that there is a "filter property value". Are you refering to the Filter Property under Data in the properties of the form or in some other location?

2) Can you give me a little more help on the coding you suggest? What are LINNAM and lin 3*? Thanks.

Lloyd

 

[dhookom]

1) Yes, that is the property that gets "valued" when you do your filter by form.
2) the value I posted was taken from the Filter property of the form that I had open on my PC. Yours would depend on your filter by form.

You can filter by form and then press Ctrl+G. This opens the debug window. Try enter
? Forms!frmYourFormName.Filter
to see the value of your form's filter property

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

Duane,

Thanks again for your help. I used the following DoCmd.OpenReport, which specifies the filter in the where location rather than the filter location and it seems to do exactly what I want - allow the user to use the filter by form to select whatever records they want, then the button dumps those records to a report.

DoCmd.OpenReport stDocName, acViewPreview, , Forms!frmGrantSumView.Filter

Lloyd

 

[chappie2]

I was so pleased to make major progress, that I missed one element. How do I pass the display order (sort) from the form on to the report?

Lloyd

 

[dhookom]

In addition to the filter property of the form, it has a OrderBy property. You can grab this similar to your other code but do so in the On Open event of the report. You can then modify the Sorting and Grouping level property using code like

http://members.iinet.net.au/~allenbrowne/ser-33.html.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

I have tried a couple of alternatives, but no "luck" so far. I tried your suggestion, but don't think I fully understood it. The followup led to a popup box rather than simply using the order by established in the form.

Someone suggested the following:

With Forms!frmGrantSumView
If .OrderByOn Then
Me.OrderBy = .OrderBy
Me.OrderByOn = True
End If
End With

I tried placin this both in the Open Report event and below the DoCmd.OpenReport. With this I am able to get the orderby property to appear in the properties of the report, but the report does not reflect the order in the form.

Any further suggestions?

Thanks, Lloyd

 

[dhookom]

I never use the OrderBy property of a report. You should create at least one sorting and grouping level. You can then use code like the link I provided to modify the ControlSource property of the grouping level(s) to the same value as used to order your form. The ControlSource property will need to begin with "=" and should not include the table name, just the field name.

Don't ever rely on the OrderBy property of a report.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[chappie2]

Thanks for your help and patience!!

Let me see if I understand the code you provided earlier and shown below - it says that if the value of the grpSort field is "Name", sort it in one way and if it is "Company" sort it as shown in case 2.

Select Case Forms!frmChooseSort!grpSort
Case 1 'Name
Me.GroupLevel(0).ControlSource = "LastName"
Me.GroupLevel(1).ControlSource = "FirstName"
Me.GroupLevel(2).ControlSource = "Company"
Case 2 'Company
Me.GroupLevel(0).ControlSource = "Company"
Me.GroupLevel(1).ControlSource = "LastName"
Me.GroupLevel(2).ControlSource = "FirstName"
End Select

When I do a sort in the form, the form is updated to list the field name in the "Order by" of the form. I think I want to use this Order by statement as the "test expression" in the Select Case statement. But I don't know how to refer to it, since it is not a particular field, but the value of the "order by" setting in the form. Neither Forms![MyForm]!grpSort or OrderBy seem to work.

Thanks, Lloyd

 

[dhookom]

If your form is open and has an order set, then you should be able to use:
Select Case Forms!MyForm.OrderBy
Case "tblA....."
Me.GroupLevel(0).Controlsource = "..."
Case "tblA....."

End Select

Duane
MS Access MVP
Find out how to get great answers faq219-2884.