Select Deselect Cont Form - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Select Deselect Cont Form

Brian Crawford

3 years ago

This seems like a question that should already be covered in a lesson, but I can't find it.

I have a continuous form with a list of records filtered to a condition so that the user can decide whether or not to perform an action on them.  At the beginning of each row, I have a check box for the user to indicate whether to include that record in the action.  I set up this by adding a field in the table as described in the tech help video "Select Multiple Records".  This all works great, BUT...

I would like the user have a check box on the top of the list to select or deselect all the records.  I have this check box in the form header.  I wrote code that runs through a RecordsetClone to update the rs!IsChecked for each record in the form.  This seems to work fine to set all the check boxes in the continuous form on or off, EXCEPT in cases where I have already manually checked the box.  When the code to change all the value runs (of the records I manually selected), the last check box stays the same of my manual selection.  Any records not manually changed are correctly set True or False, INCLUDING ALL BUT the last row that was manually selected.

For Example if I have 5 rows and I manually selected 1,3, and 5 to True, and I click the select-all off, it changes 1-4 to False, but 5 stays true.  If none were selected, all 5 got on or off as expected.  The same behavior happens if you tried turning all the rows True, when some were manually set to False.

What is STRANGER is if I use the debugger, I see the code performing EXACTLY as I expect it to run, but the form shows the last manually selected check box with the wrong value.

I tried adding a Me.Refresh after the rs operations are done and rs=Nothing, but that generates a "record locked by another user' error.

Brian Crawford OP @Reply

3 years ago

Here is the code I wrote to do this:
Private Sub SetAllChk(Optional CheckState As Boolean)
    Dim rs As Recordset
    If IsMissing(CheckState) Then
        CheckState = Me.AllChk ' This is the field on the form header to indicate select/deselect all
    End If
    Set rs = Me.RecordsetClone
    rs.MoveFirst
    If rs.EOF Then
        GoTo Clean_up
    End If

    rs.MoveFirst
    Do Until rs.EOF
        rs.Edit
        rs!IsChecked= Me.AllChk
        rs.Update
        rs.MoveNext
    Loop

Clean_up:
    Set rs = Nothing

End Sub

	Adam Schwanz @Reply 3 years ago
Why not just use an easy normal recordset? `Dim RS As Recordset Set RS = Currentdb.openrecordset("Select * From Table") While not rs.eof rs.edit rs!IsChecked=Me.AllChk rs.update rs.movenext wend rs.close set rs=nothing`

	Brian Crawford OP @Reply 3 years ago
Thanks. I will try this. I used the clone so as to touch only the records that matched the filter used by the form. I will try this approach and add a WHERE clause to the SQL built from the Me.Filter. I'll let you know if this works. Also, more than debugging my code itself, I would still like to understand what is going on that causes this odd behavior.

Brian Crawford OP @Reply

3 years ago

Adam, Unfortunately, your recommendation ended up producing the same behavior as my original code.  HOWEVER, I did manage to figure out the cause.  It was because when I tested checking off some of the check boxes (that I wanted to clear or set with my code), checking the boxes itself made the form DIRTY.

Once I added: Me.Dirty = False, both your code and mine functioned correctly.

Interestingly, I asked ChatGPT:
     If Me.Dirty=True, what will happen with this code? <paste of my code>
It's response included this:
     If Me.Dirty=True, it means that the current record in the form is in edit mode and has changes that have not been saved yet.

     In this case, if the code you provided is executed, it will throw an error when it tries to move to the next record in the recordset using rs.MoveNext, because Access does not allow you to move to another record while the current record is in edit mode.

NOTE: In this case, my form was simple and the only editable fields were the check boxes so clearing the Dirty state had no ramifications, BUT if my form had other user changes and I only wanted to clear the check boxes, clearing the Dirty state would have unintended consequences of its own.

In that case, I think I would need to check all other the fields and each row to see if any of them were Dirty using Me.Controls("myFieldName').Dirty.  If they were Dirty, I THINK I would need to:
1) Loop through and save the states somehow for the all Dirty fields row-by-row
2) Set the form to Me.Dirty = False (so my set/clear all code could run)
3) Loop through and change all the check boxes
4) Loop through and set the indvidual Dirty fields (by row) back to their prior Dirty states
5) (probably not needed because of 4 above) set the form back to dirty. Me.Dirty = True

It seems like with Access VBA, I learn something every day! ;-)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View