Doing a "Select All" in Form

  • Thread starter Thread starter Chaplain Doug
  • Start date Start date
C

Chaplain Doug

I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:

Set rst = Me.Recordset
With rst
.MoveFirst
While Not .EOF
If Not ![Grad] Then
.Edit
![Selected] = True
.Update
End If
.MoveNext
Wend
.MoveFirst
.Close
End With
Me.Requery

When this code runs my continuous form moves down through
the records and ends with a blank screen. All I want it
to do is change the (selected) check box, but otherwise
leave the screen alone. How may I do this.
 
Don't close the rst recordset. When you do that, you also close the form's
recordset. Replace the line that says ".Close" near the end of the code with
"Set rst = Nothing".
 
Thanks Ken. This fixes the problem of ending with blank
screen. How may I also make it so that the display does
not cycle through the records, but rather remains fixed
and simply shows the changed check boxes when finished?
Thanks.



-----Original Message-----
Don't close the rst recordset. When you do that, you also close the form's
recordset. Replace the line that says ".Close" near the end of the code with
"Set rst = Nothing".


--
Ken Snell
<MS ACCESS MVP>

I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:

Set rst = Me.Recordset
With rst
.MoveFirst
While Not .EOF
If Not ![Grad] Then
.Edit
![Selected] = True
.Update
End If
.MoveNext
Wend
.MoveFirst
.Close
End With
Me.Requery

When this code runs my continuous form moves down through
the records and ends with a blank screen. All I want it
to do is change the (selected) check box, but otherwise
leave the screen alone. How may I do this.
Click to expand...


.
Click to expand...
 
I haven't tested it, but see whether

Application.Echo False

before you run your code, and

Application.Echo True

after works.

You might need a Repaint statement in there after you turn Echo back on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


chaplain doug said:
Thanks Ken. This fixes the problem of ending with blank
screen. How may I also make it so that the display does
not cycle through the records, but rather remains fixed
and simply shows the changed check boxes when finished?
Thanks.



-----Original Message-----
Don't close the rst recordset. When you do that, you also close the form's
recordset. Replace the line that says ".Close" near the end of the code with
"Set rst = Nothing".


--
Ken Snell
<MS ACCESS MVP>

I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:

Set rst = Me.Recordset
With rst
.MoveFirst
While Not .EOF
If Not ![Grad] Then
.Edit
![Selected] = True
.Update
End If
.MoveNext
Wend
.MoveFirst
.Close
End With
Me.Requery

When this code runs my continuous form moves down through
the records and ends with a blank screen. All I want it
to do is change the (selected) check box, but otherwise
leave the screen alone. How may I do this.
Click to expand...


.
Click to expand...
Click to expand...
 
It works. Thanks.
-----Original Message-----
I haven't tested it, but see whether

Application.Echo False

before you run your code, and

Application.Echo True

after works.

You might need a Repaint statement in there after you turn Echo back on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Thanks Ken. This fixes the problem of ending with blank
screen. How may I also make it so that the display does
not cycle through the records, but rather remains fixed
and simply shows the changed check boxes when finished?
Thanks.



-----Original Message-----
Don't close the rst recordset. When you do that, you
Click to expand...
also
close the form's
recordset. Replace the line that says ".Close" near the end of the code with
"Set rst = Nothing".


--
Ken Snell
<MS ACCESS MVP>

I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:

Set rst = Me.Recordset
With rst
.MoveFirst
While Not .EOF
If Not ![Grad] Then
.Edit
![Selected] = True
.Update
End If
.MoveNext
Wend
.MoveFirst
.Close
End With
Me.Requery

When this code runs my continuous form moves down through
the records and ends with a blank screen. All I want it
to do is change the (selected) check box, but otherwise
leave the screen alone. How may I do this.


.
Click to expand...
Click to expand...


.
Click to expand...
 
That may work.

Another option is to make the form's detail section invisible while the code
runs, and then make it visible again when done.

Or, a neat trick that I have seen posted by another MVP (don't recall who)
may be to use the RecordsetClone object and make the changes to it. It seems
that those changes then will be reproduced in the original Recordset. I
remember being very surprised that that could be done...but I admit that I
haven't tried it yet.

--
Ken Snell
<MS ACCESS MVP>


Douglas J. Steele said:
I haven't tested it, but see whether

Application.Echo False

before you run your code, and

Application.Echo True

after works.

You might need a Repaint statement in there after you turn Echo back on.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


chaplain doug said:
Thanks Ken. This fixes the problem of ending with blank
screen. How may I also make it so that the display does
not cycle through the records, but rather remains fixed
and simply shows the changed check boxes when finished?
Thanks.



-----Original Message-----
Don't close the rst recordset. When you do that, you also close the form's
recordset. Replace the line that says ".Close" near the end of the code with
"Set rst = Nothing".


--
Ken Snell
<MS ACCESS MVP>

I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:

Set rst = Me.Recordset
With rst
.MoveFirst
While Not .EOF
If Not ![Grad] Then
.Edit
![Selected] = True
.Update
End If
.MoveNext
Wend
.MoveFirst
.Close
End With
Me.Requery

When this code runs my continuous form moves down through
the records and ends with a blank screen. All I want it
to do is change the (selected) check box, but otherwise
leave the screen alone. How may I do this.


.
Click to expand...
Click to expand...
Click to expand...
 
Chaplain Doug said:
I have a form with student names and a select box. I am
trying to build a button that will select all the
nongraduates in the group. The code I use behind the
button is:
Click to expand...

Forget the code, be lazy, build a query that does this, run it with DoCmd
and then requery the form.
Do the same with a button that will deselect all the choices.

It may not be obvious in this case if the number of records are small, but
the speed difference is huge.

For a much more complex requirement I replaced looping through recordsets
with queries and saved the project (or my butt). From 10 - 20 seconds with
sample data to a second or less in the real world - and that included
opening a form if the end result had any records in it.
 
Back
Top