|
Subject:
|
Filter Report
|
|
Posted By:
|
TonyG
|
Post Date:
|
2/10/2004 8:34:11 AM
|
Hi
I am still new to VBA and trying to work out how to create an SQL query in Code which will be activated via Form which users will have to define what subject or date they want to view data for. The form will consist of Combi's and Txt boxes, and an Apply cmd button which will activate the code using the selected items from Combo and Text box to filter down the topics which are currently on show in the report.
When the report is loaded, it loads the filter form and shows all records.
This is the code i have tried to use for the Click event:
Private Sub cmdApply_Click()
Dim strWhere As String
strWhere = "[Call Cat 2] = " & txtCat2
With m_rptSales
.Filter = strWhere
.FilterOn = True
.txtFilter.Visible = True
.txtFilter = strWhere
.lblFilter.Visible = True
End With
End Sub
"Call Cat2" is a name of a field in the Report.
Or am i doing it all wrong, and should come in from another direction?
Thanks
Tony
PS It is from an example i took from "Beginning Access 2002 VBA", Chapter 10
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
2/10/2004 3:55:35 PM
|
Are you saying that the form is filtered correctly, but when you open the report it's still showing all records? If you set the filter on the form and the form is working, then on the report you can merely put this on the On Open event:
Me.Filter = Forms.frmMyFormName.Filter
or
Me.Filter = Forms.frmMyFormName.Form.Filter
I forget if you need the .Form or not.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
TonyG
|
Reply Date:
|
2/11/2004 6:25:11 AM
|
Hi
Thanks for getting back. :)
I use the form to filter data within a report. The report uses the Open Even to open the Form at the same time, and then the user selects the topic they wish to filter the report for by clicking Apply and activates the following code:
Private Sub cmdApply_Click()
Dim strWhere As String
strWhere = "[Call Cat 2] =" & Chr(34) & cboCat2 & Chr(34)
With m_rptSales
.Filter = strWhere
.FilterOn = True
.txtFilter.Visible = True
.txtFilter = strWhere
.lblFilter.Visible = True
End With
End Sub
As you can see i have changed the code so that it allows for Text to be searched for in the 'Filter' property of the Report. It actually works since doing this (I discovered the Chr() from a friend and i think this was wheat was bothering me the most), but now trying to see what else i can do it by creating a larger Search string using Fields from the form for users to filter the Report further eg by date etc.
I am starting to realise that Access is so much bigger than i first suspected.
Tony
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
2/11/2004 8:01:06 AM
|
I think you're making this more of an effort than it needs to be. First Chr(34) is simply a quote mark ("). You really don't need it.
strWhere = "[Call Cat 2] =" & Chr(34) & cboCat2 & Chr(34)
can easily be expressed as
strWhere = "[Call Cat 2] = '" & cboCat2 & "'"
Also, you don't need to do anything in the report. If your form is already filtered the correct way, on the button's On Click event that opens the report, you need only do this:
Me.Filter = strWhere
Docmd.OpenReport "rptMyReport", acViewPreview , Me.Filter
If you don't filter the form first, then the button would have this
Docmd.OpenReport "rptMyReport", acViewPreview, , strWhere
Note the first one has a filter after the acViewPreview. The second one has nothing (two commas) but does have a where criterion. Either way, the report has no interaction with the user. The form does.
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
TonyG
|
Reply Date:
|
2/11/2004 10:00:15 AM
|
HI Serrano
Thats working alot more efficiently than before now, thanks for that. What does the stand for in the code because when i stopped the routine it didn't show any additional formating or clue to indicate what it was?
strWhere = "[Call Cat 2] = '" & cboCat2 & "[red]'"
Thanks Again
Tony
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
2/11/2004 10:33:08 AM
|
Oh, sorry... I answered your post right off the Internet. When I did that, I highlighted the single quotes (') and commas (,) with red so you would notice them and know that it wasn't a typo. I forgot that web page formatting transfers to e-mail messages. Remove any color formatting from the code. That is:
strWhere = "[Call Cat 2] = '" & cboCat2 & "'"
And
Docmd.OpenReport "rptMyReport", acViewPreview, , strWhere
Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division
|
|
Reply By:
|
TonyG
|
Reply Date:
|
2/11/2004 10:56:53 AM
|
Ahhhh! Thanks for that, i was going through the code trying to work it out and could not find anything to indicate to what it was (but i noticed something on the web page only after checking what i had sent in in to the last reply)
|
