By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,193 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

How to define a loop

P: n/a
Hello all,
Could anybody give me the correct syntax for defining a loop using the
form's recordset for as long as it is not at the end of the table ?

example:
Do While not EOF ' this is the part I don't know
commands ....
.....
.....
Loop

Thanks .....
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a


Ronny Sigo wrote:
Hello all,
Could anybody give me the correct syntax for defining a loop using the
form's recordset for as long as it is not at the end of the table ?

example:
Do While not EOF ' this is the part I don't know
commands ....
.....
.....
Loop

Thanks .....


Dim rst As Recordset
Set rst = Currentdb.Openrecordset("MyTable",dbopendynaset)
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF
....process
rst.MoveNext
Loop
Endif
rst.close
set rst = Nothing

Nov 12 '05 #2

P: n/a
Hello Salad ...
Thanks for responding ...
I tried it as you suggested
Now I get the error message "Invalid argument" at the line where I put the
***********
tblFoutmeldingen does exist ....
Here is the snippet ....

Dim rst As Recordset
Set rst = CurrentDb.Openrecordset("tblFoutmeldingen", dbopendynaset)
***********
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
If [Karel] = True Then
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
End If
If [Ronny] = True Then
ronnylogic = True
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing

"Salad" <oi*@vinegar.com> schreef in bericht
news:3F***************@vinegar.com...


Ronny Sigo wrote:
Hello all,
Could anybody give me the correct syntax for defining a loop using the
form's recordset for as long as it is not at the end of the table ?

example:
Do While not EOF ' this is the part I don't know
commands ....
.....
.....
Loop

Thanks .....


Dim rst As Recordset
Set rst = Currentdb.Openrecordset("MyTable",dbopendynaset)
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF
....process
rst.MoveNext
Loop
Endif
rst.close
set rst = Nothing

Nov 12 '05 #3

P: n/a
You might be missing a reference to a DAO library. Go to Tools->References,
see if DAO is checked.

"Ronny Sigo" <ro********@skynet.be> wrote in message
news:3f**********************@feed0.news.be.easyne t.net...
Hello Salad ...
Thanks for responding ...
I tried it as you suggested
Now I get the error message "Invalid argument" at the line where I put the
***********
tblFoutmeldingen does exist ....
Here is the snippet ....

Dim rst As Recordset
Set rst = CurrentDb.Openrecordset("tblFoutmeldingen", dbopendynaset)
***********
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
If [Karel] = True Then
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
End If
If [Ronny] = True Then
ronnylogic = True
End If
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing

"Salad" <oi*@vinegar.com> schreef in bericht
news:3F***************@vinegar.com...


Ronny Sigo wrote:
Hello all,
Could anybody give me the correct syntax for defining a loop using the
form's recordset for as long as it is not at the end of the table ?

example:
Do While not EOF ' this is the part I don't know
commands ....
.....
.....
Loop

Thanks .....


Dim rst As Recordset
Set rst = Currentdb.Openrecordset("MyTable",dbopendynaset)
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF
....process
rst.MoveNext
Loop
Endif
rst.close
set rst = Nothing


Nov 12 '05 #4

P: n/a
Ronny Sigo wrote:
Hello Salad ...
Thanks for responding ...
I tried it as you suggested
Now I get the error message "Invalid argument" at the line where I put the
***********
tblFoutmeldingen does exist ....
Here is the snippet ....

Dim rst As Recordset
Set rst = CurrentDb.Openrecordset("tblFoutmeldingen", dbopendynaset)
***********


As Randy stated, you may be using ADO instead of DAO. You could set your
references or enter
Dim rst As DAO.Recordset

Otherwise the table name may be misspelled.
Nov 12 '05 #5

P: n/a
Hello all,
The DAO thing proved to be true, as you both said, and it also showed in the
help files. So I referenced it and now I don't get this error anymore. So
this is solved but .... now at the same line I get another errormssg saying
"Types don't match" (or something similar, 'cause the actual error message
is in Dutch) ....

Can you see why ? any help very much appreciated ....
Thanks,
Ronny
"Salad" <oi*@vinegar.com> schreef in bericht
news:3F***************@vinegar.com...
Ronny Sigo wrote:
Hello Salad ...
Thanks for responding ...
I tried it as you suggested
Now I get the error message "Invalid argument" at the line where I put the ***********
tblFoutmeldingen does exist ....
Here is the snippet ....

Dim rst As Recordset
Set rst = CurrentDb.Openrecordset("tblFoutmeldingen", dbopendynaset)
***********


As Randy stated, you may be using ADO instead of DAO. You could set your
references or enter
Dim rst As DAO.Recordset

Otherwise the table name may be misspelled.

Nov 12 '05 #6

P: n/a
"Ronny Sigo" <ro********@skynet.be> wrote in message
news:3f**********************@feed0.news.be.easyne t.net...
Hello all,
The DAO thing proved to be true, as you both said, and it also showed in the help files. So I referenced it and now I don't get this error anymore. So
this is solved but .... now at the same line I get another errormssg saying "Types don't match" (or something similar, 'cause the actual error message
is in Dutch) ....

Can you see why ? any help very much appreciated ....
Thanks,
Ronny


Ronny
Your original question asks about a form's recordset which could be written
differently - e.g.
Private Sub cmdTest1_Click()

On Error GoTo Err_Handler

With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveFirst
While Not .EOF
Debug.Print Nz(.Fields(2), "")
.MoveNext
Wend
End If
End With

MsgBox "Done", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

but a more generalised DAO loop might look more like this:
Private Sub cmdTest2_Click()

On Error GoTo Err_Handler

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

strSQL = "SELECT tblCompanies.CoName FROM tblCompanies " & _
"ORDER BY tblCompanies.CoName"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL)

While Not rst.EOF
Debug.Print Nz(rst!CoName, "")
rst.MoveNext
Wend

MsgBox "Done", vbInformation

Exit_Handler:

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

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

Exit Sub

Err_Handler:

Select Case Err.Number

'Handle specified errors here:

Case Else
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Select

End Sub



Nov 12 '05 #7

P: n/a
"Pink Panther" <Pi*************@mail.com> wrote in message
news:ec**************************@posting.google.c om...
Salad,

As a matter of interest why
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF
....process
rst.MoveNext
Loop
Endif


why not just
Do while not rst.EOF
....process
rst.MoveNext
Loop


?

Does the former avoid some kind of potential problem that I'm unaware of?

Regards,

Peter

Since the post started on the subject of a form's recordset, then it's
possible that the code contains

Set rst = Me.RecordsetClone

so that the recordset is based on the form's RecordsetClone. If this is the
case, then you may well not be at the beginning of a recordset - so in order
to loop through all the records you need to call MoveFirst. However, if the
form's underlying recordsource returns no records, or perhaps the user has
applied a filter to the form which returns no records, then this MoveFirst
will cause a 'no current record' runtime error. Therefore checking the
record count before calling MoveFirst both seem sensible steps.

If, however, the code contains

Set rst = dbs.OpenRecordset(strSQL)

There is no need to call MoveFirst and you can get straight on with While
Not rst.EOF or whatever.
HTH
Fletcher
Nov 12 '05 #8

P: n/a
Hello Arnold,
I used your "more generalised DAO loop", as you suggested. I get no error
messages anymore but ...
This thing serves to automatically generate an email message with the
contents of the tabls in it ...
By each rst.Move.Next, the sBody variable is updated with the old value +
the new one, finally forming the body of the emailmessage.
By using your loop, the email msg displays the correct number of lines
(according to the numer of records in the table - 9 at present (testing)).
The weird thing is that all 9 lines are displaying the values of the first
record, so I get 9 lines exactly the same ..... this beats me .....
Here is the code .... can you see why it does that ?

Private Sub btnStuurMails_Click()
Forms!frmBekijkFoutmeldingen.Requery
Dim nLast As Long
Dim Email As String, Subject As String, sBody As String, attach As String
Dim cc As String, bcc As String

Subject = "Foutmelding(en)"
Email = "so*****@someprovider.be"
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
'DoCmd.GoToRecord , , acFirst
tellerke = 0
Dim ronnylogic As Boolean
ronnylogic = False

' Here comes Fletcher's part ...

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

strSQL = "SELECT * FROM tblFoutmeldingen"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
If [Karel] = True Then
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
End If
If [Ronny] = True Then
ronnylogic = True
End If
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, you'll use the same instance...
Dim olApp As Object
'Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

' Logon. Doesn't hurt if you are 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 your new contact.
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
' Fill out & send message...
'olMail.To = olItem.Email1Address
olMail.To = "so*****@someprovider.be"
olMail.Subject = "Foutmelding(en)"
olMail.body = sBody

olMail.Display

'olMail.Send

' Clean up...
Set olNs = Nothing
Set olMail = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing
End Sub
Nov 12 '05 #9

P: n/a
Salad,

As a matter of interest why
If rst.Recordcount > 0 then
rst.MoveFirst
Do while not rst.EOF
....process
rst.MoveNext
Loop
Endif
why not just
Do while not rst.EOF
....process
rst.MoveNext
Loop


?

Does the former avoid some kind of potential problem that I'm unaware of?

Regards,

Peter
Nov 12 '05 #10

P: n/a
"Ronny Sigo" <ro********@skynet.be> wrote in message
news:3f**********************@feed0.news.be.easyne t.net...
Hello Arnold,
I used your "more generalised DAO loop", as you suggested. I get no error
messages anymore but ...
This thing serves to automatically generate an email message with the
contents of the tabls in it ...
By each rst.Move.Next, the sBody variable is updated with the old value +
the new one, finally forming the body of the emailmessage.
By using your loop, the email msg displays the correct number of lines
(according to the numer of records in the table - 9 at present (testing)).
The weird thing is that all 9 lines are displaying the values of the first
record, so I get 9 lines exactly the same ..... this beats me .....
Here is the code .... can you see why it does that ?

Private Sub btnStuurMails_Click()
Forms!frmBekijkFoutmeldingen.Requery
Dim nLast As Long
Dim Email As String, Subject As String, sBody As String, attach As String Dim cc As String, bcc As String

Subject = "Foutmelding(en)"
Email = "so*****@someprovider.be"
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
'DoCmd.GoToRecord , , acFirst
tellerke = 0
Dim ronnylogic As Boolean
ronnylogic = False

' Here comes Fletcher's part ...

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

strSQL = "SELECT * FROM tblFoutmeldingen"

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset(strSQL)

Do While Not rst.EOF
If [Karel] = True Then
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
End If
If [Ronny] = True Then
ronnylogic = True
End If
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, you'll use the same instance...
Dim olApp As Object
'Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")

' Logon. Doesn't hurt if you are 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 your new contact.
Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)
' Fill out & send message...
'olMail.To = olItem.Email1Address
olMail.To = "so*****@someprovider.be"
olMail.Subject = "Foutmelding(en)"
olMail.body = sBody

olMail.Display

'olMail.Send

' Clean up...
Set olNs = Nothing
Set olMail = Nothing
Set olAppt = Nothing
Set olItem = Nothing
Set olApp = Nothing
End Sub


Ronny
Your post has only just appeared on my news server - I don't know why it was
so late. Perhaps you have solved this problem by now, but if not, you could
look at setting a breakpoint at the beginning of the code and use F8 to
step through line by line and see what is going on. You could also help
yourself by reducing the code to a simpler form to make de-bugging easier.
For example, try this version:

While Not rst.EOF
str = str & Nz(rst!Beschrijving, "") & vbCrLf
rst.MoveNext
Wend

Msgbox str

Does that work? If so, slowly build up to your code. Possible problems:

If [Karel] = True Then ... check this out

[DatumMailKarel] = Date ... this looks like you are trying to edit the
recordset. You can't do that without writing rst.Edit beforehand.
PS My first name is Fletcher

Fletcher

Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.