Loop does not reach the 2nd record

[Ronny Sigo]

Hello all,
I wrote a routine in order to generate an email wheroff the message body is
composed by the contents of the fields in a table. For the moment I have 9
reocrds in the table. The loop I wrote does this 9 times allright, but the 9
lines are always the contents of the first record, yet the 9 records sure
are different. I fail to see where I go wrong. Could sombody help me with
this please ? Very much appreciated ...
Here is the snippet :

Dim sBody As String

sBody = "Beste Karel," & vbCrLf & vbCrLf & _
"De volgende problemen werden mij gemeld. Weet jij hier een oplossing
voor?" & _
vbCrLf & "Met vriendelijke groeten," & vbCrLf & vbCrLf & _
"Steven Leman," & vbCrLf & _
"consulent ICT" & vbCrLf & vbCrLf

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblFoutmeldingen where [Karel] = True"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
sBody = sBody & "Apparaat: " & Trim([Apparaat]) & " Beschrijving: "
& Trim([Beschrijving]) & _
" Datum: " & [DatumMelding] & " Gebruiker: " &
Trim([Gebruiker]) & vbCrLf & _
Trim([Gebruiker]) & " probeerde dit te herstellen met volgende
handeling(en): " & Trim([OndernomenActie]) & vbCrLf & vbCrLf
[DatumMailKarel] = Date
rst.MoveNext
Loop

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If

If Not dbs Is Nothing Then
Set dbs = Nothing
End If



' Start Outlook.
' If it is already running, use the same instance...
Dim olApp As Object
'Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

' Logon. Doesn't hurt if already running and logged on...
Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon

'sFunctie = DLookup("[Functie]", "tblMwerkers", "[Volledigenaam] = '" &
sNaamschrijver & "'")
' Send a message to contact.
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
' Send message...
olMail.To = (e-mail address removed)"
olMail.Subject = "Foutmelding(en)"
olMail.body = sBody
olMail.Display
' Clean up...


Set olNs = Nothing
Set olMail = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing

 

[JSand42737]

Hello all,
I wrote a routine in order to generate an email wheroff the message body is
composed by the contents of the fields in a table. For the moment I have 9
reocrds in the table. The loop I wrote does this 9 times allright, but the 9
lines are always the contents of the first record, yet the 9 records sure
are different. I fail to see where I go wrong. Could sombody help me with
this please ? Very much appreciated ...
Here is the snippet :


strSQL = "SELECT * FROM tblFoutmeldingen where [Karel] = True"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
sBody = sBody & "Apparaat: " & Trim([Apparaat]) & " Beschrijving: "
& Trim([Beschrijving]) & _
" Datum: " & [DatumMelding] & " Gebruiker: " &
Trim([Gebruiker]) & vbCrLf & _
Trim([Gebruiker]) & " probeerde dit te herstellen met volgende
handeling(en): " & Trim([OndernomenActie]) & vbCrLf & vbCrLf
[DatumMailKarel] = Date
rst.MoveNext
Loop

Ronny

The problem is that although you are opening a recordset, you are not actually
using the data returned by it, instead you are using the data currently being
displayed in the form.

Try something like:

sBody=sBody " Apparaat: " & Trim(rst!Apparaat) & ........

Assuming that Apparaat is a field that appears in the recordset.

 

[Ronny Sigo]

Works great !!! Many thanks
Greets, Ronny

Hello all,
I wrote a routine in order to generate an email wheroff the message body is
composed by the contents of the fields in a table. For the moment I have 9
reocrds in the table. The loop I wrote does this 9 times allright, but the 9
lines are always the contents of the first record, yet the 9 records sure
are different. I fail to see where I go wrong. Could sombody help me with
this please ? Very much appreciated ...
Here is the snippet :


strSQL = "SELECT * FROM tblFoutmeldingen where [Karel] = True"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
sBody = sBody & "Apparaat: " & Trim([Apparaat]) & " Beschrijving: "
& Trim([Beschrijving]) & _
" Datum: " & [DatumMelding] & " Gebruiker: " &
Trim([Gebruiker]) & vbCrLf & _
Trim([Gebruiker]) & " probeerde dit te herstellen met volgende
handeling(en): " & Trim([OndernomenActie]) & vbCrLf & vbCrLf
[DatumMailKarel] = Date
rst.MoveNext
Loop

Ronny

The problem is that although you are opening a recordset, you are not actually
using the data returned by it, instead you are using the data currently being
displayed in the form.

Try something like:

sBody=sBody " Apparaat: " & Trim(rst!Apparaat) & ........

Assuming that Apparaat is a field that appears in the recordset.


--

Jon

www.applecore99.com - Access Tips and Tricks