Open query based on currently filtered form - Access 2007

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
Clean14Asked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:

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
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Clean14Author Commented:
Thanks for the quick response.  Where would I place the code that you supplied?
Thanks
0
 
Rey Obrero (Capricorn1)Commented:
let me ask the question-- where do you want the data from the query?
0
 
Clean14Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
then apply the filter to the query before you do the mail merge
0
 
Clean14Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:

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
 
Clean14Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
where is the file?
0
 
Clean14Author Commented:
Sorry about that.  It wouldn't let me attach the .accdb file, so I just changed it to .mdb.  Thanks.
test.mdb
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Clean14Author Commented:
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
 
Clean14Author Commented:
I tried that code, but I have the same problem.  Query loads with last filtered criteria.  Thanks
0
 
Rey Obrero (Capricorn1)Commented:
Clean14, did you remove the filter in the form before you open the query?
0
 
Clean14Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
click the remove filter button before opening the query
test.mdb
0
 
Clean14Author Commented:
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
 
Rey Obrero (Capricorn1)Commented:
just select the more appropriate one
0
 
Clean14Author Commented:
Thanks again for all yoru help.  You saved me TONS of time and frustration.  
Pat
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.