Use one Query to lookup values in another Query

[kully]

I have a query called qryExceptions which holds records off all errors within a table. This has to be mailed out to the person who submitted that record.

From this query, I have created another query which looks up the users from qryExceptions and exracts their email address. This query is called qryMailingList.

I need to write code where it opens up qryMailingList and starting with the first record it looks up the user name and goes to qryExceptions and filters on that username and saves the output as an Excel or Text file and sends a mail to the user (using the field eMail Address in qryMailing List). I want the code to go through each record in qryMailingList till EOF.

I am quite new to VB. From past postings I have figured out how to open the recordset and DO While Not qryMailingList.EOF - I just don't know hoe to put the whole thing together.

Any help would be very much appreciated.

 

[simongallop]

You need 3 parts:

Open a module and paste the following code

Function User()
User = MyUser
End Function

In the criteria of the User field in your query qryExceptions place:

User()
{This will limit the query to whatever the value of User is}

Now for you main code:

Dim MyRS as recordset
Public MyUser as String

Sub Output_Query()
Set MyRS = Currentdb.openrecordset("qryMailingList",dbopendynaset)
MyRS.MoveFirst
Do Until MyRS.EOF = True
MyUser = MyRS("NameOfUserField")
Docmd.OpenQuery {Or whatever you want to do with the query}
MyRS.MoveNext
Loop
MyRS.Close
End Sub

HTH

 

[kully]

Thanks for this Harry.

If I filtered on user who wasn't included in the qryExceptions and the query came up blank, how can I skip to the next user in qryMailingList?

i.e. If qryExceptions is null then.........


Also, how do I save the qryExceptions output as an Excel or Text File?

(Sorry for bombarding you with questions)

Code so far..........


Public Function User()
User = MyUser
End Function


Sub Output_Query()
Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim MyRS As Recordset
Dim MyUser As String

Set MyRS = CurrentDb.OpenRecordset("qryMailingList", dbOpenDynaset)

MyRS.MoveFirst
Do Until MyRS.EOF = True

MyUser = MyRS("User e-mail Address")
DoCmd.OpenQuery ("qryExceptions")


MyRS.MoveNext
Loop
MyRS.Close