Slow code - any suggestions? 4

[HFloyd]

Dear All,

I have written a subroutine that formats a subform based on whether there are records to display or not - basically, it hides some controls if there are no records to display and instead shows a label with a message that there are no records to display. The only problem is that it seems to run very slowly, it can take up to 30 seconds to display the subforms, and while that is happening, the user can't do anything else in the database.

Does anyone who is a sharper programmer than I have any tips for speeding up this code? I have pasted the entire subroutine below:

Public Sub SfrmNoData(CurrentForm As Form, SfrmName As String, RecordCount, _
NoDataLabelName As String, Optional HideBlankRows As Boolean = False)
'=============================================================================
' FormsCode.SfrmNoData
'-----------------------------------------------------------------------------
' Copyright by Heather L. Floyd - Floyd Innovations - [URL unfurl="true"]www.floydinnovations.com[/URL]
' Created 01-28-2004
'-----------------------------------------------------------------------------
' To show/hide subform controls based on whether there are records or not
' Any controls that should be displayed regardless of the number of records
' should have a suffix of "_AS" on their names. Any controls that should always
' be hidden should have a suffix of "_NS"
'
' *NOTE* You should have at least one control that can receive focus in the
' subform set to "_AS", and if you receive errors about no being able to hide
' the current control, have the subform's "OnOpen" Event set the focus to a
' control that will always be visible.
'-----------------------------------------------------------------------------
' Parameters:
' ARGUEMENT                     DESCRIPTION
'-----------------------------------------------------------------------------
' CurrentForm (Form)        :   Use 'ME' keyword
' sfrmName (String)         :   Name of subform in question
' RecordCount (Variant)     :   Number of records in subform
'                              (referencing a Count Query value is most dependable)
' NoDataLabelName (String)  :   Name of control that should display if no records
'-----------------------------------------------------------------------------
' Example:
'-----------------------------------------------------------------------------
' Placed in Master Form's OnCurrent Event, or in OnOpen Event
' SfrmNoData "sfrmAccounts_Deals", Me.txtDealCount, "lblNoData"
'-----------------------------------------------------------------------------
'=============================================================================

On Error GoTo ErrorCode

'Variables
    Dim ctrl As Control
    Dim Frm As Form
    Dim iRecCount As Integer
    Dim strFormName As String
    

'Values
    'Debug.Print "RecordCount: " & RecordCount & "(" & TypeName(RecordCount) & ")"
    strFormName = CurrentForm.Name
    iRecCount = Nz(RecordCount, 0)
    'Debug.Print "iRecCount: " & iRecCount

    'Set frm = Forms(Screen.ActiveForm.Name)(SfrmName).Form
    Set Frm = Forms(strFormName)(SfrmName).Form
    'Debug.Print "Form: " & frm.Name

'Show/Hide controls
    If iRecCount = 0 Then
        'No data, hide all labels except NoDataLabel
        For Each ctrl In Frm.Controls
            'Debug.Print "     " & ctrl.Name
            If ctrl.Name = NoDataLabelName Then
                ctrl.Visible = True
            ElseIf InStr(ctrl.Name, "_AS") > 0 Then
                ctrl.Visible = True
            ElseIf InStr(ctrl.Name, "_NS") > 0 Then
                ctrl.Visible = False
            Else
                ctrl.Visible = False
            End If
            'Debug.Print "     Visble: " & ctrl.Visible
        Next
    Else
       'Data, hide NoDataLabel, show others
        For Each ctrl In Frm.Controls
            If ctrl.Name = NoDataLabelName Then
                ctrl.Visible = False
            ElseIf InStr(ctrl.Name, "_AS") > 0 Then
                ctrl.Visible = True
            ElseIf InStr(ctrl.Name, "_NS") > 0 Then
                ctrl.Visible = False
            Else
                ctrl.Visible = True
            End If
        Next
    End If

'Set DataType to Snapshot
    If HideBlankRows = True And iRecCount <> 0 Then
        Frm.RecordsetType = 2   'Snapshot
    Else
        'leave unchanged
    End If

ExitCode:
    Exit Sub

' Error handling block added by Error Handler Add-In. DO NOT EDIT this block of code.
' Automatic error handler last updated at 01-28-2004 14:23:58
ErrorCode:
    Select Case Err.Number
        Case 2165   'can't hide object with focus
            Resume Next
        Case 2467   'object is closed
            DoCmd.Close
            DoCmd.OpenForm strFormName
            'Debug.Print "Handled - Error " & Err.Number & ": " & Err.Description & " - FormsCode.SfrmNoData"
            Resume ExitCode
        Case 2465   'can't locate subform
            'Debug.Print "Error " & Err.Number & ": " & Err.Description & " - FormsCode.SfrmNoData"
            Resume ExitCode
        Case 2475   'No form is open
            'Debug.Print "Error " & Err.Number & ": " & Err.Description & " - FormsCode.SfrmNoData"
            Resume ExitCode
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "FormsCode.SfrmNoData"
            Resume ExitCode
    End Select
' End Error handling block.
End Sub

Thanks in advance!

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[LNBruno]

This may be an overly simplistic approach, but if there are no records to display, why not pop a message box and just not show the form at all?

< M!ke >

 

[HFloyd]

Dear M!ke,

I want to have a consistent interface, so, for instance, on the main menu/switchboard/home screen, the current user's deals are displayed in two subforms (one where the current user is the "producer" of the deal, and another where the user is the "co-producer" of the deal), so I wanted to show both the subforms, but if the user is currently not a producer or co-producer on any deals, the message is displayed that they do not have any deals.

You are right, however, that just disapearing the subforms would be a lot faster, code wise, and I might consider giving up my "beautiful" interface for the performance gain if I can't get this code to run a bit faster.

Thanks,

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[LNBruno]

Ah, I see.

Okay, you're going to figure out that I'm the kind of developer who looks for the shortest distance between two points (sometimes you have to blow up the mountain!):

Maybe try this:

'Show/Hide controls
    If iRecCount = 0 Then
        'No data, hide all labels except NoDataLabel
        For Each ctrl In Frm.Controls
            'Debug.Print "     " & ctrl.Name
            If ctrl.Name = NoDataLabelName OR InStr(ctrl.Name, "_AS") > 0 Then
                ctrl.Visible = True
            Else
                ctrl.Visible = False
            End If
            'Debug.Print "     Visble: " & ctrl.Visible
        Next

< M!ke >

 

[LNBruno]

You would, of course, need to repeat that in the ELSE part. Not sure if that's going to buy you much, but it would keep from checking the control a couple extra times.

Nano-seconds! Who knew they added up to so much so fast?

< M!ke >

 

[RoyVidar]

Some suggestions, don't know how much they'll help...

In stead of using a string comparision on part of the control name, you could use the .tag property of the controls for these values.

This is looping thru all controls on the form, you could perhaps limit it a little by using the .controltype property, some samples in this thread thread705-786249 (also some discussion on the tag property there). For instance to only work on textboxes and comboboxes:

[tt]select case ctl.controltype
case accombobox, actextbox
' ...
end select[/tt]

Roy-Vidar

 

[RoyVidar]

On second thought, the looping can't be what's slowing downt, that performs very quick. You might consider using some debug.print time at different places in the code to identify what's really the drag.

Is there any other coder running from the on current, do you debug/compile, compact/repair (perhaps consider a /decompile)...

Roy-Vidar

 

[HFloyd]

Dear M!ke & Roy-Vidar,

Thanks for your tips, after puting in some "debug.print Now()" statements, I realized that this particular subroutine wasn't the big bottleneck - the subforms themselves were, so I'm going to put further efforts into optimizing the subforms data sources, maybe with temp tables or something.

However, I did implement M!ke's "If...Else..." improvement, because I think it is cleaner.

Thanks a lot for your help,

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[CosmoKramer]

Heather,

I think this should accomplish what you're looking for by simply making the subform invisible and the "no records" message visible when there is no data.

Place your lblNoData label under the subform. In the On Current event of your main form place code like this:

Private Sub Form_Current()
If IsNull(Forms![frm_MainForm]![sfrmSubForm]![AnySubFormControl]) Then
   Me.lblNone.Visible = True
   Forms![frm_MainForm]![sfrmSubForm].Visible = False
Else
   Me.lblNone.Visible = False
   Forms![frm_MainForm]![sfrmSubForm].Visible = True
End If
End Sub

Hoc nomen meum verum non est.

 

[HFloyd]

Dear CosmoKramer,

That is a good suggestion, except that I have a command button on each of the subforms that will allow the user to add a new deal, which I would like to be available even if there are no records. Also, the subform header label is on the subform, so I always want that displayed as well.

Of course, I could copy all the controls I want to always be displayed onto the main form, covered by the subform, but I wanted to keep things tidy, and if I change the layout of the subform, I wouldn't have to worry about rearranging the extra controls also.

Also, I think I read somewhere that overlapping controls slows down the rendering of a form as it's opening, though I haven't done any benchmarking myself to verify that.

Heather

Floyd Innovations

http://www.floydinnovations.com

 

[cgarts]

I sometimes simplify and speed up a form by swapping
simplified subforms into that form - sometimes blank
unbound subs with just a label on them.

To alternate between a bound sub and a blank unbound
sub with a subform control named FrameSwap and a
blank sub named FrmSubBlank, and a more complex
sub named FrmSubA:

If FrameSwap.SourceObject <> "FrmSubA" Then
FrameSwap.SourceObject = "FrmSubA"
Else FrameSwap.SourceObject = "FrmSubBlank"
End If


In your case, when no records exist - maybe something like:

If iRecCount = 0 Then
FrameSwap.SourceObject = "FrmSubBlank"
LblBlank = "No Records Exist"
Else FrameSwap.SourceObject = "FrmSubA"
End If

Hope this is useful

C

 

[Page410]

Are you using the Name AutoCorrect feature? I've read on a couple of postings that using this feature can severely affect performance in regard to form loading.

Check out this thread: thread702-206410

 

[HFloyd]

Dear cgarts,

That is an interesting idea. For awhile I was just leaving the subforms unbound (they are on tab controls) and when the user clicked on the tab control, it would load in the subform and perform the formatting.

I think I've managed to get the subforms running fast enough that I can just open them as bound forms now.

Thanks,

Heather


Floyd Innovations

http://www.floydinnovations.com

 

[HFloyd]

Dear Page410,

You are right - I forgot about the "Name Autocorrect" bugs. I will definitly try that.

Thanks,

Heather

Floyd Innovations

http://www.floydinnovations.com