Loop statement not working

[Guest]

Hi all,
thanks to help from Daniel P, I got the ground work for creating a loop
statement to be used to go through a select query and send an email to each
record. I have added code to set up the variables (and also believe this is
where the problem is) and here is the following code:
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Ship_Confirmation_Email")
If rst.RecordCount > 0 Then 'ensure there is data
rst.MoveFirst 'goto the 1st recordset
Do Until rst.EOF 'End of file
Dim sAddress As String
Dim STD As String
Dim TID As String
Dim ST As String
sAddress = DLookup("ShipTo_Email", "Ship_Confirmation_Email")
STD = DLookup("Ship_Type_Description", "Ship_Confirmation_Email")
TID = DLookup("Tracking_ID", "Ship_Confirmation_Email")
ST = DLookup("Ship_Times", "Ship_Confirmation_Email")
DoCmd.SendObject acSendNoObject, , , sAddress, , , "Order Shipment
Notification - Creations by You", "This email is to serve notice that your
Creations by You order has shipped!" _
& vbNewLine _
& vbNewLine _
& "It was sent via " + STD + " " + TID + " and will take
approximately " + ST + " for delivery.", False, ""
rst.MoveNext
Loop
Else
MsgBox "No Query Results To Process!", vbInformation
End If
The problem is that it will only read the first record of
Ship_Confirmation_Email - it will send the total number of emails that there
are records, but every one that comes is in is of the first record. Should I
not be using the DLookUp or should it be moved? I was hoping by moving the
variables into the loop statement it would then change for each record.
Please let me know where I messed up, thanks!
-gary

 

[Bob Quintal]

=?Utf-8?B?R2FyeSBEb2xsaXZlcg==?=

Hi all,
thanks to help from Daniel P, I got the ground work for creating a
loop statement to be used to go through a select query and send an
email to each record. I have added code to set up the variables
(and also believe this is where the problem is) and here is the
following code: Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Ship_Confirmation_Email")
If rst.RecordCount > 0 Then 'ensure there is data
rst.MoveFirst 'goto the 1st recordset
Do Until rst.EOF 'End of file
Dim sAddress As String
Dim STD As String
Dim TID As String
Dim ST As String
sAddress = DLookup("ShipTo_Email",
"Ship_Confirmation_Email") STD =
DLookup("Ship_Type_Description",
"Ship_Confirmation_Email") TID = DLookup("Tracking_ID",
"Ship_Confirmation_Email") ST = DLookup("Ship_Times",
"Ship_Confirmation_Email") DoCmd.SendObject
acSendNoObject, , , sAddress, , , "Order Shipment
Notification - Creations by You", "This email is to serve notice
that your Creations by You order has shipped!" _
& vbNewLine _
& vbNewLine _
& "It was sent via " + STD + " " + TID + " and will take
approximately " + ST + " for delivery.", False, ""
rst.MoveNext
Loop
Else
MsgBox "No Query Results To Process!", vbInformation
End If
The problem is that it will only read the first record of
Ship_Confirmation_Email - it will send the total number of emails
that there are records, but every one that comes is in is of the
first record. Should I not be using the DLookUp or should it be
moved? I was hoping by moving the variables into the loop
statement it would then change for each record. Please let me know
where I messed up, thanks! -gary

Your dLookups have no criteria parameter, so they will look up the
first record in the Ship_Confirmation_Email table or query.

You could just add the criteria and the code would work, but, since
you have the recordset open anyways, just use the value from the
recordset, and don't bother with the Dlookups.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Ship_Confirmation_Email")
If rst.RecordCount > 0 Then 'ensure there is data
' Since you just opened the recordset,
'it will be on the first record, no need to
'rst.MoveFirst 'goto the 1st recordset
Do Until rst.EOF 'End of file
DoCmd.SendObject acSendNoObject, , , _
rst!Shipto_Email, , , _
"Order Shipment Notification - Creations by You", _
"This email is to serve notice that your " _
& "Creations by You order has shipped!" _
& vbNewLine _
& vbNewLine _
& "It was sent via " _
& rst!Ship_Type_Description _
& " " + RST!Tracking_ID _
& " and will take approximately " _
& Rst!ship_times & " for delivery.", False, ""
rst.MoveNext
Loop
Else
MsgBox "No Query Results To Process!", vbInformation
End If

 

[Guest]

Thank you so much! Works great!
-gary

=?Utf-8?B?R2FyeSBEb2xsaXZlcg==?=


Your dLookups have no criteria parameter, so they will look up the
first record in the Ship_Confirmation_Email table or query.

You could just add the criteria and the code would work, but, since
you have the recordset open anyways, just use the value from the
recordset, and don't bother with the Dlookups.

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("Ship_Confirmation_Email")
If rst.RecordCount > 0 Then 'ensure there is data
' Since you just opened the recordset,
'it will be on the first record, no need to
'rst.MoveFirst 'goto the 1st recordset
Do Until rst.EOF 'End of file
DoCmd.SendObject acSendNoObject, , , _
rst!Shipto_Email, , , _
"Order Shipment Notification - Creations by You", _
"This email is to serve notice that your " _
& "Creations by You order has shipped!" _
& vbNewLine _
& vbNewLine _
& "It was sent via " _
& rst!Ship_Type_Description _
& " " + RST!Tracking_ID _
& " and will take approximately " _
& Rst!ship_times & " for delivery.", False, ""
rst.MoveNext
Loop
Else
MsgBox "No Query Results To Process!", vbInformation
End If