Printing a report based on the filtered results of a form

[Bruce75]

Here is the problem:



I have a form where the user selects a value from an unbound list box. The selected value is used to filter records. The results are shown on a second, Main Form. The filter is applied through VB which was generated through a wizard. On the second form, there is a print preview command button which opens a report. When the report opens from the filtered results of the second form, all the records are displayed rather than just the filtered records. How do I make the report show only the filtered results? The record source is from a main table. I cannot copy the filter property from the Main Form to the report, as it changes depending on the selection the user makes on the first form.



1st form (user selects value) >>> 2nd form (Main Form – displays filtered results from user selection) >>> Report (I want it to show just the filtered results, not all the data)


Please bear in mind that I am a novice and quite thick if you answer this. Also, I have tried searching the forum but could not find a solution - this could also be related to my stupidity.

thank you

 

[Ukraine82]

Take a look at the attached file. It a little similar to your question, so you can play around with it.

hth,
Michael

 

[Bruce75]

thank you very much for your prompt reply.

i have looked through the attached file, and I understand that:

DoCmd.OpenReport "rptSuppliers", acViewPreview,

opens the report rptSuppliers in preview view

and that:

, , "SupplierID > 5 and CompanyName like 'S*'"

specifies that the filter brings back all records whose ID is greater than 5 and begins with S

However, in my database this parameters of the filter are chosen by the user on the first form. So, I need to get what the user has selected into the (and instead of the): "SupplierID > 5 and CompanyName like 'S*'" bit.

I have tried this:

Private Sub Preview_report_Click()
On Error GoTo Err_Preview_report_Click

Dim stDocName As String

stDocName = "SLAM supervisor report"
stLinkCriteria = "[Directorate/Division/Speciality]=" & "'" & Find_by_Directorate!Me![Combo8] & "'"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Exit_Preview_report_Click:
Exit Sub

Err_Preview_report_Click:
MsgBox Err.Description

Resume Exit_Preview_report_Click

where Find_by_Directorate is the form where the user selects the criteria for the search, and combo8 is where the value is that the user selects. however this does not work.

please help!
Resume Exit_Preview_report_Click

 

[Ukraine82]

Sorry for the delay I got held up at work. Try another way to filter your report by making a query connecting to your report. If you do a search in this forum on filter reports, I'm almost positive you'll find plenty of examples.

hth,
Michael

 

[Bruce75]

A solution

Thank you for your advice. After many extensive searches on google I found a solution:

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

(I put it in the preview report button bit of the VB for my main form)

This works for me, so I thought I would be helpful and post the code.

 

[Janjang]

y when i use cbo, these solotion not work? it say that i "mismatch in criteria expression..
can any1 help me?