Solved Select All Checkboxes in continuous subform (1 Viewer)

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
Hello


I've a main form Called "frm_Main" and i'm using it as Form Heading to filter records in subforms in Navigation form
all subforms view as continuous form
this subform is getting data from a query based on filters i use in main form

so in this main form i wanna create checkbox field or button to "select all" all checkboxes in the filtered subform records ( table have yes/no field )
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:04
Joined
Apr 27, 2015
Messages
6,286
On the main form's control (either checkbox or button) you will need to have code on the applicable event (checkbox = AfterUpdate / Button = OnClick) that loops through the controls on the subform and if it is a checkbox, then makes the value = true.
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
On the main form's control (either checkbox or button) you will need to have code on the applicable event (checkbox = AfterUpdate / Button = OnClick) that loops through the controls on the subform and if it is a checkbox, then makes the value = true.

yeah that's what i need , so do u have a code for it ?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:04
Joined
Apr 27, 2015
Messages
6,286
I can give it a shot, but you will need to make a decision on what you want on your main form first...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Another option might be to simply update the underlying table to check the box. For example:
Code:
UPDATE TableName SET CheckboxField=True WHERE YourCriteriaHere
You can then Requery the form to display the updated data.
Hope it helps...
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
it will be a button click
so when i click it it will select all checkboxes in filtered records in subform

and i don't want to deselect
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
Hi. Another option might be to simply update the underlying table to check the box. For example:
Code:
UPDATE TableName SET CheckboxField=True WHERE YourCriteriaHere
You can then Requery the form to display the updated data.
Hope it helps...

Can't do it in a simple update query as filters is not the same everytime
so i've to do that on the fly on the showed records
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 19:04
Joined
Apr 27, 2015
Messages
6,286
All yours DBG, it's getting late here...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:04
Joined
Oct 29, 2018
Messages
21,358
Can't do it in a simple update query as filters is not the same everytime
so i've to do that on the fly on the showed records
I think it can because you "know" the criteria every time it gets changed. For example, how do you modify what's displayed on the form after a criteria is selected?
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
in the main form i'm using 3 combo boxes to filter subform records and my choices not the same everytime so i can't use update query as i'm using different criteria everytime
 

isladogs

MVP / VIP
Local time
Today, 23:04
Joined
Jan 14, 2017
Messages
18,186
If you already have a yes/no field to which your checkbox is bound then an update sql statement is definitely the best solution. You can build the filter dynamically to work with different criteria each time and set the field true ....then set to false afterwards if you wish

Or if you don't have a suitable field add one and call it something like Tag.
Its purpose is purely to mark a group of records matching a particular set of filter criteria so you can do something with those records.
 

Micron

AWF VIP
Local time
Today, 19:04
Joined
Oct 20, 2018
Messages
3,476
If the field is bound to a table or query that is updatable, then something like
Code:
Private Sub chkAll_Click()
Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone
Do Until rs.EOF
  With  rs
    .Edit
    .Fields("nameOf Field") = True
    .Update
    .MoveNext
 Loop
set rs = Nothing

 
End Sub
You will have to replace Me with a valid subform reference, which I didn't attempt because you didn't provide the names of the other forms. Also, if this is a subform on a navigation form you would need the correct subform reference. Your form design isn't clear to me so if you need help with the reference, post back but do clarify if this is a subform on a navigation form or what.
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
i've found this code
Code:
Private Sub cmdTickAllBoxes_Click()

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

 With rs
 
  .MoveFirst
 
 Do While Not rs.EOF
  .Edit
  !CheckboxFieldName = -1
  .Update
  .MoveNext
 Loop

End With

rs.Close

Set rs = Nothing

Me.Requery

End Sub
and i tried with button in subform it self and it worked

but how to move this code to the main form?
how to point to " !CheckboxFieldName"
as this Checkbox Field is in subform in navigation form named "NavigationSubform" in main form named "frm_main"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:04
Joined
Oct 29, 2018
Messages
21,358
Hi. You could try changing this line:
Code:
Set rs = Me.RecordsetClone
into something like this:
Code:
Set rs = Me.SubformControlName.Form.RecordsetClone
However, I still say an UPDATE query will still work, but that could be a discussion for another day.
 

isladogs

MVP / VIP
Local time
Today, 23:04
Joined
Jan 14, 2017
Messages
18,186
@Alvin
The code you found is basically what Micron suggested

The only difference between the recordset code and an update query or sql statement is speed.
The recordset will loop through and update the data one row at a time whereas the update statement will update 'all records at once'. If you have a lot of records, the difference in speed will be significant
 

alvingenius

IT Specialist
Local time
Tomorrow, 01:04
Joined
Jul 10, 2016
Messages
169
@Alvin
The code you found is basically what Micron suggested

The only difference between the recordset code and an update query or sql statement is speed.
The recordset will loop through and update the data one row at a time whereas the update statement will update 'all records at once'. If you have a lot of records, the difference in speed will be significant

well, you r right , i've alot of records like 500 want to update them in one time
so, the best solution will be an update query

Thanks @isladogs @theDBguy
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 23:04
Joined
Jul 9, 2003
Messages
16,245
please may i have an attachment for this topic ??
I'm not sure what you mean by an attachment? On my website there are some instructions on how to check ALL, not in a continuous form, in a datasheet. However I think the same code will work for the continuous form.

Unfortunately I am away for a few days and unable to test it in Access. I suggest you have a look at my webpage about it. I'm sure you will find something useful:-

https://www.niftyaccess.com/check-list-extras/

Sent from Newbury UK
 

Users who are viewing this thread

Top Bottom