By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,932 Members | 1,302 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,932 IT Pros & Developers. It's quick & easy.

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

P: 4
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCheckSA_Click()
  3. Dim rs As DAO.Recordset
  4. Set rs = Forms!FollowUpForm!FollowUpTableForm.Form.RecordsetClone
  5. If Not rs.EOF Then
  6. Do Until rs.EOF
  7. rs.Edit
  8. rs!CheckBox = True
  9. rs.Update
  10. rs.MoveNext
  11. Loop
  12. Forms![FollowUpForm]![FollowUpTableForm].Form.Refresh
  13. Me.Refresh
  14. End If
  16. Me!btncheckSA.SetFocus
  19. Set rs = Nothing
  22. End Sub
Thank you in advance for your help!
Jul 19 '10 #1
Share this Question
Share on Google+
6 Replies

P: 4
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?
Jul 19 '10 #2

Expert Mod 15k+
P: 31,769
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!
Jul 20 '10 #3

Expert Mod 15k+
P: 31,769
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.
Jul 20 '10 #4

P: 4
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! :)
Jul 20 '10 #5

P: 4
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
Jul 20 '10 #6

Expert Mod 15k+
P: 31,769
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.
Jul 20 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.