Looping through records

B

Bill

In the codesheet of a form whose RecordSource
property is defined as a table or query, can't that
VBA code reference the descrete records in that
RecordSource?

Something like:
While Not Me.EOF
....
....
.....
Next
Wend

Where it is assumed that the current record is the
first record returned by the query.

Me.EOF is flagged as an error by the compiler.

OR

Does one have to SQL select "to a Recordset".

Thanks,
Bill
 
K

Ken Snell [MVP]

Use the RecordsetClone of the form to search through the records in the
form's Recordset (not RecordSource -- RecordSource is a string that
identifies the query that selects the records).

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
' code here to search a record
'
.MoveNext
Loop
End With
 
B

Bill

PERFECT!
Thank you Ken,
Bill


Ken Snell said:
Use the RecordsetClone of the form to search through the records in the
form's Recordset (not RecordSource -- RecordSource is a string that
identifies the query that selects the records).

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
' code here to search a record
'
.MoveNext
Loop
End With
Click to expand...
 
B

Bill

Ken,
I'm unable to edit fields when making field references
within the Me.RecordsetClone. E.g., .fieldname = true
What do I need to do in order to make field content
changes?
Bill
 
A

Albert D.Kallal

Bill said:
Ken,
I'm unable to edit fields when making field references
within the Me.RecordsetClone. E.g., .fieldname = true
What do I need to do in order to make field content
changes?
Bill
Click to expand...

With Me.RecordsetClone
.MoveFirst
Do While .EOF = False
' code here to search a record
if !City = "N. Y" then
.edit
!City = "New York"
.update
end if
.MoveNext
Loop
End With

You have to use the .edit method, and also the "update" method to modify the
data....

The above would change all City from N. Y to New York...
 
B

Bill

Thank you Albert, I searched entirely in the wrong places
in Help for that answer.

Hope it's not getting too cold in Canada!!!

Thanks again,
Bill
 
Top