Solved Select All, Deselect All or multiple record selection in continuous form in MS Access (1 Viewer)

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
I have continuous form and want a single button for
1) select All
2) deselect all
3) multiple selection
How it works:
A) by button ""Select/Deselect All"
B) by checking the Check box (multiple selection)
Specific record row selection (check box) also changes a value in another control.
I have code, working fine but there is one problem.
In the picture you can see, Yellow highlighted, the record which in edit mode does not change with the button, because if it is already true then will remain true or False with remain false.
Screenshot 2022-03-29 191541.png

I have attached Demo to understand better what I meant.
Meaning If record is already selected by checkbox, and I click deselect all. then that specific record is not deselected. AND vice versa

Code for Button
Code:
Private Sub btnSelect_Click()
Dim rst As Recordset, i As Integer

Set rst = Me.RecordsetClone
i = 0
rst.MoveFirst
Do While Not rst.EOF
   i = i + 1
   rst.Edit
   If rst![SELECTION] Then
       rst![SELECTION] = False
       rst![Status] = "Pending"
   Else
       rst![SELECTION] = True
       rst![Status] = "Approved"
   End If
   rst.Update
   rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Check box code
Code:
Private Sub SELECTION_Click()
If Me.SELECTION.Value = True Then
Me.Status.Value = "Approved"
Else
If Me.SELECTION.Value = False Then
Me.Status.Value = "Pending"
End If
End If
End Sub

Thanks
 

Attachments

  • SELECT All DESELECT All - 29.03.22.accdb
    800 KB · Views: 92

theDBguy

I’m here to help
Staff member
Local time
Today, 00:47
Joined
Oct 29, 2018
Messages
18,992
Select All
Code:
UPDATE TableName SET CheckboxField = True

Deselect All
Code:
UPDATE TableName SET CheckboxField = False
 

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Select All
Code:
UPDATE TableName SET CheckboxField = True

Deselect All
Code:
UPDATE TableName SET CheckboxField = False
It is single button performing both function, for select all and deselect all - as demo db
 

isladogs

CID VIP
Local time
Today, 08:47
Joined
Jan 14, 2017
Messages
16,241
Scrap the recordset code.

Change the button caption to Select All
Use an If...Else code section in the button click event as follows

Code:
If btnSelect.Caption ="Select All" Then
   ' run the first update code given in post #2 and change the button caption to Deselect All.
Else
  'run the second update code and change the caption back to Select All
End If

You can also include the changes to the Status field as part of the update queries
 
Last edited:

moke123

AWF VIP
Local time
Today, 03:47
Joined
Jan 11, 2013
Messages
2,857
Be aware that if this is a multi user database you may run into conflicts when 2 users are utilizing the same code.
 

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Scrap the recordset code.

Change the button caption to Select All
Use an If...Else code section in the button click event as follows

Code:
If btnSelect.Caption ="Select All" Then
   ' run the first update code given in post #2 and change the button caption to Deselect All.
Else
  'run the second update code and change the caption back to Select All
End If

You can also include the changes to the Status field as part of the update queries
Thank you. I am trying this way. but it gives compile error, variable not defined.
Code:
If btnSelect.Caption = "SelectAll" Then

Update OrdersTbl
Set Approval = True

Else
Update OrdersTble
Set Approval = False

End If
 

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Be aware that if this is a multi user database you may run into conflicts when 2 users are utilizing the same code.
This is good to know, because it will be multiuser. thank you
 

isladogs

CID VIP
Local time
Today, 08:47
Joined
Jan 14, 2017
Messages
16,241
Change the code to:

Code:
Private Sub btnSelect_Click()

If btnSelect.Caption = "Select All" Then
   CurrentDb.Execute "UPDATE Table1 SET Selection=True, Status='Approved'"
   btnSelect.Caption = "DeSelect All"
Else
  CurrentDb.Execute "UPDATE Table1 SET Selection=False, Status='Pending'"
  btnSelect.Caption = "Select All"
End If

Me.Requery

End Sub

Private Sub SELECTION_Click()

If Me.SELECTION.Value = True Then
    Me.Status.Value = "Approved"
Else
    Me.Status.Value = "Pending"
End If

Me.Dirty = False

End Sub
 
  • Love
Reactions: Ihk

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Change the code to:

Code:
Private Sub btnSelect_Click()

If btnSelect.Caption = "Select All" Then
   CurrentDb.Execute "UPDATE Table1 SET Selection=True, Status='Approved'"
   btnSelect.Caption = "DeSelect All"
Else
  CurrentDb.Execute "UPDATE Table1 SET Selection=False, Status='Pending'"
  btnSelect.Caption = "Select All"
End If

Me.Requery

End Sub

Private Sub SELECTION_Click()

If Me.SELECTION.Value = True Then
    Me.Status.Value = "Approved"
Else
    Me.Status.Value = "Pending"
End If

Me.Dirty = False

End Sub
Thank you very much. Kind of you. :)
 

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Change the code to:

Code:
Private Sub btnSelect_Click()

If btnSelect.Caption = "Select All" Then
   CurrentDb.Execute "UPDATE Table1 SET Selection=True, Status='Approved'"
   btnSelect.Caption = "DeSelect All"
Else
  CurrentDb.Execute "UPDATE Table1 SET Selection=False, Status='Pending'"
  btnSelect.Caption = "Select All"
End If

Me.Requery

End Sub

Private Sub SELECTION_Click()

If Me.SELECTION.Value = True Then
    Me.Status.Value = "Approved"
Else
    Me.Status.Value = "Pending"
End If

Me.Dirty = False

End Sub
Hi Sorry I had to come back with a big problem. Code solves the problem which I was looking for. But there is another very big problem with this. It updates all the records in database, rather it should change only current records showed in current continuous form.
It changes all the data in database (for example cancelled, delayed etc etc )..
 

moke123

AWF VIP
Local time
Today, 03:47
Joined
Jan 11, 2013
Messages
2,857
Hi Sorry I had to come back with a big problem. Code solves the problem which I was looking for. But there is another very big problem with this. It updates all the records in database, rather it should change only current records showed in current continuous form.
It changes all the data in database (for example cancelled, delayed etc etc )..
I was just about to post about this. It also appears that there are more than 3 possible statuses

You also should not use a lookup field in a table. http://access.mvps.org/access/lookupfields.htm
 
Last edited:
  • Like
Reactions: Ihk

isladogs

CID VIP
Local time
Today, 08:47
Joined
Jan 14, 2017
Messages
16,241
You didn't mention that there were other possible status values nor that the form would only show a subset of the table records.
We can only answer based on the info you provide.

You can use a WHERE filter in the UPDATE statements to limit the number of records altered to ensure only the ones you want get altered.
Consider basing your form on a query instead then update the query records.

I didn't notice you had used lookup fields in the table but agree these should be avoided. Use a lookup table instead
Now I've checked I see 3 status values NOT including delayed
 

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
You didn't mention that there were other possible status values nor that the form would only show a subset of the table records.
We can only answer based on the info you provide.

You can use a WHERE filter in the UPDATE statements to limit the number of records altered to ensure only the ones you want get altered.
Consider basing your form on a query instead then update the query records.

I didn't notice you had used lookup fields in the table but agree these should be avoided. Use a lookup table instead
Now I've checked I see 3 status values NOT including delayed
1st of all I must have to say thanks a lot for helping and sorting out the problem. Now it is functional. I am learning here every day.
1) above was just demo db, otherwise the original which has more than 20 tables and so on, only one table for each related record has text the rest have foreign ID via subform, combo based. None of them has look up.
2) I have two tables only for different combo records (ComboTypesTbl, ComboDataTbl), So all combos are fetched from comboDataTbl based on ID every where in Database. This helps not to create so many tables for combos data only.
3) Yes form is based on Query, so I changed code to update Query, so other records in table are not affected.
4) This Query is filtering on "Pending" records based on ID for pending (offcourse not text). (there are different queries + forms to show Approved, delayed, cancelled etc)
Another problem:
As I said query is filtering "Pending" records only. So after applying "Select All" code, all record disappear because of "Me.requery" But on the other hand If I dont do "Requery" then code works only on 1st record or even does not work.
Why I dont want to get record disappeared, reason is
For example: if there are 50 pending records and user wants to approve 45 records, so he will do "Select All" and then will manually deselect the 5 of his choice. Hope you got what i meant. thank you.
 
Last edited:

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
Here is my real code

Code:
Private Sub btnSelect_Click()
If btnSelect.Caption = "Select All" Then
   CurrentDb.Execute "UPDATE PendingOrdersQRY SET Approval=True, Status= 2"
   btnSelect.Caption = "DeSelect All"
Else
  CurrentDb.Execute "UPDATE PendingOrdersQRY SET Approval=False, Status=1"
  btnSelect.Caption = "Select All"
End If

Me.Requery
End Sub
In my real database checkbox name is Approval instead of above said SELECTION in demo....
Code:
Private Sub Approval_Click()
If Me.Approval.value = True Then
    Me.Status.value = 2
Else
    Me.Status.value = 1
End If
Me.Dirty = False
End Sub
 

moke123

AWF VIP
Local time
Today, 03:47
Joined
Jan 11, 2013
Messages
2,857
You may want to consider a multiselect listbox instead of a continuous form.
 
  • Like
Reactions: Ihk

isladogs

CID VIP
Local time
Today, 08:47
Joined
Jan 14, 2017
Messages
16,241
You may be able to solve your new problem by using Me.Refresh or Me.Repaint or Me.Recalc instead of Me.Requery.

All will update the screen but work in slightly different ways.
Which will be right for you depends on the recordsource used for the form.
 
  • Like
Reactions: Ihk

Ihk

Member
Local time
Today, 09:47
Joined
Apr 7, 2020
Messages
209
You may be able to solve your new problem by using Me.Refresh or Me.Repaint or Me.Recalc instead of Me.Requery.

All will update the screen but work in slightly different ways.
Which will be right for you depends on the recordsource used for the form.
Thank you again, it is getting closer and solved the problem. Record wont get disappeared. Still available for manual deselection one by one.
Though caption is changed but Deselect wont work.
Because query is being filtered based "Pending", After "select All" probably no record is left in query to reverse it "DeSelect All", Though record has not disappeared from the form.
Because I tested it with unfiltered query, there Select All and Deselect All works perfectly.
 

isladogs

CID VIP
Local time
Today, 08:47
Joined
Jan 14, 2017
Messages
16,241
II tested on a form based on a query with Selection=False.
In my tests, Recalc/Refresh & Repaint all allowed the form to update & allowed for the reverse action to work.
The obvious problem was that when reopened, the form would be empty if all had been selected

Without having a cut down version of your actual database it is difficult to advise further.
 

Users who are viewing this thread

Top Bottom