Emailing queries

[James]

Hello,

Right my problem is:
I have a database, two tables are "Students"
and "Tutors". Each day the database needs to send a email
to each tutor with their new students.
Now apart from making different queries for each tutor is
there efficient way of writing a SQL statement in VBA
that can write one query, change it, and email it for
each record in Tutor and only send them the students
assigned to them?

Hope you can help, thankyou in advance.

James

 

[Joe Fallon]

Sure.
Off the top of my head,
I would write the query ionce in the grid and set the criteria for a
specific teacher.
Then I would switch to SQL view and copy the code.

I would write a module and paste this code in and then modify to feed a
string variable.
e.g.
strSQL = "SELECT field1, field2 "
strSQL = strSQL & "FROM Table1 "
strSQL = strSQL & "WHERE Teacher='Jones';"

Then tweak it so the teacher is also a variable:
(Note that the single quotes still exist!!!)
Dim mTeacher As String

strSQL = "SELECT field1, field2 "
strSQL = strSQL & "FROM Table1 "
strSQL = strSQL & "WHERE Teacher='" & mTeacher & "';"

Now I would write code to open the table of teachers into a recordset and
loop through the list of teachers.
Just change
mTeacher =rs!["Teacher"]

Now the query runs once for each teacher.

Else where in the code do the export/email stuff.

 

[James]

Many thanks for your reply,

But how would I get the VBA code to save the query each
time so I can export it as an attachment? Or is there
another way?

-----Original Message-----
Sure.
Off the top of my head,
I would write the query ionce in the grid and set the criteria for a
specific teacher.
Then I would switch to SQL view and copy the code.

I would write a module and paste this code in and then modify to feed a
string variable.
e.g.
strSQL = "SELECT field1, field2 "
strSQL = strSQL & "FROM Table1 "
strSQL = strSQL & "WHERE Teacher='Jones';"

Then tweak it so the teacher is also a variable:
(Note that the single quotes still exist!!!)
Dim mTeacher As String

strSQL = "SELECT field1, field2 "
strSQL = strSQL & "FROM Table1 "
strSQL = strSQL & "WHERE Teacher='" & mTeacher & "';"

Now I would write code to open the table of teachers into a recordset and
loop through the list of teachers.
Just change
mTeacher =rs!["Teacher"]

Now the query runs once for each teacher.

Else where in the code do the export/email stuff.
--
Joe Fallon
Access MVP

Hello,

Right my problem is:
I have a database, two tables are "Students"
and "Tutors". Each day the database needs to send a email
to each tutor with their new students.
Now apart from making different queries for each tutor is
there efficient way of writing a SQL statement in VBA
that can write one query, change it, and email it for
each record in Tutor and only send them the students
assigned to them?

Hope you can help, thankyou in advance.

James

.