Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Open query based on currently filtered form - Access 2007

Posted on 2008-06-23
21
Medium Priority
?
635 Views
Last Modified: 2013-11-29
Hello,
I am trying to integrate a mail merge function with Acces 2007.  I want my users to be able to narrow the list of contacts that will recieve an email by filtering fields on a form.  Once the user filters the form, they would click an email button that would start the mail merge wizard.  Inside the mail merge wizard, the user has to choose a table or query to get the data from.  My question is this - how do I base a query on an open form's filtered records?
Thanks
0
Comment
Question by:Clean14
  • 11
  • 10
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21849592
get the form's filter and apply it to the query..

dim  qd as dao.querydef, ssql as string


qd=currentdb.querydef("nameOfQuery")
ssql=qd.sql

ssql=ssql & " where " & me.filter

qd.sql=ssql
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21849620
or better set a general query string

dim genSql,qd as dao.querydef, ssql as string
genSql="select * from tablex"

qd=currentdb.querydef("nameOfQuery")

qd.sql=genSql & " where " & me.filter
0
 

Author Comment

by:Clean14
ID: 21849692
Thanks for the quick response.  Where would I place the code that you supplied?
Thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21849997
let me ask the question-- where do you want the data from the query?
0
 

Author Comment

by:Clean14
ID: 21850092
The mail merge wizard will be accessing the query.  I just want the query to always reflect the records that are filtered on my main form.  The main form will be open when the mail merge wizard looks for the mail merge query.  I hope that makes sense.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21850145
then apply the filter to the query before you do the mail merge
0
 

Author Comment

by:Clean14
ID: 21850249
I know how to apply a filter to a query....what i am trying to do is have the query look at the open form, and copy the form's current filters automatically.  Is that possible?  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21850514

create a button in your form cmdTest

filter the form before you click the button

in the click event of cmdTest

private sub cmdtest_click()
dim genSql,qd as dao.querydef, ssql as string
genSql="select * from tablex"     'change { tablex } with the actual name of the table

qd=currentdb.querydef("nameOfQuery")  'change "nameOfQuery" with the name of the query

qd.sql=genSql & " where " & me.filter

docmd.openquery "nameOfQuery"

end sub
0
 

Author Comment

by:Clean14
ID: 21855864
I tried your last suggestion, and when I click the button, I get an error message - "compile error - method or data member not found" I've attached the file for your reference.  Thanks for your help!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21855890
where is the file?
0
 

Author Comment

by:Clean14
ID: 21856052
Sorry about that.  It wouldn't let me attach the .accdb file, so I just changed it to .mdb.  Thanks.
test.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21856232
try this codes

Private Sub cmdtest_click()
Dim genSql, qd As DAO.QueryDef, ssql As String
genSql = "select * from Executives"  

Set qd = CurrentDb.QueryDefs("Query1")

qd.SQL = genSql & " where " & Me.Filter

DoCmd.OpenQuery "Query1"

End Sub

0
 

Author Comment

by:Clean14
ID: 21856343
Almost there!  The code worked except for 1 thing.  If I don't filter any records, the query still opens with the last filtered criteria.  Is there a way to empty the filter from the query when it is closed?  Thanks!
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 21856391

Private Sub cmdtest_click()
Dim genSql, qd As DAO.QueryDef, ssql As String
genSql = "select * from Executives"  

Set qd = CurrentDb.QueryDefs("Query1")

if len(me.filter)>0 then
qd.SQL = genSql & " where " & Me.Filter
else
qd.SQL = genSql
end if

DoCmd.OpenQuery "Query1"

End Sub
0
 

Author Comment

by:Clean14
ID: 21856527
I tried that code, but I have the same problem.  Query loads with last filtered criteria.  Thanks
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21856586
Clean14, did you remove the filter in the form before you open the query?
0
 

Author Comment

by:Clean14
ID: 21856689
I removed the filter by pressing the filtered / unfiltered button on the bottom of the form.  Here is the file again in case you want to try.  Thanks
test.mdb
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21856778
click the remove filter button before opening the query
test.mdb
0
 

Author Comment

by:Clean14
ID: 21856907
Thanks a lot for all your help.   I'm new to this site....does it matter which one of your comments I accept as the solution?  
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 21856961
just select the more appropriate one
0
 

Author Closing Comment

by:Clean14
ID: 31469908
Thanks again for all yoru help.  You saved me TONS of time and frustration.  
Pat
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question