How to re-use recordSetClone without closing/opening form 1

[missinglinq]

Been so long since I asked a question, took me 5 minutes to find the 'new thread' place!

This works to tick CheckboxField on all records of a form:

Private Sub cmdTickAllBoxes_Click()

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

 With rs

 Do While Not rs.EOF
  rs.Edit
  rs!CheckboxField = -1
  rs.Update
  rs.MoveNext
 Loop

End With

rs.Close

Set rs = Nothing

Me.Requery

End Sub

And this works to untick CheckboxField on all records of a form:

Private Sub cmdUntickAllBoxes_Click()
 
 Dim rs As DAO.Recordset
 
 Set rs = Me.RecordsetClone

 With rs

 Do While Not rs.EOF
  rs.Edit
  rs!CheckboxField = 0
  rs.Update
  rs.MoveNext
 Loop

End With

rs.Close

Set rs = Nothing

Me.Requery

End Sub

But you can only do one of these operations unless you close then re-open the Form. In other words you cannot 'tick all' and then immediately 'untick all.' I thought that

rs.Close

Set rs = Nothing

would allow the RecordsetClone to be recreated again, but apparently not.

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[PHV]

...
With rs
[!] .MoveFirst[/!]
Do While Not .EOF
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

I knew it was something simple! Thanks for the quick replay; I've been pulling my hair our, and at 64 cannot really afford to lose more than I already have!

Linq ;0)>

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[Andrzejek]

A good way to show the appreciation to the person who help you is to click on
[blue]
Like this post?
Star it![/blue]

This way PHV gets the star, and everybody else will see that this was a helpful answer.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.