I am trying to run an existing query from a code procedure and specifying a certain query field criteria...
For instance:

I have a query named "Expired_Annex_Query".

This query has 8 fields:
The first field is "Employee_Name"
The second field is "Employee_Annex"
The rest of the fields are 6 different expiration dates pertaining to the employee

I am trying to have an E-mail notification sent to the e-mail address associated with the annex in another table when an employee has any of the expiration date fields expired.

So I must run this query once for each annex (24 annexes), with the criteria changing each time for the "Employee_Annex" Field and an E-mail sent to the respective e-mail address associated with this annex if there are any results.


I posted this earlier in the "FORMS FORUM" by mistake...

Thanks in Advance!!!!

Mitch

for the code you need 2 parts. The first gets the annex details from the annex table and the second part uses that information to limit the query. To get the SQL string limit your query with one annex and then look at the SQL view. Copy the string and place it in your code between the double quotes.

When you paste the SQL string you will find carriage returns. Remove them.


dim rs as recordset
dim qd as querydef
dim feedstr as string
dim AnnexName as string
set rs = currentdb.openrecordset("TableName")
set qd = currentdb.querydefs("QueryName")
rs.movefirst
do
AnnexName = rs("AnnexNameField")
FeedStr= "...... WHERE .. ='" & AnnexName & "'.....;"
qd.SQL = FeedStr


do whatever you want with the result of the query

rs.MoveNext
Loop until rs.EOF = True
rs.Close
qd.Close


Hope that points you in the right direction