Looping through a recordset in Access 2003 to outlook

[NicholasE]

Hi,

I have a table with a list of data. For example:
Field:AcctNamControl DynamicTextControl2 TxnAmtControl
data: Nick IBM 23
Nick FutureShop 34
Tim Boss 34
Jen jhjj 3

I need to create sent an outlook email (already got that covered) that will send a list of all the name to that email:
eg: One email to contain all the records named Nick in one email etc.

So far,I have loop that pick up all the Nick and then places them into a variable (file1, file2), but cannot get the logic to have it cycle through the names. I know how to call a sub routine to place all the variables with the name to the email, but for it to cycle to the next name, pick up all the records, loop through all the records with the name is beyond me! If I can get this, I have the emails joined, so thats not a problem. Code:

Private Sub Command3_Click()
'declare all objects
Dim db As Database
Dim rs, tmp As Recordset
Dim count As Integer
Dim mfile As String
Dim mfile1 As String

  
Set tmp = CurrentDb.OpenRecordset("tblResults", dbOpenDynaset)

    tmp.MoveLast
    tmp.MoveFirst
    reccount = tmp.RecordCount
    
    'While not End Of File
    Do While tmp.EOF = False
    'Get the name from the table
    mname = tmp![AcctNamControl]
    mname = "nick" ' This will pick the name Nick
        
        Do While mname = tmp![Name]
        'Count
        count = count + 1
        'If there is one, place the fields into the variable mfile
        If count = 1 Then mfile = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 2 Then mfile1 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 3 Then mfile2 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 4 Then mfile3 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 5 Then mfile5 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 6 Then mfile6 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
        If count = 7 Then mfile7 = tmp![DynamicTextControl2] + " " + Str(tmp![TxnDtControl]) + " " + Str(tmp![TxnAmtControl])
     
        tmp.MoveNext
        
        If tmp.EOF Then GoTo EndOfRecord
    
        Loop
   
    tmp.MoveNext
    

    Loop
  
EndOfRecord:
MsgBox mfile
MsgBox mfile1
MsgBox mfile2
MsgBox mfile3
    
tmp.Close
Set tmp = Nothing

End Sub

And help of advise, would be appreciated!

 

[vb5prgrmr]

Perhaps formum707 might be better for this question...



Good Luck

 

[vb5prgrmr]

well if I could spell forum correctly forum707

 

[NicholasE]

Will Do ! Thanks!