Auto scroll Scroll to Certain Points on a sub form on button click

[stu_c]

Hi All
I have got a Spell check function to check two fields when a button is clicked prior to creating a report, this works perfectly. The issue I have is when it is spell checking a particular box it wont scroll down show the sub form textbox it is checking and just remains at the top of the page, this would be useful when reading what it is actually checking

Public Function FUNC_SpellChecker()

Dim ctlSpell As Control
DoCmd.SetWarnings False
    
    Set ctlSpell = [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].[Form]![BackgroundDetails]
    
    [Forms]![FRM_TBLALL_FullHRDetails].SetFocus
       [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].SetFocus
    [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].SetFocus
  
    If TypeOf ctlSpell Is TextBox Then
        If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
            ctlSpell.SetFocus
            Exit Function
        End If
        With ctlSpell
            .SetFocus
            .SelStart = 0
            .SelLength = Len(ctlSpell)
            DoCmd.RunCommand acCmdSpelling
        End With
    End If

   Set ctlSpell = [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].[Form]![ResolutionDetails]
    
    [Forms]![FRM_TBLALL_FullHRDetails].SetFocus
       [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].SetFocus
    [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].SetFocus
    
    If TypeOf ctlSpell Is TextBox Then
        If IsNull(Len(ctlSpell)) Or Len(ctlSpell) = 0 Then
            ctlSpell.SetFocus
            Exit Function
        End If
        With ctlSpell
            .SetFocus
            .SelStart = 0
            .SelLength = Len(ctlSpell)
            DoCmd.RunCommand acCmdSpelling
        End With
    End If
DoCmd.SetWarnings True
MsgBox "Spell Check complete!", vbInformation + vbOKOnly, "Spell Check"
End Function

 

[Uncle Gizmo]

Chat GPT came up with the following code

Public Function FUNC_SpellChecker()

    Dim ctlSpell As Control
    Dim originalTabIndex As Integer
    DoCmd.SetWarnings False
    
    '...repeat for all controls you want to spell check...
    Set ctlSpell = [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].[Form]![ResolutionDetails]
    
    If TypeOf ctlSpell Is TextBox Then
        If Not IsNull(Len(ctlSpell)) And Len(ctlSpell) > 0 Then
            
            'Remember the original tab index and temporarily set it to zero
            originalTabIndex = ctlSpell.TabIndex
            ctlSpell.TabIndex = 0
            
            [Forms]![FRM_TBLALL_FullHRDetails].SetFocus
            [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails].SetFocus
            [Forms]![FRM_TBLALL_FullHRDetails]![SFRM_TBLALL_StaffDetails]![SSFRM_TBLALL_HRReportDetails].SetFocus
            
            With ctlSpell
                .SetFocus
                .SelStart = 0
                .SelLength = Len(ctlSpell)
                DoCmd.RunCommand acCmdSpelling
            End With
            
            'Revert to the original tab index
            ctlSpell.TabIndex = originalTabIndex
        End If
    End If
    
    DoCmd.SetWarnings True
    MsgBox "Spell Check complete!", vbInformation + vbOKOnly, "Spell Check"
End Function

Chat GPT warning:-
The downside of this approach is that it could briefly disrupt the tab order of your Form. If that's a major issue for your application, then this workaround may not be ideal. Unfortunately, without using external libraries or Windows APIs, there isn't a more precise way to scroll a Form or control into view in VBA.

Please remember to replace the control name "ResolutionDetails" with the actual names of the controls you are working with.