Query using VB (1 Viewer)

[kully]

I am a beginner to VB in Access 97.

I have a query called qryMailing List which is a list of all users and their email addresses. Also a query called qryExceptions which is is a query with certain conditions applied to it.

I would like the code to open qryMailingList and go to the first user and then open qryExceptions (this query has the username as a criteria as per the code pasted below - MyUser). If then want the query to be exported in Excel and saved as a file. If the query comes back blank then I want to skip the exporting and go back to the next user.

I have tried writing some code but I'm not getting very far. I have pasted what I have done but may be completely wrong.

Any help would be much appreciated.

Public Function User() ['I have entered user() in my criteria in qryExceptions']
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) ['I'm not sure if I need this line']

MailRS.MoveFirst
Do Until MyRS.EOF = True

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

If MyExc <> Null Then
DoCmd.TransferSpreadsheet
Else

MailRS.MoveNext
Loop ['I get an error here saying I have Loop without Do']
MailRS.Close


Many Thanks

 

[simongallop]

You are getting the error because you haven't closed the If statement with End If.

 

[kully]

Thanks again Harry.

I am also getting an error on this line - do I need this?

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

I am trying to make a reference to my user field in qryExceptions which is "Opportunity Primary Login" I have done this with MyExc

Do I need to Set ExcRS in a different way?