INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Looping through a recordset in Access 2003 to outlook

Looping through a recordset in Access 2003 to outlook

(OP)
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:
 

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!

RE: Looping through a recordset in Access 2003 to outlook

Perhaps formum707 might be better for this question...



Good Luck

 

RE: Looping through a recordset in Access 2003 to outlook

(OP)
Will Do ! Thanks!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close