Loop/Repeat Code

[Tom Ventouris]

A2007

I need to run code while the record is displayed on a form, and must run
this for all records.

I have on the Click event of a button on the form:
DoCmd.GoToRecord , "", acFirst
Call MyFunction

It is MyFunction I need help with:

DoCmd 'Run some code and queries'
DoCmd.GoToRecord , "", acNext

Need help here to repeat/loop until the last record.

Thanks in advance.

 

[bubbles]

A2007

I need to run code while the record is displayed on a form, and must run
this for all records.

I have on the Click event of a button on the form:
DoCmd.GoToRecord , "", acFirst
Call MyFunction

It is MyFunction I need help with:

DoCmd 'Run some code and queries'
DoCmd.GoToRecord , "", acNext

Need help here to repeat/loop until the last record.

Thanks in advance.

Assuming you are still using DAO...you could open the recordset and
loop through the rows:

DIM db as database
DIM rs as recordset
Set db = currentdb

SET rs = db.OpenRecordset("myTable")
With rs
DO UNTIL .EOF
'Run some code
'If you need to update the current row then
.Edit
!myField = someValueIassign
.Update
.MoveNext
LOOP
END WITH
rs.close
set rs=nothing


Pay attention to the period in
.EDIT
.UPDATE
.MOVENEXT

HTH

Bubbles

 

[pietlinden]

A2007

I need to run code while the record is displayed on a form, and must run
this for all records.

I have on the Click event of a button on the form:
DoCmd.GoToRecord , "", acFirst
Call MyFunction

It is MyFunction I need help with:

DoCmd 'Run some code and queries'
DoCmd.GoToRecord , "", acNext

Need help here to repeat/loop until the last record.

Thanks in advance.

What exactly is MyFunction supposed to do? Just calculate some
values? Then base your form on a query and put the function(s) there
or use an unbound control to show the function result(s). Then it
would automatically recalculate in the OnCurrent event of the form...

 

[Tom Ventouris]

MyFunction selects and runs queries based on the content of fields on the
current record on the form, therefore the record must be in the active widow
for the queries to run.
Calculations would have been easy enough with general update queries.
Thanks for the suggestion.

 

[Pieter Wijnen]

Still no reason to use the form I think. You can run the code against the
underlying query

however you can do it like this

Sub LoopAndDo()
Dim RsC As DAO.Recordset

Set RsC = Me.RecordsetClone
RsC.MoveFirst
While Not RsC.EOF
Me.BookMark = RsC.bookmark
Call MyFunction()
RsC.MoveNext
Wend
Set RsC = Nothing
End Sub


HTH

Pieter

 

[Tom Ventouris]

Thanks, this does the job.

Under normal circumstances, a query would have done. This was a very unusual
set up for a most ubusual need. Neither normalisation nor query problems.

Thanks again.