End-of-file test while using form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

For a Recordset, the following is a valid construct testing for EOF

Do While Not rs.EO

Is there an equivalent Do…Loop construct for a Form

ctda
 
ctdak said:
For a Recordset, the following is a valid construct testing for EOF:

Do While Not rs.EOF

Is there an equivalent Do.Loop construct for a Form?

ctdak
Click to expand...

If the form allows additions, then you can test to see whether the
form's NewRecord property is True. For example, I have upon occasion
written code like this:

Private Sub Form_Current()

If Me.NewRecord Then
' do something special ...
End If

End Sub

Although you can use DoCmd.GoToRecord to start at a form's first record
and proceed sequentially through all the records until Me.NewRecord
becomes True, I can't think of any situation where I would do that. If
I wanted to process all the records displayed by a form, I would use a
recordset or an action query to deal with them, and then, if necessary,
refresh or requery the form.
 
No, this is not ambiguous. But you are correct that both give the same result
ctda

----- Bas Cost Budde wrote: ----

ctdak wrote
Do While Not rs.EO
Click to expand...

For the record, (umm, that is ambiguous here) While Not is equivalent to
Unti
 
Thanks. I appreciate your response. I assume this means that the following would work as a way of processing all records in a form, correct?

Do Until Me.Newrecord = Tru

I'm probably not as comfortable as you working with recordsets and queries, and frankly I can't see the point of what you said, but then I'm a relative novice with VBA. Why create a query or recordset out of a form that already has the records and fields you need to sequentially process? It seems to me that your suggestion would take more code to do the same thing. I can easly use the DoCmd.GoToRecord with acFirst, then acNext in a loop, to do what I need. Seems straighforward and minimal code to me

ctda

----- Dirk Goldgar wrote: ----

ctdak said:
For a Recordset, the following is a valid construct testing for EOF
Click to expand...

If the form allows additions, then you can test to see whether th
form's NewRecord property is True. For example, I have upon occasio
written code like this

Private Sub Form_Current(

If Me.NewRecord The
' do something special ..
End I

End Su

Although you can use DoCmd.GoToRecord to start at a form's first recor
and proceed sequentially through all the records until Me.NewRecor
becomes True, I can't think of any situation where I would do that. I
I wanted to process all the records displayed by a form, I would use
recordset or an action query to deal with them, and then, if necessary
refresh or requery the form
 
ctdak said:
Thanks. I appreciate your response. I assume this means that the
following would work as a way of processing all records in a form,
correct?:

Do Until Me.Newrecord = True
Click to expand...

Coupled with

DoCmd.GoToRecord , , acNext

or

RunCommand acCmdRecordsGoToNext

inside the loop, it would.
I'm probably not as comfortable as you working with recordsets and
queries, and frankly I can't see the point of what you said, but then
I'm a relative novice with VBA. Why create a query or recordset out
of a form that already has the records and fields you need to
sequentially process?
Click to expand...

Why put the application through all the overhead of graphically
representing your movement through the form's recordset by redisplaying
the form and all its controls each time you go to another record? It
will take much longer, showing the user things he doesn't need to see
and mustn't interfere with.
It seems to me that your suggestion would take
more code to do the same thing.
Click to expand...

Not really. The form has a RecordsetClone method that returns a copy of
its recordset, so you can if you wish perform your operations on that:

With Me.RecordsetClone
.MoveFirst
Do Until .EOF
' ... here's where you do something with the
' fields in the recordset ...
.MoveNext
Loop
End With

Me.Refresh ' or Requery, if you deleted records
I can easly use the DoCmd.GoToRecord
with acFirst, then acNext in a loop, to do what I need. Seems
straighforward and minimal code to me.
Click to expand...

The code seems to me to be about equal in extent and complexity. On the
other hand, if there's some update you want to perform on all the
records in the form's recordsource, then it may well be that a single
action query will do the job:

CurrentDb.Execute _
"UPDATE tblMyTable SET SomeField = SomeExpression " & _
"WHERE (SomeCriteria)", _
dbFailOnError

Me.Requery

Wherever it is suitable to the job, such an action query will be much
quicker and more efficient than looping through records in the form or
in a recordset.
 
----- Dirk Goldgar wrote: ----

ctdak said:
Thanks. I appreciate your response. I assume this means that th
following would work as a way of processing all records in a form
correct?
Click to expand...

Coupled wit

DoCmd.GoToRecord , , acNex

o

RunCommand acCmdRecordsGoToNex

inside the loop, it would
I'm probably not as comfortable as you working with recordsets an
queries, and frankly I can't see the point of what you said, but the
I'm a relative novice with VBA. Why create a query or recordset ou
of a form that already has the records and fields you need t
sequentially process
Click to expand...

Why put the application through all the overhead of graphicall
representing your movement through the form's recordset by redisplayin
the form and all its controls each time you go to another record? I
will take much longer, showing the user things he doesn't need to se
and mustn't interfere with

Dirk

I'm not putting the application through graphical overhead. I didn't mention it before, but the forms that I'm sequentially processing are hidden while this is happening. The user doesn't know or see what's going on and can't interfere with what's happening. Is doing things this way considered to be generally poor VBA programming practice? Since I'm new with VBA I don't know, but it's working just fine for me

ctda
 
ctdak said:
----- Dirk Goldgar wrote: -----


the > following would work as a way of processing all records in
a form, > correct?:

Coupled with

DoCmd.GoToRecord , , acNext

or

RunCommand acCmdRecordsGoToNext

inside the loop, it would.

and > queries, and frankly I can't see the point of what you
said, but then > I'm a relative novice with VBA. Why create a
query or recordset out > of a form that already has the records
and fields you need to > sequentially process?

Why put the application through all the overhead of graphically
representing your movement through the form's recordset by
redisplaying the form and all its controls each time you go to
another record? It will take much longer, showing the user
things he doesn't need to see and mustn't interfere with.

Dirk,

I'm not putting the application through graphical overhead. I didn't
mention it before, but the forms that I'm sequentially processing are
hidden while this is happening. The user doesn't know or see what's
going on and can't interfere with what's happening. Is doing things
this way considered to be generally poor VBA programming practice?
Since I'm new with VBA I don't know, but it's working just fine for
me.
Click to expand...

I would have to set of benchmarks to demonstrate it, but I believe that
you *are* putting the application through graphical overhead, even
though the user isn't seeing it because the forms are hidden. It's
clear that a whole lot of things must happen behind the scenes, in the
normal way of things, for a form (a graphical object) to move from one
record to the next. Now, since the form itself must use a recordset of
its own to manipulate the records it is displaying, anything that a form
does besides the basic Recordset.MoveNext must be in addition to the
processing handled by the recordset. It *may* be that almost all of
that is suppressed when the form is not visible, but I wouldn't count on
it, nor is it even possible that all of it is, since there must at the
bare minimum be the extra layer of handling between the DoCmd.GoToRecord
call (made to the application, not the form), and the internal
Recordset.MoveNext call.

There's no question but that looping through the records via
DoCmd.GoToRecord will work. Is it poor programming practice? I don't
set myself up as an arbiter of good or bad practice, so it's not for me
to say. I don't think it's harmful in any way; I just think it's
wasteful. I hope you aren't opening forms in hidden mode just so that
you can loop through their recordsets via DoCmd.GoToRecord! That would
reflect the (IMO) faulty belief that a form is a container of data,
rather than just a window on the data that is contained in the tables.
 
Back
Top