Why does my code only work once when I open the form?

[Brad1451]

Hello,
Access version 2002, SP3
This is my first post ever, so please be kind. Problem: I have a command button that "selects all" check boxes in a subform which is based on a table(datasheet view). I have another button that deselects all check boxes. I can do one or the other, but only once without exiting the form and restarting it. In other words, I can select all, but i want the end user to be able to deselect in case the select all click was a mistake. Since the code is almost identical for both buttons, I will provide the select all code:

Private Sub btnCheckSA_Click()

Dim rs As DAO.Recordset
Set rs = Forms!FollowUpForm!FollowUpTableForm.Form.RecordsetClone
If Not rs.EOF Then
Do Until rs.EOF
rs.Edit
rs!CheckBox = True
rs.Update
rs.MoveNext
Loop
Forms![FollowUpForm]![FollowUpTableForm].Form.Refresh
Me.Refresh
End If

Me!btncheckSA.SetFocus


Set rs = Nothing


End Sub

Thank you in advance for your help!

[Brad1451]

Ok...I just noticed that it does work as long as I have the subform table filtered. This should be the functionality needed by the user. Can I somehow hide the buttons unless the list is filtered?

[NeoPa]

I suspect your first problem is because you leave the recordset (rs) set to EOF when you run the code the first time. Next time it checks for this and, finding it to be true, skips the code.

By the way, we do have rules, but we are certainly tolerant of newbies making a few innocent mistakes when starting out. As long as you're not a repeat offender we're happy to have you here. No worries.

Welcome to Bytes!

[NeoPa]

Brad1451: Can I somehow hide the buttons unless the list is filtered?

Certainly. Most controls (and certainly CommandButtons) have a .Visible property. Set and reset this in code when you start and when you apply or clear the filter.

[Brad1451]

Certainly. Most controls (and certainly CommandButtons) have a .Visible property. Set and reset this in code when you start and when you apply or clear the filter.

Since the user has no reason to select all of the records without filtering first, I went with the second approach. The select and deselect buttons work fine as long as the subform is filtered. So, I created a "filter by selection" button and a "remove filter" button. When the filter by selection button is pressed it unhides the select and deselect buttons. When the remove filter button is pressed it hides the select and deselect buttons.

Thanks a bunch for your help. I have read many of the forums for several years now and learned a great deal. My code might be ungly at times, but it gets the job done! :)

[Brad1451]

Certainly. Most controls (and certainly CommandButtons) have a .Visible property. Set and reset this in code when you start and when you apply or clear the filter.

One quick question, I noticed my code now shows up in a box with numbers and scroll bars. Did you do this? Should I have done this somehow when posting the code?

Thanks Again

[NeoPa]

Yes indeed :) Here is something I put together earlier.

When posting any code on here please :

1. For VBA code specifically :
   1. Ensure you have Option Explicit set (See Require Variable Declaration).
   2. Try to compile it. If it doesn't compile for any reason please explain that clearly - including the error message and which line of your code it appears on. Compilation is done from the Visual Basic Editor menu - Debug \ Compile Project (Where Project is the actual name of your project).
2. For SQL as well as VBA :
   1. Copy your code (using the Clipboard - Cut / Copy / Paste) from your project directly into your post. Typing in code is not appreciated as it is likely to introduce typos which cause members to waste their time unnecessarily.
   2. Ensure that the code in your post is enveloped within CODE tags. The hash (#) button in the posting page helps with this. Simply select your code and click on the hash button to have it enveloped automatically.

If all these points are covered then all members will be better able to understand, and therefore attempt to answer, your question.