Select all checkboxes and apply current filter

[cbmorehouse]

I found some code to use so that I can press a button to select or deselect checkboxes next to the records within my form. The problem is that when I filter the form and then toggle the button to select all the records it will select every record in that table instead of just the ones i'm filtering on the screen. What addition do I need to put into my code that tells it to only check/uncheck the records that are currently being filtered? Here's the code below


Private Sub tglYesNo_Click()
Select Case tglYesNo


Case True


DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblItemDetail SET tblItemDetail.Selection = On"
DoCmd.SetWarnings True
Me.Requery
Me.tglYesNo.Caption = "Un-Tick All"

Case False
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblItemDetail SET tblItemDetail.Selection = Off"
DoCmd.SetWarnings True
Me.Requery
Me.tglYesNo.Caption = "Tick All"


End Select


End Sub

[Gicu]

You need to loop through the form's recordsetclone and update the checkboxes:

DIm intJ as integer, iNumberOfSelectedRecords as Integer

DoCmd.SetWarnings False
With Forms!YOUR_FORM.Form.RecordsetClone
        .MoveLast
        .MoveFirst
         iNumberOfSelectedRecords = .RecordCount




    DoCmd.SetWarnings False


    For intJ = 1 To iNumberOfSelectedRecords 
        .Edit
        !Selected = True
        .Update
            .MoveNext
    Next intJ
    .Close
End With
DoCmd.SetWarnings True
Forms!YOUR_FORM.Form.Refresh
End Sub

Cheers,
Vlad

[CJ_London]

The problem is that when I filter the form and then toggle the button to select all the records it will select every record in that table instead of just the ones i'm filtering on the screen

you need to apply a criteria to your sql based on the filter of your form. Not tested but along the lines of

DoCmd.RunSQL "UPDATE tblItemDetail SET tblItemDetail.Selection = On WHERE " & me.filter

[cbmorehouse]

you need to apply a criteria to your sql based on the filter of your form. Not tested but along the lines of

DoCmd.RunSQL "UPDATE tblItemDetail SET tblItemDetail.Selection = On WHERE " & me.filter

I tried that and it didn't work.

[CJ_London]

OK - 'didn't work' doesn't tell me anything and I don't have time for twenty questions so suggest go with Gicu's suggestion.

good luck with your project

[cbmorehouse]

OK - 'didn't work' doesn't tell me anything and I don't have time for twenty questions so suggest go with Gicu's suggestion.

good luck with your project

Sorry, I didn't see Gicu's comment above. No reason for you to be a complete asshole though. I followed what you said and that didn't work. I don't know how else to say that it didn't work.

[davegri]

No reason for you to be a complete asshole though.

This is a perfect example of how to get on everyone's ignore list.

[Gicu]

davegri, would you please elaborate? Why "didn't work" is considered a "I have to work harder on this shi...y forum to get some positive response out of it". If you don't have time to fully reply just say so.
Anyway, the solution I posted was my way of dealing with numerous ways of changing the recordsource of a (sub)form. It could be actual record source: query with parameters coming from a form, filters set by VBA, right clicking and going Equalto... I think the "what you see is what you got got: approach of looping through the visible records of the form is the way to go in this case,

Sorry if I offended anyone...
Vlad

[davegri]

Vlad, my man, you've stepped in front of the bullet by mistake!
My comment was directed to the OP.

[ssanfu]

I've been going back and forth on whether or not to post a reply......

I'm in agreement with davegri on this.

@Gicu

<snip> "I have to work harder on this shi...y forum ......

I'm not offended, but I am curious as to why you are still on the forum, if that is the way you truly feel about the forum....

I tried that and it didn't work.

@cbmorehouse,
maybe you are new to forums and don't understand that a reply like "it didn't work." is not informative. There should be info on what happened, didn't happen, error messages, results, etc. We don't know your skill level with VBA or Access in general. Details from you are important to be able to try and give a valid response.

No reason for you to be a complete asshole though.

However, bad behavior is NEVER acceptable.
"complete asshole"...really???? You (with 5 posts), insulting the person (with 5,800+ posts) trying to help you is astonishing.
Ever heard the saying "Never bite the foot that stomps your grapes"?

Against my better judgment, I looked at your code and made a couple of changes.

Private Sub tglYesNo_Click()
    Dim sSQL As String
    Dim MeFilter As String

    'is there a filter applied?
    If Len(Me.Filter) > 0 Then
        MeFilter = "  WHERE " & Me.Filter
    Else
        MeFilter = vbNullString
    End If

    sSQL = "UPDATE tblItemDetail SET tblItemDetail.YourFieldName = " & Me.tglYesNo & MeFilter
    'Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError
    Me.Requery
    If Me.tglYesNo Then
        Me.tglYesNo.Caption = "Un-Tick All"
    Else
        Me.tglYesNo.Caption = "Tick All"
    End If
End Sub

Another option:

Private Sub tglYesNo_Click()
    Dim sSQL As String
    Dim MeFilter As String

    
    MeFilter = Me.Filter

    Select Case tglYesNo
        Case True
            sSQL = "UPDATE tblItemDetail SET tblItemDetail.YourFieldName = TRUE WHERE " & MeFilter
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
            Me.tglYesNo.Caption = "Un-Tick All"
        Case False
            sSQL = "UPDATE tblItemDetail SET tblItemDetail.YourFieldName = FALSE WHERE " & MeFilter
            'Debug.Print sSQL
            CurrentDb.Execute sSQL, dbFailOnError
            Me.Requery
            Me.tglYesNo.Caption = "Tick All"
    End Select
End Sub

Is "Selection" the name of the field in the table? Don't know because you haven't said anything about the table/fields.....
Replace YourFieldName with the field name that is used to "select" record(s).