Problems with looping

[aford]

Hi all, still very much a newbie here and i'm trying to learn as i go.

Here's my issue, i'm trying to create an email with list of numbers in the email body which come from field strEquipNum in temp table ttblIxReview

To begin with i was doing this from a continuous form. I could get the email to open with data in the body but the same data was being repeated and wasn't looping through the table. I figured out that was because of the form. So i tried putting a button on a single form and using the temp table as a recordset and now i can't get it to find the table. To my untrained newbie eye it looks like it should work but obviously it isn't and i can't see/ have no idea what it is i'm not doing right.

Thank you for looking, any pointers would be greatly appreciated.

Code below-

Private Sub Command29_Click()

Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set rst = db.OpenRecordset("ttblIxReview")

Set MyMail = MyOutlook.CreateItem(olMailItem)

Do Until rst.EOF

MyMail.Body = [strEquipNum]

rst.MoveNext

Loop

MyMail.Display

Set MyOutlook = Nothing

Set rst = Nothing
Set db = Nothing

End Sub

 

[JANR]

MyMail.Body = rst!strEquipNum

JR

 

[aford]

MyMail.Body = rst!strEquipNum

JR

Thanks for replying so quick, i tried this and it got me something . It put in the last entry in the table. The table has two entries. So now i'm guessing my loop isn't right.

 

[spikepl]

#3 Your code does exactly what you asked it to do You just make it do not quite the right thing .

The best way to debug this is to use the debuggert, then you can see what the code does step by step and also inspect the values of the variables. For a how to, see this post: http://www.access-programmers.co.uk/forums/showthread.php?p=705111#post705111

 

[aford]

Thanks Spike, have that page open and am about to start reading. I like to try and figure stuff out myself but sometimes you just come up blank. I do wish i could get my work to put me through some kind of training but don't see that happening

 

[spikepl]

IF you get stuck then yell. But I think it is valuable to learn how to use the debugger - it will save you oodles of grief in the future.

 

[aford]

IF you get stuck then yell. But I think it is valuable to learn how to use the debugger - it will save you oodles of grief in the future.

Once again thanks, i never knew about that function in VBA, i'm definitely going to be making good use of that.

I'm stepping through the code now and you are right. My code is working as i told it to, couldn't see that before. The loop is working correctly, i added a third record to the table and it is looping through all three, but just displaying the last record in the loop in the email body.

Now to go search for answers now i have a better idea what the problem is.

I'll be back later if i can't find a fix or if i do i'll post what the fix was.

 

[boblarson]

What you need is to change this:

MyMail.Body = [strEquipNum]

to this:

MyMail.Body = MyMail.Body & [strEquipNum] & ", "

and then after the loop change

If Right(MyMail.Body, 2) = ", " Then
   MyMail.Body = Left(MyMail.Body, Len(MyMail.Body) - 2)
End If

But I would use a variable to get the body string and then assign it to MyMail.Body ONE TIME instead of using it so many times.

 

[aford]

So after a little thought this code line ended up working

MyMail.Body = MyMail.Body + rst!strEquipNum

Haven't tried Bob's suggestions yet. That will be the afternoon project.

Thanks for all the help on this.