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

iterating over a form's records

P: n/a
Hi,

I'm a bit of an access-dummy, and i suppose this problem of mine has a
rather simple solution, but nevertheless i'm not sure i see it at the
moment.

I've got an access-form in datasheet-view and i want to iterate over
it's records and set some values according to our business logic. This
action should be triggered by the user via a button. I suppose i need
to access the record-set the form is based on doing something like:

With FormRecordSet
If .EOF = False Then
FormRecordSet("column1")="WhateverValueIWantToSetI tTo"
.MoveNext
End With

But, how do I access the form's record-set? And is there a better
solution to this problem?

Thanks for any input!

stephan

May 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
steph wrote in message
<11**********************@u72g2000cwu.googlegroups .com> :
Hi,

I'm a bit of an access-dummy, and i suppose this problem of mine has
a rather simple solution, but nevertheless i'm not sure i see it at
the moment.

I've got an access-form in datasheet-view and i want to iterate over
it's records and set some values according to our business logic.
This action should be triggered by the user via a button. I suppose i
need to access the record-set the form is based on doing something
like:

With FormRecordSet
If .EOF = False Then
FormRecordSet("column1")="WhateverValueIWantToSetI tTo"
.MoveNext
End With

But, how do I access the form's record-set? And is there a better
solution to this problem?

Thanks for any input!

stephan


I would probably rather execute an action query (update) with the same
filter or where clause as the form in stead. Then requery the form.

I wouldn't be surprised, though, if a couple of smallish alterations of
your code, would work - some air code

dim FormRecordSet as form
set FormRecordSet = me!frmSubFormControl.Form.Recordsetclone

With FormRecordSet
' an .eof test and .movefirst here?
Do While not .EOF
.Edit
.Fields("YourFieldName").Value = "WhateverValueIWantToSetItTo"
.Update
.MoveNext
Loop
' .movefirst ' ?
End With
set FormRecordSet = nothing

Note - the referencing when assigning the recordsetclone - you say this
is a datasheet, then I assume it is a subform, and that the button
you're pressing, is within a main form. Then you need to refer through
the main form, using the subform control name, which can differ from
the name in the database window.

--
Roy-Vidar
May 16 '06 #2

P: n/a
On Tue, 16 May 2006 17:23:24 +0200, RoyVidar
<ro*************@yahoo.no> wrote:

dim FormRecordset as DAO.Recordset.

Yes, I think a .MoveFirst is warranted at the top of the loop, because
if the subform is selected on a non-first record, the recordsetclone
will be starting there as well.

-Tom.

steph wrote in message
<11**********************@u72g2000cwu.googlegroup s.com> :
Hi,

I'm a bit of an access-dummy, and i suppose this problem of mine has
a rather simple solution, but nevertheless i'm not sure i see it at
the moment.

I've got an access-form in datasheet-view and i want to iterate over
it's records and set some values according to our business logic.
This action should be triggered by the user via a button. I suppose i
need to access the record-set the form is based on doing something
like:

With FormRecordSet
If .EOF = False Then
FormRecordSet("column1")="WhateverValueIWantToSetI tTo"
.MoveNext
End With

But, how do I access the form's record-set? And is there a better
solution to this problem?

Thanks for any input!

stephan


I would probably rather execute an action query (update) with the same
filter or where clause as the form in stead. Then requery the form.

I wouldn't be surprised, though, if a couple of smallish alterations of
your code, would work - some air code

dim FormRecordSet as form
set FormRecordSet = me!frmSubFormControl.Form.Recordsetclone

With FormRecordSet
' an .eof test and .movefirst here?
Do While not .EOF
.Edit
.Fields("YourFieldName").Value = "WhateverValueIWantToSetItTo"
.Update
.MoveNext
Loop
' .movefirst ' ?
End With
set FormRecordSet = nothing

Note - the referencing when assigning the recordsetclone - you say this
is a datasheet, then I assume it is a subform, and that the button
you're pressing, is within a main form. Then you need to refer through
the main form, using the subform control name, which can differ from
the name in the database window.


May 17 '06 #3

P: n/a
Hi,

Thanks for the replies. It worked as expected and I learned a bit, too.

regards,
stephan

May 17 '06 #4

P: n/a
"steph" <st*******@yahoo.de> wrote in
news:11*********************@y43g2000cwc.googlegro ups.com:
Thanks for the replies. It worked as expected and I learned a bit,
too.


But there's no reason to change data by looping through a form's
recordset, unless the changes you make are dependent on complex
considerations that aren't found in the record you're operating on.

This is a point that perhaps got lost in Roy's post, but I can't
stress it enough. Looping through recordsets is something that
should only seldom be done in order to update data. It's sequential
thinking, whereas the efficient method is thinking in sets of data
and using SQL to operate on those sets.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 17 '06 #5

P: n/a
I agree with you. But as I said before I'm not very proficient in
access, so I shouldn't leave out a chance to learn something, although
if it's through doing things not the way it ought to be.
For this small problem I had to solve here my solution is now
sufficient - for bigger operations i tend to do things directly in the
database (which is not an access-database).

May 17 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.