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 October 14th, 2003, 05:03 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Filter Report using value Box

I have 3 value boxes receiving their criteria from 3 list boxes. I would like the 3 value boxes to filter a report called "Branches" accordingly as it becomes opened in either preview or print view.
The 3 value boxes simply dictate city, license & dealer respectively, for particular branches within the "Branches" report.
I would like to use VBA exclusively for this procedure.
Thank you for any assistance offered.

 
Old October 15th, 2003, 09:06 AM
Authorized User
 
Join Date: Jun 2003
Posts: 24
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The way to do this is simply to write an SQL statement based on the selections from the boxes, and then have that statement populate an variable that is a recordset.

Then in the "On Open" event of the report, set the recordsource to that recordset.

Me.RecordSource = rs.Name

where rs is the recordset created by the program creating the SQL.

Let me know if you get stuck on this.

John
 
Old December 8th, 2003, 12:47 AM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi John,
I am finally addressing this issue. Not out of laziness or procrastination, but overwhelmed with other VBA issues. I just started learning....
Your suggestion worked great. I have a pop up form with 4 combo boxes that offer criteria to create a SQL statement for the Main forms recordsource.
The code is very simple, but I would like, at this point to have an "IF" statement to check if the criteria is valid.
I don't know how to approach this.
eg;
If so and so is ? Then
MsgBox "No records match selection"
Else
Forms.frmProjects.Form.RecordSource = .....
End If
Any suggestions would be great, otherwise, thank-you very much for your help on my last 2 requests, very usefull information for sure!
Have a great day!

 
Old December 10th, 2003, 04:21 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

if rs.RecordCount = 0 then
    msgBox "No records found!",vbInformation
Else
   ....your code
end if
 
Old December 14th, 2003, 12:47 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Edcaru,
Thank-you for that!
After looking into what you said, I agree, it should work, and yet, it doesn't?
What I've done is simply create a SQL with a pop up form & assign it to the recordsource of the form I want to "Filter". All this happens from the "Find" command button from the pop up form. All variables are private within the click event method of the command button.
Do I need to declare any public variables & assign any to the form I want to "Filter"
The recordcount is always showing the total number of records in the query, no matter what criteria is entered.
Any help would be appreciated & thanks again for your original response!

 
Old December 15th, 2003, 07:47 PM
Authorized User
 
Join Date: Nov 2003
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Are u using a parameter query to populate the form you want to filter.
You can use Form1.filter = "SELECT * FROM table1 WHERE field1 = " _
& Forms!frmPopUP!combo1
R u using a fully qualified reference?
 
Old December 17th, 2003, 08:04 PM
Authorized User
 
Join Date: Oct 2003
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Edcaru,
I'll try to explain as coherently & concisely as possible.
SQL works fine, search works fine...except if there is a record that doesn't exist according to user entries into upto 5 comboboxes.
Out of curiosity, I created a field that holds recordcount.
No matter what criteria is entered (as long as it's valid), the recordcount field always shows the maximum records "80", which is the the original query.
I played around with my If statement eg; If rec.recordcount < 80 Then
...and the Message box will appear.
So...it appears that the problem (for whatever reason), is that even when a new record is formed(initially), the record count always shows 80?
I don't know what "clause" to stipulate in the IF Statement to ensure no non existent record will create a new form.
I still feel, what you said, If rec.recordcount = 0 Then... should work?
Either way Edcaru, Thanks again!







Similar Threads
Thread Thread Starter Forum Replies Last Post
How to apply filter to shrink combo box records beetle_jaipur Access 3 July 28th, 2007 01:58 AM
Filter records using a combo box tixe83 Access 2 August 3rd, 2006 12:43 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 TonyG Access VBA 6 February 11th, 2004 11:56 AM





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