iterating over a form's records

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

re: iterating over a form's records

steph wrote in message
<1147790302.878599.207390@u72g2000cwu.googlegroups .com> :[color=blue]
> 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[/color]

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

re: iterating over a form's records

On Tue, 16 May 2006 17:23:24 +0200, RoyVidar
<roy_vidarNOSPAM@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.

[color=blue]
>steph wrote in message
><1147790302.878599.207390@u72g2000cwu.googlegroup s.com> :[color=green]
>> 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[/color]
>
>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.[/color]

re: iterating over a form's records

Hi,

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

regards,
stephan

re: iterating over a form's records

"steph" <stephan0h@yahoo.de> wrote in
news:1147848989.714047.28210@y43g2000cwc.googlegro ups.com:
[color=blue]
> Thanks for the replies. It worked as expected and I learned a bit,
> too.[/color]

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/

re: iterating over a form's records

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).