Dealing with an empty recordset (1 Viewer)

[kully]

I am trying to loop through one query (qryMailingList) and using the value in field UserID. I am using this value in my criteria in another query (qryExceptions field:Opportunity Primary Login).

If qryExeptions has records in it, I want to email this to the user using the email address in the field TestEmail

If qryExceptions opens with no records I want to go to the next record in qryMailingList and do the process again until I reach the end of the file.

I am a novice at VB so am struggling a bit. I can't get the bit to work where if there are no records then go to the next record.
The code I have so far is (which doesn't seem to work):

Public Function User()
User = MyUser
End Function

Sub Output_Query()

Dim dbs As DAO.Database
Set dbs = CurrentDb

Dim MailRS As Recordset
Dim ExcRS As Recordset
Dim MyUser As String
Dim MyExc As String

Set MailRS = CurrentDb.OpenRecordset("qryMailingList", dbOpenDynaset)
Set ExcRS = CurrentDb.OpenRecordset("qryExceptions", dbOpenDynaset)

MailRS.MoveFirst
Do Until MailRS.EOF = True

MyUser = MailRS("User ID")

DoCmd.OpenQuery ("qryExceptions")
MyExc = ExcRS("Opportunity Primary Login")

If ExcRS <> Null Then
DoCmd.TransferSpreadsheet
Else

MailRS.MoveNext
End If
Loop
MailRS.Close

End Sub


Any help would be very much appreciated.

 

[Alexandre]

You have to use:
If ExcRS.NoMatch Then
to test whether the recordset is empty

 

[kully]

Alex - Thanks for this............

That solved the problem with the empty recordset, but I seem to be doing something wrong as my qryExceptions doesn't open with any records.

Is this line correct....

MyUser = MailRS("User ID")

MailRS is my query qryMailingList and the field User ID is the citeria I want to use in qryExceptions.

In qryExceptions, the User ID is in a field called 'Opportunity Primary Login' and in the criteria for this field I have put User()

I presumed that MyUser is the User ID that I am currently on and this value is what is used as the criteria for User()

What am I doing wrong?

 

[Alexandre]

As a matter of fact, I have difficulies to understand what your code does and is suppposed to do. So let's see if I understand well your pb:
1. you have two queries that can be joined on a common field: UserID (note that using spaces in field names is bad practice albeit allowed in Access. You should really consider getting rid of them. I know, that's no fun).
2. You want to loop through qryMailingList and for each UserID in it, see if they are matching records in qryExceptions.
3. If there are, you want to act upon them (do what? emailing? exporting to data sheets?)

If the above is correct, you can do much of the preparation work without code, just by making a new query based on properly joined qryMailingList and qryExceptions.

Wait for further info from you

 

[kully]

Sorry, I probably don't explain myself very well.

I have a qry called qryExceptions which filters out data which has certain criteria i.e. no order date, or status between 1-4 etc. I want to mail these Exceptions to each individual user to let them know what Exceptions they have.

I then have a query called qryMailingList which has a list of Users (User ID) and their email address.

Therefore, what I was trying to do was, with my list (qryMailingList), I was trying to loop through the list and use the User ID as the criteria in qryExceptions to show all the Exceptions for that particular user and then mail that to the user. If qryExceptions had no records for that user then I would move to the next User ID in qryMailingList.

Am I still confusing?