troubleshooting Question

How to update all check boxes filtered on a Microsoft Access subform using VBA?

Avatar of stephenlecomptejr
stephenlecomptejrFlag for United States of America asked on
Microsoft AccessVBA
11 Comments2 Solutions19 ViewsLast Modified:
Per the below code I attempt to uncheck data (Active field) that is bound to a subform.   However when people filter on that subform - I now want the VBA to recognize the subform is filtered and then uncheck such after the Active button is clicked.  How may I do this with the following and change the syntax?

Private Sub bActive_Click()
On Error GoTo err_proc

  Dim lTTLActiveFalse As Long
  Dim lTTLActiveTrue As Long
  Dim bActive As Boolean
  Dim sProcessName As String
  Dim sSpecificDB As String
  
  Dim sSQL As String
  Dim rs As DAO.Recordset
  
  sProcessName = FormProcessNameTASKS
  sSpecificDB = GetBaseFileNameOnly
  
  sSQL = "SELECT TASKS.Active, TASKS.ProcessName, TASKS.SpecificDB"
  sSQL = sSQL & " FROM [TASKS] WHERE [ProcessName] = '" & sProcessName & "' AND [SpecificDB] = '" & sSpecificDB & "'"
  sSQL = sSQL & " AND [Active] = False"
  Set rs = CurrentDb.OpenRecordset(sSQL)
  If Not rs.EOF Then
    rs.MoveLast
    lTTLActiveFalse = rs.RecordCount
  End If
  rs.Close
  Set rs = Nothing
  sSQL = "SELECT TASKS.Active, TASKS.ProcessName, TASKS.SpecificDB"
  sSQL = sSQL & " FROM [TASKS] WHERE [ProcessName] = '" & sProcessName & "' AND [SpecificDB] = '" & sSpecificDB & "'"
  sSQL = sSQL & " AND [Active] = True"
  Set rs = CurrentDb.OpenRecordset(sSQL)
  If Not rs.EOF Then
    rs.MoveLast
    lTTLActiveTrue = rs.RecordCount
  End If
  rs.Close
  Set rs = Nothing
  
  If lTTLActiveTrue > lTTLActiveFalse Then
    bActive = False
  Else
    bActive = True
  End If
  sSQL = "UPDATE [TASKS] SET [Active] = " & bActive
  sSQL = sSQL & " WHERE [ProcessName] = '" & sProcessName & "' AND [SpecificDB] = '" & sSpecificDB & "'"
  CurrentDb.Execute sSQL
  DoEvents
  
  Me.sfTasks.Requery

  
Exit_Proc:
  Exit Sub
  
err_proc:
  Call LogError(Err.Number, Err.Description, "_fTasks @ bActive_Click")
  Resume Exit_Proc
End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 2 Answers and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros