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!
  • Students Click Here

*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.

Students Click Here


Looping through a recordset in Access 2003 to outlook

Looping through a recordset in Access 2003 to outlook

Looping through a recordset in Access 2003 to outlook


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)

    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 + 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])
        If tmp.EOF Then GoTo EndOfRecord

MsgBox mfile
MsgBox mfile1
MsgBox mfile2
MsgBox mfile3
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

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!

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