Looping through 2 recordsets

[beckyr]

Hello, can anyone tell me where i am going wrong please? Im trying to send an email to tutors who have student/s in a particular school which is entered by use of an input box.
At the moment i've created 2 recordsets. The first one finds out what courses are related to that school (as the student table only has course codes not school codes in it)
The second recordsets finds the tutors who have students in that school
Currently it is creating an email to only one of the tutors (but enters in their email address twice) Is it because of the way i am trying to loop through both recordsets?

sch = InputBox("Please enter the school number", , "School Number")


str2 = "Select [tblCourse].CS_CODE from [tblCourse] where [tblCourse].CS_SCH_NO = " & "" & sch & ";"
Set rs = db.OpenRecordset(str2)
code = rs("CS_CODE")

Do While Not rs.EOF
strsql = "Select DISTINCT [tblTutor].[TU_EMAIL] from [tblTutor] inner join [tblStudents] on [tblTutor].TU_CODE = [tblStudents].STU_TU_CODE " _
& "where tblStudents.STU_COURSE_CODE = '" & code & "'"
Set rs2 = db.OpenRecordset(strsql)


Do While Not rs2.EOF
If Not IsNull(rs2("TU_EMAIL")) Then
addresses = addresses & ";" & rs2("TU_EMAIL")
End If
rs2.MoveNext
Loop

rs.MoveNext
Loop

 

[GolferGuy]

Could you post some sample data for these two tables? That would really help.

 

[beckyr]

Sure:

tblCourse CS_CODE CS_NAME CS_SCH_NO CS_FAC_NO
e.g. TR001 History 2 1
e.g. TR801 Nursing 57 3


tblStudents STU_ID STU_COURSE_CODE STU_TU_CODE
e.g. 1 TR801 81
e.g. 10 TR001 P4


tblTutor TU_CODE TU_EMAIL
e.g P4 xyz@hotmail.com
e.g. 83 123@gmail.com


This what you meant?

 

[rolaaus]

If I am understanding correctly (Which I admit, I may not be), you probably do not need 2 different recordsets. You may simply just need to join 3 tables together in a query instead of the current 2 (tutor and students).

What I will sometimes do, when I come across a VBA line of code that uses SQL that is too complicated for me to write out by hand, I jump into a Query Design and use the GUI to build the statement, then switch back over the VBA and drop the query (using SQL view) into the code.

 

[beckyr]

Thanks Rolaaus, i had tried using 1 recordset but it got too complicated but once i put it into query design i got it - thanks!

 

[GolferGuy]

rolaaus and beckyr,
I have written a small utility that will take the SQL from a query and convert it into VBA code. You can within the SQL screen on the utility name variables within your VBA code, so the value from a variable can be inserted into the SQL string at run time. For example, within the WHERE clause if you only want data from the student course you are dealing with at that moment.
Click on the Access Design Tips below my signature and the SQL to VBA utility is next to last on that page. There is also a little documentation available on that same line.