A loop not loading the correct value?

[techexpressinc]

I know my loop is working because the counter is working and I am getting three emails. The problem is the value from the table is staying with row 1. I do not know why the row 2 value is not being loaded into the varible and emailed to me. The value getting loaded into TxtSubject is staying with the value of row 1 not getting to the value of row 2, etc.

Please review the below code.

Thank you very much. Russ

[FONT=Times New Roman][SIZE=3]Private Sub MFNDTEMAILBUTTON_Click()[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]'==The first part prepares a table that is equal to a record set from the query to be loop through[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Dim db As DAO.Database[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim rs As DAO.Recordset[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim intNumRecs As Integer[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]' The query loop below[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Set db = CurrentDb()[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]intNumRecs = 0[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set rs = db.OpenRecordset("Q1230NDT3RDNOTICE")[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]While Not rs.EOF[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  ' do what has to be done[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  intNumRecs = intNumRecs + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  rs.MoveNext[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Wend[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Set rs = Nothing[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]'==This section provides the configuration information for the remote SMTP server.[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'==Normally you will only change the server name or IP.[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]'== the call is items are toemail fromemail subj body[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Dim Txtbody1 As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim TxtSubject As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim Txtbody2 As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim Txtbodyall As String[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Dim Response As Integer[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]Txtbody2 = "Notice of Confidentiality: **"[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Txtbody1 = " Test"[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Txtbodyall = "Date Received - " & Txtbody1 & " - " & Txtbody2[/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3]Response = MsgBox("Please confirm the emailing of " & intNumRecs & " solicitations?", vbYesNo + vbQuestion, "Send by Email?")[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]If Response = vbYes Then[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman]  'user answered "Yes"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  intNumRecs = 0[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Set rs = db.OpenRecordset("Q1230NDT3RDNOTICE")[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  While Not rs.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      ' do what has to be done[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      intNumRecs = intNumRecs + 1[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]      TxtSubject = Q1230NDT3RDNOTICE!IDCoName_2010TDB[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      Call Xmail("Russell.neuman@lfg.com", "Russell.Neuman@lfg.com", TxtSubject, Txtbodyall)[/FONT][/SIZE]
 
[SIZE=3][FONT=Times New Roman]      rs.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Wend[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  Set rs = Nothing[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  MsgBox "The number of NDT solicitation(s) emailed was: " & intNumRecs & ".", vbOKOnly, "Confirmation...  "[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]Else[/SIZE][/FONT]
[SIZE=3][FONT=Times New Roman] 'user answered "No"[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]  MsgBox "This NDT was Not emailed.", vbOKOnly, "Confirmation...  "[/FONT][/SIZE]
[FONT=Times New Roman][SIZE=3]End If[/SIZE][/FONT]
 
[FONT=Times New Roman][SIZE=3]End Sub[/SIZE][/FONT]

 

[techexpressinc]

RESOLVED now loading the correct value

instead of this ... TxtSubject = Q1230NDT3RDNOTICE!IDCoName_2010TDB

use this TxtSubject = rs!IDCoName_2010TDB

Russ