How do I loop through records

  • Thread starter Thread starter Anderson
  • Start date Start date
A

Anderson

What is the VBA format/code for looping through records. This is what I
am trying to do.

I have a table called appointments. A student can have as many
appointments. This table has among other fields change date, reason and
grade.

I want to write a funnction which says:

If change date > "certain date" and reason = "something new" and grade
different from the previous record then out put something.


How can I structure and write this please.?
 
hi Anderson,
What is the VBA format/code for looping through records.
Click to expand...

Dim rs As ADO.Recordset
Dim grade As Long

Set rs = New ADO.Recordset
rs.Open "SELECT * FROM appointments", _
CurrentProject.Connection, _
adOpenDynamic, _
adLockOptimistic

grade = 0
Do While Not rs.EOF
If rs![change date] > "certain date" _
And rs![reason] = "something new" _
And rs![grade] > grade Then
End If
grade = rs![grade]
rs.MoveNext
Loop

rs.Close
Set rs = Nothing

mfG
--> stefan <--
 
Actually, it is not at all clear what you are trying to do.

Generally, you can build report, and that report can display all the
records for one student. And, those records can be two related tables.

And, you can get a total, or grouping for each student..and then the report
will move on to the next student.
I want to write a funnction which says:

If change date > "certain date" and reason = "something new" and grade
different from the previous record then out put something.
Click to expand...

Out put to where? Another table? Another file? A report? A message on the
screen?

Normally, we don't need to loop the data..and simply can use sql:

dim strWhere as string

strwhere = "(ChangeDate > #12/01/2004#) and (Reason = 'Something new')"

docmd.OpenReport "yourReport",acViewPreview,,strWhere

So, the above shows a filter to a report. The reports "grouping" would thus
output data for each student, or you could group on grade..and thus have
output occur for each grade. We don't as a rule need to use code here.

It not quite clear what are trying to accomplish here.

However, the basic code to traverse data in a loop is:

Dim rst As dao.Recordset

Set rst = CurrentDb.OpenRecordset("select * from contacts")
Do While rst.EOF = False
Debug.Print rst!FirstName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

The above would print all first names from the table contacts to the debug
window.
 
Back
Top