For - Each - Next

C

Confused Slug

When a form is opened in datasheet view i want to change a check box status
from false to true for ALL records. I think i need to use the For Each Next
routine but i have failed.
I have only been able to change the check box on the first record (lack of
VBA knowledge on my part)

Any help appreciated
Thanks
 
S

Stefan Hoffmann

hi Slug,

Confused said:
When a form is opened in datasheet view i want to change a check box status
from false to true for ALL records. I think i need to use the For Each Next
routine but i have failed.
Click to expand...
No, this won't work. Try the following:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

If Not rs.Bof And Not rs.Eof Then
rs.First
Do While Not rs.Eof
rs.Edit
rs![CheckBoxFieldName] = True
rs.Update
rs.Next
Loop
End If

Set rs = Nothing


You can also try to modify the underlying data directly:

CurrentDb.Execute "UPDATE TableOrQuery SET Field = True"
Me.Requery

But this may be not applicable, if you have a complex record source with
a complex condition (filter).


mfG
--> stefan <--
 
C

Confused Slug

thanks stefan

sorted



Stefan Hoffmann said:
hi Slug,

Confused said:
When a form is opened in datasheet view i want to change a check box status
from false to true for ALL records. I think i need to use the For Each Next
routine but i have failed.
Click to expand...
No, this won't work. Try the following:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

If Not rs.Bof And Not rs.Eof Then
rs.First
Do While Not rs.Eof
rs.Edit
rs![CheckBoxFieldName] = True
rs.Update
rs.Next
Loop
End If

Set rs = Nothing


You can also try to modify the underlying data directly:

CurrentDb.Execute "UPDATE TableOrQuery SET Field = True"
Me.Requery

But this may be not applicable, if you have a complex record source with
a complex condition (filter).


mfG
--> stefan <--
Click to expand...
 
J

John Vinson

When a form is opened in datasheet view i want to change a check box status
from false to true for ALL records. I think i need to use the For Each Next
routine but i have failed.
I have only been able to change the check box on the first record (lack of
VBA knowledge on my part)

Any help appreciated
Thanks
Click to expand...

What's the Form's Recordsource? A Table, or a Query?

Bear in mind that the data - and the checkbox - is NOT stored in the
Form. It's stored in a Table. I suspect that you want to run an Update
query, updating the yes/no field in the table (or in the table via the
query which selects that subset of data displayed on the form) to
True.

I do have to wonder what's the point of this checkbox; it seems odd to
update potentially thousands of records every time you just open a
form!

John W. Vinson[MVP]
 
Top