Wrox Programmer Forums
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 10th, 2004, 09:34 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default Filter Report

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

 
Old February 10th, 2004, 04:55 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old February 11th, 2004, 07:25 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old February 11th, 2004, 09:01 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old February 11th, 2004, 11:00 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


 
Old February 11th, 2004, 11:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

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
 
Old February 11th, 2004, 11:56 AM
Authorized User
 
Join Date: Oct 2003
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)






Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter vs WhereCondition in Report Access powerz Access 4 May 23rd, 2006 02:57 PM
Access Report Filter rfurman1161 Access 10 March 22nd, 2006 09:14 PM
How to filter Crytal report ? abdusalam VB How-To 0 June 24th, 2004 01:44 AM
Filter Report using value Box DBoulos Access VBA 6 December 17th, 2003 08:04 PM
Filter Report by a formulate field Tere Crystal Reports 0 December 1st, 2003 07:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.