tabbed subform requeries 2x

[SteveApa]

Hi All....



I vba requery a tabbed subform based on "on current" of a selection. With a break point on the "on current" of each tab of the tabbed subform, I can see that it hits the "on current" of each tab two times!!

If I don't do the tabbed subform and put each of the tabs as a sub form individually, it does not hit the "on current" of each of the subforms 2x, it only hits 1x as expected. which greatly increases speed.

Speed is much greater not using a tabbed subform vs having each tab as an individual subform.

Any info would be great

Thanks
Steve

[SteveApa]

more info... Access frontend with sql server backend

[Gicu]

Because you won't see both tabs at the same time anyway you should only have one subform control under the tab control itself (on the main form not on a tab page).

Now add code to the Change event of the tab to change the source object of the subform to the corresponding form.

An alternative is to have code removing the source objects of all the subforms on all the tab pages but the current one, but depending on how many pages you have it could get complicated (I used to have a local table holding the names of the subform controls on each tab and their corresponding source objects).
Here is the code I used for the second option:

Option Compare Database
Option Explicit
Public Sub vcWriteSourceObject()
On Error Resume Next


Dim ctrl As Control, strSubform As String
Dim rst As DAO.Recordset


Set rst = CurrentDb.OpenRecordset("tmpRecordSourceLookup", dbOpenDynaset, dbSeeChanges)
For Each ctrl In Screen.ActiveForm.Controls
    If ctrl.ControlType = acSubform Then
        strSubform = ctrl.Name
        rst.AddNew
        rst("FormName") = Screen.ActiveForm.Name
        rst("SubFormName") = ctrl.Name
        rst("SourceObject") = ctrl.SourceObject
        rst.Update
    End If


Next


MsgBox "Done"


End Sub
Public Sub vcShowTabSubforms(strForm As String, strTabControl As String, lngPage As Long)
On Error GoTo vcShowTabSubforms_Error


Dim ctrl As Control
Application.Echo False
For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages.Item(lngPage).Controls
    If ctrl.ControlType = acSubform Then
        ctrl.SourceObject = DLookup("[SourceObject]", "[tmpRecordSourceLookup]", "[FormName]= '" & strForm & "' AND [SubformName]= '" & ctrl.Name & "'")
    End If
Next
Application.Echo True


vcShowTabSubforms_Exit:
Exit Sub


vcShowTabSubforms_Error:
Application.Echo True


End Sub


Public Sub vcHideTabSubforms(strForm As String, strTabControl As String)
On Error GoTo vcHideTabSubforms_Error


Dim ctrl As Control, pg As Page
Application.Echo False


For Each pg In Forms(strForm).Form.Controls(strTabControl).Pages
    For Each ctrl In Forms(strForm).Form.Controls(strTabControl).Pages(pg.Name).Controls
        If ctrl.ControlType = acSubform Then
            ctrl.SourceObject = ""
        End If
    Next
Next pg


Application.Echo True
vcHideTabSubforms_Exit:
Exit Sub
vcHideTabSubforms_Error:
Application.Echo True
End Sub

To use if I would have this in the Change event of the tab:

Private Sub StudentInfoSub_Change() 'this is a tab control
vcUpdateSubforms
End Sub
Private Sub vcUpdateSubforms()
On Error Resume Next
Application.Echo False
    Call vcHideTabSubforms(Me.Name, "StudentInfoSub")
    Call vcShowTabSubforms(Me.Name, "StudentInfoSub", Me.StudentInfoSub.Value)
Application.Echo True
End Sub

Cheers,

[SteveApa]

Hey thanks for that. While I was waiting for a response, I thougt I would nix the tabbed subform and have 2 std forms, just hide one / show one with a toggle button.

[Gicu]

If you simply hide it (the subform control that is) it will still take time to requery its source object's record source. If you only have on subform control it will only requery the one being displayed, so you can do the same think I mentioned in the Click event of the toggle button, just swap the source object of the subform control based on the toggle value.

Cheers,

[SteveApa]

Thanks. My solution of having 2 subforms and not use the 1 tabbed subform with 2 tabs is this... The main issue is that the "on Current" of each tab on the tabbed subform get hit 2x when I requery the tabbed subofrm: Not sure why, I stepped thru many times to figure it out but cannot. When I use 2 subforms, each "on current" of the subforms only hits the "on current" 1 time, as expected when re-quey the individual subforms

[Gicu]

Steve,
Here is what I meant with my first suggestion.
Cheers,