G
Gerwin
Hello,
I have created a database in Access 2003 with a table of suppliers and a
table containing my orders with those suppliers. Now suppose I want to send
out lists with outstanding orders to those individual suppliers as a sort of
reminder, only containing their "own" orders. I have written following code:
---
Dim rs As Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers")
Do While Not rs.EOF
If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" &
rs.Fields(0) & "'") <> 0 Then
CurrentDb.QueryDefs("qryOutstandingList").SQL = "SELECT
qryOutstanding.[Purchase order] FROM qryOutstanding WHERE
qryOutstanding.SupplierID = '" & rs.Fields(0) & "';"
DoCmd.SendObject acSendQuery, "qryOutstandingList", acFormatXLS,
rs.Fields(1), , , "Outstanding purchase orders", "See attached", False
End If
rs.MoveNext
Loop
rs.Close
---
This works.
However I still have two questions:
1. Performance is rather poor. Is there a more efficient way to accomplish
the same?
2. For every e-mail created by this code, Outlook asks me to confirm sending
after a delay of five seconds as a security measure. For 500 e-mails that's
rather awkward. Is there a way to bypass this?
Thanks in advance,
Gerwin
I have created a database in Access 2003 with a table of suppliers and a
table containing my orders with those suppliers. Now suppose I want to send
out lists with outstanding orders to those individual suppliers as a sort of
reminder, only containing their "own" orders. I have written following code:
---
Dim rs As Recordset
Set rs = DBEngine(0)(0).OpenRecordset("tblSuppliers")
Do While Not rs.EOF
If DCount("[SupplierID]", "qryOutstanding", "[SupplierID] = '" &
rs.Fields(0) & "'") <> 0 Then
CurrentDb.QueryDefs("qryOutstandingList").SQL = "SELECT
qryOutstanding.[Purchase order] FROM qryOutstanding WHERE
qryOutstanding.SupplierID = '" & rs.Fields(0) & "';"
DoCmd.SendObject acSendQuery, "qryOutstandingList", acFormatXLS,
rs.Fields(1), , , "Outstanding purchase orders", "See attached", False
End If
rs.MoveNext
Loop
rs.Close
---
This works.
However I still have two questions:
1. Performance is rather poor. Is there a more efficient way to accomplish
the same?
2. For every e-mail created by this code, Outlook asks me to confirm sending
after a delay of five seconds as a security measure. For 500 e-mails that's
rather awkward. Is there a way to bypass this?
Thanks in advance,
Gerwin