I want to create a button to check off boxes in a subform (1 Viewer)

[raymondchen21]

Hi,
I created a navigation form which has several tabs. Each tab has a subform attached to it. When I click on different tabs, different subforms will show. I will want to create a button to check off all boxes in the subform only when it's showing.

I found the following code but when I ran it and it showed me run-time error '7951'. Can anyone please help me with this code? What went wrong or what other code should I use?


Private Sub SelectAll_Click()

Dim rst As DAO.Recordset, i As Integer

Set rst = Me.RecordsetClone
i = 0
rst.MoveFirst
Do While Not rst.EOF
i = i + 1
rst.Edit
If rst![Identified] Then
rst![Identified] = False
Else
rst![Identified] = True
End If
rst.Update
rst.MoveNext
Loop
MsgBox i & " Records Marked."

rst.Close
Set rst = Nothing

End Sub


[Identified] is the name of the checkbox in the subforms.

 

[sneuberg]

I'm not sure about this but since the error suggests a lack of a record set the line

Set rst = Me.RecordsetClone

is perhaps referencing the wrong place. If you type Me. does the intelisense show a subform that might go better here.

 

[raymondchen21]

I'm not sure about this but since the error suggests a lack of a record set the line

Set rst = Me.RecordsetClone

is perhaps referencing the wrong place. If you type Me. does the intelisense show a subform that might go better here.

After I done typing Me., it gave me lots of option. The name of the subform is on the list as well.

After I changed it, now it showed run time error '13'
Type Mismatch

 

[Cronk]

You have your button on the parent form. So your code is trying to update whatever data source your parent form is bound to, not the subform.

The syntax would be
Set rst = me.subformControlName.Form.recordsetclone

Rather than just put a button on the parent form and then try to sort out which tab page has the focus, put a button on each of your subforms and use the code you posted initially.

Then the changes to the underlying data source won't show up on the screen until the screen if refreshed with a Me.requery on each subform's button onClick code.

 

[raymondchen21]

You have your button on the parent form. So your code is trying to update whatever data source your parent form is bound to, not the subform.

The syntax would be
Set rst = me.subformControlName.Form.recordsetclone

Rather than just put a button on the parent form and then try to sort out which tab page has the focus, put a button on each of your subforms and use the code you posted initially.

Then the changes to the underlying data source won't show up on the screen until the screen if refreshed with a Me.requery on each subform's button onClick code.

Thank you very much for the tip Cronk. It works perfect. How it allows me to check all boxes with one click.
I like your idea as well. I will play around with the code and see which one works best.