Delay in Exiting bound form

Current app is in Access 2003,  executed on a multi-user network.  One of the bound forms in the app is experiencing a long delay (up to 50 seconds) when exiting.  

I put logic in the form to log times as it goes thru each event, starting with before update.  

I am testing on our office network with 2 concurrent users.  I've tried the form with 'Edited Record' as the locking scheme and currently have 'No Locks' selected.

Here's what I'm seeing

BeforeUpdate event
After the last statement, prior to Exit of Before Update  10:55:13

The following events have no logic other than the time logging function.

Form After Update                      10:55:13
Form After Insert                         10:55:13
Form Unload                                10:55:23         Ten seconds after prior step
Form Close                                   10:55:23  

On our small in-office network it's taking 10 seconds after the time is logged in 'After Insert' to log the time in 'Unload'.  There is no logic in these events.  On the clients network with 25 - 30 users it's taking 50 seconds to one minute.

When testing on a stand alone Non-networked machine there is no noticible delay exiting the form.  The issue is when running on a network, with the backend DB resident on the network.  
Each user has their own copy of the front end mdb on their local drive.

The delay is unacceptable for the users but I don't see any way to tighten it up.   Unless I can get the bound form to be more efficient I am considering changing the form to be 'Unbound' and insert the new record using SQL.

Any ideas o this huge delay exiting the form?

Here are the events:
Private Sub Form_AfterInsert()
logTimingRecsInErrorLog "frmCallResult_Manual_Single, After Insert"

End Sub

Private Sub Form_AfterUpdate()
logTimingRecsInErrorLog "frmCallResult_Manual_Single, After Update"

End Sub

Private Sub Form_Unload(Cancel As Integer)
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Unloading Form"

End Sub


Private Sub Form_BeforeInsert(Cancel As Integer)
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Insert"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'                            
                               If IsDeveloper Then
                               Else
                                 On Error GoTo Form_BeforeUpdate_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

If Any_Edit_Errors = True Then
    Cancel = True
    Errors_So_DoNotExit = True
    Exit Sub
End If
'
' Set up to write event record
'
Dim wkComment As String
Dim wkPhone As Double
Dim wkEventID As Long
'Dim wkHistoryDialerImportID As Long
Dim wkDateTimeRcvd As Date
Dim wkResult As String
Dim wkIncomingOrOutgoing As String
Dim wkPhoneNumber As String
'
wkDateTimeRcvd = Me.DateOfCall + Me.TimeOfCall
'
wkComment = ""
wkResult = ""
wkIncomingOrOutgoing = ""
'
wkPhoneNumber = Me.PhoneNum
'
If Nz(Me.CallTypeID, 0) = eCallType.eIncoming Then
    wkIncomingOrOutgoing = "Incoming"
Else
    wkIncomingOrOutgoing = "Outgoing"
End If
'
If Nz(Me.CallResultID, 0) = eDialerStatus.eReponseCallSuccessful Then
    wkResult = "Successful"
ElseIf Nz(Me.CallResultID, 0) = eDialerStatus.eReponseCallUnsuccessful Then
    wkResult = "Unsuccessful"
ElseIf Nz(Me.CallResultID, 0) = eDialerStatus.eResponceBadNumber Then
    wkResult = "Bad Number"
End If
'
wkComment = wkDateTimeRcvd & ", " & wkIncomingOrOutgoing & " manual call, number: " & wkPhoneNumber & ", Result: " & wkResult & ".  " & Me.Comment
'
wkPhone = getJustNumbersFromPhone(Me.PhoneNum)
'
' save values to write on records, if multiple BRT's are to be updated
'
gCallLogID = Nz(Me.ID, 0)
'
savePhoneNum = Nz(Me.PhoneNum, 0)
saveCallResultID = Nz(Me.CallResultID, 0)
saveComment = wkComment
saveDateTimeRcvd = wkDateTimeRcvd
saveDateOfCall = Me.DateOfCall
saveTimeOfCall = Me.TimeOfCall
saveCallTypeID = Nz(Me.CallTypeID, 0)
saveCallerTypeID = Nz(Me.CallerTypeID, 0)
saveInterestedPartyID = Nz(Me.InterestedPartyID, 0)
saveCallResultHeaderID = Nz(Me.ID, 0)
'
' Update PropertyID reflect the call status
'
DoCmd.Hourglass True
'
' write call event
'
'writeEventLog eSystemEvent.eManualCallResult, _
'              Nz(Me.BRT, 0), _
'              returnPropertyID, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              wkHistoryDialerImportID, _
'              wkComment, _
'              0, _
'              0, _
'              0, _
'              0, _
'              wkDateTimeRcvd, _
'              Nz(Me.CallResultID, 0), _
'              wkPhone
'
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Update or Add Phone Number"

updateOrAddPhoneNumerResult Nz(Me.PropertyID, 0), _
                            Nz(Me.BRT, 0), _
                            Nz(Me.PhoneNum, ""), _
                            Nz(Me.CallResultID, 0), _
                            Nz(Me.InterestedPartyID.Column(5), 0)
''
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Ater Update Add Phone, Before write comment "

writePropertyComment Nz(Me.PropertyID, 0), wkComment, , , Nz(Me.BRT, 0)
'
''
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Ater Write Comment "

'
DoCmd.Hourglass False
'
If Me.NewRecord Then
    Me.RelatedEventID = wkEventID
    Me.DateAdded = Now()
    Me.UserAdded = gCurrUserName
Else
    Me.DateRevised = Now()
    Me.UserRevised = gCurrUserName
End If
'
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Exit of Form_BeforeUpdate rotuine"

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
Form_BeforeUpdate_Error:
                               sysErrorHandler Err.Number, Err.Description, "Form_BeforeUpdate", "Form_frmCallResult_Manual_single", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------



End Sub

Private Sub Form_Close()
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Form_Close event"

End Sub

Open in new window

LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Is your app split into a Front End and Back End with each user having their own copy of the Front End on their station?

 If not, that's most likely where your problem lies.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<< The issue is when running on a network, with the backend DB resident on the network.  
Each user has their own copy of the front end mdb on their local drive.>>

 Sorry, I missed this when reading through.

 Let me re-think.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
What's this eDialerStatus?

Is it an Active-X control on the form or something else?

Where/when is it instantiated and where/when do you terminate it?

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, it's doubtful that record operations are your problem.  Once you hit AfterInsert, all the record operations are complete.

 It's the closing of the form that is slow.

Jim.
0
Dale FyeCommented:
What are your "updateOrAddPhoneNumerResult" and "writePropertyComment" procedures doing?

I do not generally do any updating or writing to any tables in the BeforeUpdate event, only validation of required fields and things like you did with this segment.
If Me.NewRecord Then
    Me.RelatedEventID = wkEventID
    Me.DateAdded = Now()
    Me.UserAdded = gCurrUserName
Else
    Me.DateRevised = Now()
    Me.UserRevised = gCurrUserName
End If

Open in new window

If I need to actually write information from unbound fields on this form to some other table, I generally do that in the AfterUpdate event of the form.  This may be a result of frequently using SQL Server as my BE, but it is still a good technique.  You don't want to actually perform those operations until the current record passes your BeforeUpdate tests.

Dale
0
mlcktmguyAuthor Commented:
The "updateOrAddPhoneNumerResult" and "writePropertyComment"  routines called in the 'Before Update' are reading and writing files.  The displays I have before and after they execute didn't show any time lag in those routines.

However, I too Dale's recommendation and moved them to the 'After Update' event.  Still no improvement in the time it takes to exit the form between the 'after insert' and 'unload' event.

I agree the delay doesn't seem to be due to record processing but I don't understand why the delay is so long.

eDialerStatus is an enumerated constant
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<eDialerStatus is an enumerated constant >>

Let me ask that differently; What is it outside of the native controls and procedures in Access that your working with (if anything) on this form?

Jim.
0
Dale FyeCommented:
1.  How are you closing this form (what mechanism)?
a. X it upper right corner
b. Close in the popup menu in the upper left corner
c. Some other button click event.  If this, what is the code behind this event?
2. What code opens this form?  Is something going on immediately after this form "closes" which might actually be requerying another form?
0
mlcktmguyAuthor Commented:
Thanks for the input, questions and ideas.  They are greatly appreciated.

As I looked thru the code to answer your questions I think I found the delay.

The form is closed in a routine in the event of a button 'Save And Exit' click.  Here is the event:
Private Sub btnSaveAndExit_Click()
'
Errors_So_DoNotExit = False

On Error Resume Next
Me.Dirty = False     'save the record
'
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'                       
                               If IsDeveloper Then
                               Else
                                 On Error GoTo btnSaveAndExit_Click_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


If Nz(Me.ID, 0) = 0 Then
    MsgBox "Nothing has been entered, there is nothing to save"
    Exit Sub
End If

If Errors_So_DoNotExit Then   'this is set in Any_Edit_Errors which is invoked by me.dirty=false
    Exit Sub
End If
'
checkAnyAssociatedUpdates  ' this checks the subform and the checkbox to see if it shold be applied anywhere else
'
On Error Resume Next
DoCmd.Close acForm, Me.Name
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
btnSaveAndExit_Click_Error:
                               sysErrorHandler Err.Number, Err.Description, "btnSaveAndExit_Click", "Form_frmCallResult_Manual_single", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------


End Sub

Open in new window


When I commented out the call to 'checkAnyAssociatedUpdates' the delay time was reduced drastically, to an acceptable level.

Here is the routine 'checkAnyAssociatedUpdates' and the routine it calls 'writeAllCommentsEventsEtc'

Private Sub checkAnyAssociatedUpdates()
'
Dim wkNumAssociatedUpdated As Long
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'                            
                               If IsDeveloper Then
                               Else
                                 On Error GoTo checkAnyAssociatedUpdates_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

wkNumAssociatedUpdated = 0
'
' First Add Any BRT's in tblCallResult_Manual_Sub to the table of brt's to be processed.


DoCmd.SetWarnings False
insertString = "Insert Into tblSelectedBRTsToProcess_Local ( [BRT] ) " & _
                                                    " Select [BRT]   " & _
              " From tblPropertyCallResult_Sub " & _
              " Where [CallResultHdrID] = " & saveCallResultHeaderID
DoCmd.RunSQL insertString
'
' Now Process all of the BRT's (if any) that ended up in tblSelectedBRTsToProcess_Local.  They could have gotten
' here by being entered manually, as a result of a matching Phone number or the result of a matching name.
' It is possible that the same BRT could be in the table multiple times from each of the three matches so
' a qry is over the top which als pulls in any need info for the update
'
selectString = "select * from qrySelectedBRTsToProcess_Local "
'
Dim rsIn As ADODB.Recordset
Set rsIn = New ADODB.Recordset
rsIn.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
'///////////////////////////////////////
If rsIn.EOF Then
    'Exit Sub
Else
    If rsIn.RecordCount > 0 Then
        '
        rsIn.MoveFirst
        '
        While Not rsIn.EOF
            wkNumAssociatedUpdated = wkNumAssociatedUpdated + 1
            '
            writeAllCommentsEventsEtc savePhoneNum, _
                                      Nz(rsIn![PropertyID], 0), _
                                      Nz(rsIn![BRT], 0), _
                                      saveCallResultID, _
                                      saveComment, _
                                      saveDateTimeRcvd, _
                                      saveCallTypeID, _
                                      Nz(rsIn![OwnerName], ""), _
                                      saveDateOfCall, _
                                      saveTimeOfCall, _
                                      saveCallerTypeID, _
                                      saveInterestedPartyID
            '
            rsIn.MoveNext
        Wend
            
    End If
End If
'
rsIn.Close
Set rsIn = Nothing
'
'
If wkNumAssociatedUpdated > 0 Then
    MsgBox Trim(Str(wkNumAssociatedUpdated)) & " Associated Record(s) were updated."
End If
'

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
checkAnyAssociatedUpdates_Error:
                               sysErrorHandler Err.Number, Err.Description, "checkAnyAssociatedUpdates", "Form_frmCallResult_Manual_single", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

End Sub
Private Sub writeAllCommentsEventsEtc(passedPhoneNum As Double, _
                                      passedPropertyRecID As Long, _
                                      passedBRT As Long, _
                                      passedCallResultID As Long, _
                                      passedComment As String, _
                                      passedDateTimeRcvd As Date, _
                                      passedCallTypeID As Long, _
                                      passedOwnerName As String, _
                                      passedDateOfCall As Date, _
                                      passedTimeOfCall As Date, _
                                      passedCallerTypeID As Long, _
                                      passedInterestedPartyID As Long)
'
' This routine writes call results for any additional records selected or specified by the user.
'
' Update PropertyID reflect the call status
'
' Write Dialer Import History Rec
'
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
'                    
                               If IsDeveloper Then
                               Else
                                 On Error GoTo writeAllCommentsEventsEtc_Error
                               End If
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------

''''''''''''DoCmd.Hourglass True
'
Dim thisHistoryDialerImportID As Long
'
' not needed in new system
'
'thisHistoryDialerImportID = writeDialerResultRec(passedPropertyRecID, _
'                                                 passedBRT, _
'                                                 passedPhoneNum, _
'                                                 passedCallResultID, _
'                                                 passedDateOfCal, _
'                                                 passedTimeOfCall, _
'                                                 passedCallerTypeID, _
'                                                 passedInterestedPartyID, _
'                                                 passedCallTypeID, _
'                                                 passedComment)
'
' write call event
'
'writeEventLog eSystemEvent.eManualCallResult, _
'              passedBRT, _
'              passedPropertyRecID, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              0, _
'              thisHistoryDialerImportID, _
'              passedComment, _
'              0, _
'              0, _
'              0, _
'              0, _
'              passedDateTimeRcvd, _
'              passedCallResultID, _
'              passedPhoneNum
'
''
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Add Update Extra Phone Number "

'
updateOrAddPhoneNumerResult passedPropertyRecID, _
                            passedBRT, _
                            Nz(Me.PhoneNum, ""), _
                            Nz(Me.CallResultID, 0), _
                            Nz(Me.InterestedPartyID.Column(5), 0)

logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Write Extra Comment "
'
writePropertyComment passedPropertyRecID, passedComment, , , passedBRT
'
'''''''''''''DoCmd.Hourglass False
'
logTimingRecsInErrorLog "frmCallResult_Manual_Single, Before Add Extra Call Result Record "
'
' Now write call result record for this associated call
'
Dim rsOutDtl As ADODB.Recordset
Set rsOutDtl = New ADODB.Recordset
rsOutDtl.Open "tblProperty_CallResults", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'
With rsOutDtl
    .AddNew
        !PropertyID = passedPropertyRecID
        !BRT = passedBRT
        !OwnerName = passedOwnerName
        !RelatedEventID = 0     ' Not Used
        !PhoneNum = passedPhoneNum
        !CallResultID = passedCallResultID
        !DateOfCall = passedDateOfCall
        !TimeOfCall = passedTimeOfCall
        !CallerTypeID = passedCallerTypeID
        !InterestedPartyID = passedInterestedPartyID
        !UserAdded = GimmeUserName
        !DateAdded = Now
        !CallTypeID = passedCallTypeID
        !Comment = passedComment
        !PropertyCommentID = 0 ' not used
    .Update
End With
'
rsOutDtl.Close
Set rsOutDtl = Nothing

logTimingRecsInErrorLog "frmCallResult_Manual_Single, After Add Extra Call Result Record "

'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------
                               On Error GoTo 0
                               Exit Sub
writeAllCommentsEventsEtc_Error:
                               sysErrorHandler Err.Number, Err.Description, "writeAllCommentsEventsEtc", "Form_frmCallResult_Manual_single", "VBA Document"
'- - - - - - - - - - - - - - - - - - - - - - - - - - G E N E R A T E D  E R R O R  C O D E ----------------



End Sub

Open in new window


What these routines do:  The purpose of the main form is to log information about call center calls.  Date, time, who was spoken to etc..
There is a subform on the form bound to linked table 'tblPropertyCallResult_Sub'.  On it, the users can manually enter account numbers (BRT's) that should also have the call information posted to them.  Since this is a linked table it is likely that at a given time, several other users have a call screen open that is linked to this table also.

There is another button on the main form that allows the users to search for properties in the system that have either the same phone number or owner as the call being logged on the main form.  Any accounts returned as a result of this lookup are put in a local table 'tblSelectedBRTsToProcess_Local'

This routine is:
1. Inserting any BRT's in 'tblSelectedBRTsToProcess_Local' into the table 'tblPropertyCallResult_Sub' bound to the subform
2.  reading thru the entries of the subform table to find out if any other accounts should have the call information posted to them.
3. Posting the call logging information for those accounts.

One key item to note is that in most cases, and for sure in my internal testing,  No BRT's were entered into the subform or selected to be placed in the local table.

Even though there was no data for this routine to process it is causing a delay.  As soon as I commented the call to '' the delay dropped to be the same as any other form that is exiting.

This routine is running some pretty simple I/o operations that in reality have no records to process.

Any ideas on how to retain the functionality of the routine but speed up the time it takes to exit the form?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Nothing specific, but in general:

1. This:
select *

Is a bad idea.  You should only pull the fields you need.

2. Check your indexing on these tables.   Generally should be indexed on any file you use in a WHERE clause and also not be over indexed either.

 When you insert records, all those indexes need to be updated.

  Indexes should not be created on fields with low cardinality either (those with few values, like a yes/no field, which has only two possible values for any record).  In that case, an index is a waste.

Jim.
0
PatHartmanCommented:
You should not be forcing a record to be saved if it wasn't updated.

As far as the subform logging goes, it would probably be better to do it one record at a time in the subform's AfterUpdate event.  What you are doing adds a lot of unnecessary overhead.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlcktmguyAuthor Commented:
Still working on a permanent solution.  A temporary solution was to remove all back end processing from the form.  I commented the execute of 'checkAnyAssociatedUpdates' and the form unloads immediately.   But the user still wants the functionality the routine had.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.