Intermittent (cumulative?) crashing in Access XP

[pbbriggs]

I have been struggling for several weeks to try to figure out the root of a problem in my Access application. I am working in Win XP and Access XP. I am experiencing frequent crashing, as described below, but without a consistent cause that I am able to identify.

I don't think my application is overly large - 10 tables, about 50 forms. The majority of the user's time will be spent in one form. When I try to use this form in a test situation, it frequently crashes Access altogether (with the &quot;Access has experienced an error and needs to close... Do you want to send an error report to Microsoft?&quot; window). What is driving me INSANE is that it does not seem to be a particular action that causes the crash; therefore, I am at my wits' end to try to figure out what the problem is. >:-<

Generally, the form has two subforms. The user chooses item(s) from each subform (by clicking a checkbox), then clicks a button which runs several queries and then requeries the subforms. Clicking this button is what typically causes Access to crash (although, to be honest, it sometimes crashes when I do other things as well, but the most common action is clicking this button).

I read about memory leak and made sure that I closed all open recordsets and set them to nothing. I also tried monitoring the &quot;CPU usage&quot; window to see if there seemed to be a cumulative growth in memory usage, but this does not seem to be the case. While the CPU usage does seem to spike pretty high while queries are run, etc. (up to 75-80%), it always returns to 0% (or thereabouts) after the processing is done. So I suspect it is not memory leak.

I know that this description is awfully vague for anyone to help, but I guess the essence of my question is: does anyone know of some sort of cumulative problem that might be occurring in my database, where I could perform the same actions 10 times in a row (for example) without a problem, but then on the 11th time Access would crash?

I have tried several things, such as: entering &quot;DoEvents&quot; lines into the code (to make sure that one action was not &quot;tripping over&quot; the previous action); unbinding the subforms during queries and re-binding them when the queries were done; etc. I also tried putting a breakpoint at the first line of the cmd button code and then stepping through the code every time I clicked on it. When I am in debugging mode like this, I can never get Access to crash. Again, this leads me to believe there is some kind of cumulative (memory or otherwise) issue that is occurring in the real-time environment, that doesn't happen in a debugging environment (?).

I know this is not a great explanation and would be happy to post any code from my application, but after poring over my code I think it must be something more generic than a code issue. I know it's a stab in the dark but I'm hoping someone has had a similar problem and might have an idea that will help.

Thanks in advance

 

[HockeyGuy]

You probably need to copy and paste the code that executes on the click of your button so that people might see what is going on.

I have never run into an Access memory crash, but then again I have loads (521 meg) of memory.

I am new here myself and cannot promise to find my way back to your question - but I will try. In any event, others could help if they can see what is being executed form the click of the button.

Rick

 

[pbbriggs]

Thanks, Rick.
I know that posting my code will just open me up to all kinds of criticisms about my coding style, etc., but I guess I am willing to run that risk in case anyone sees something strange in here that might be causing my problem.
I am not sure how easy it will be to follow this code without understanding the app. Essentially, this form allows the user to compare ledger data (sometimes I refer to it as the &quot;GL side&quot to bank data. Each batch of data is presented in its own continuous form (side-by-side on a main form). The user can declare items to be a match (typically one item on each side = one item on each side, but it also allows one-to-many matches and even one-sided matches if the total adds up to zero -- e.g., a <$4.00> item and a $4.00 item). This command button is the &quot;match&quot; button which allows the user to match items after having identified them by clicking check-boxes next to them.

Typically, when Access crashes, it has made it through the queries (which are all contained in the complex &quot;If..then&quot; sequence) and seems to be attempting the requeries at the bottom of the code. However, it does on occasion crash without the queries being completed.

The &quot;Unbind&quot; and &quot;Bind&quot; functions (which I have not posted) were just an attempt to try to isolate the problem by un-binding the two subforms while the queries were executed, then re-binding them before requerying the subforms. This has not solved the problem but does make for cleaner code so I have left it this way.

Any ideas would be greatly appreciated, although general bashing of my code (if not pertinent to this problem) is not really what I'm looking for. Do I know there are sometimes more efficient ways to do things than what I am doing? Yes, I do. I'm still learning.

Thanks in advance to anyone who takes the time to read this lengthy code and has any suggestions.

Just one final note - I'm still not entirely convinced that it is related to this button or even this form. This is where the crash most frequently occurs, but is also the most frequently-used form/button.

Private Sub cmdMatches_Click()
On Error GoTo Err_cmdMatches_Click
Unbind 'un-bind subforms from recordsources before performing queries
'Temporarily suspend the warning messages.
'these messages pop up to tell user that she is about to append rows
'(in the matched table) and delete rows (in both of the unmatched tables)
DoCmd.SetWarnings False
Dim stMatching As String
Dim stDelBOA As String
Dim stDelGL As String
Dim stOne As String
Dim stOneB As String
Dim stUser As String
Dim stOneB2 As String
Dim stOneB3 As String
Dim stOneB4 As String
Dim stOneL2 As String
Dim stOneL3 As String
Dim ctlUMB As Control
Dim ctlUMG As Control
stMatching = &quot;qryMatches&quot;
stDelBOA = &quot;qryUnmatchedBOAUpdate&quot;
stDelGL = &quot;qryUnmatchedGLUpdate&quot;
stOne = &quot;qryOneSidedMatch&quot;
stOneB = &quot;qryOneSidedMatchB&quot;
stUser = &quot;qryOneSidedLedgerUser&quot;
stOneB2 = &quot;qryMatchesOneL&quot;
stOneB3 = &quot;qryUpdateDateOneL2&quot;
stOneB4 = &quot;qryClearTempOnes&quot;
stOneL2 = &quot;qryMatchesOneL&quot;
stOneL3 = &quot;qryUpdateDateOneB2&quot;
Set ctlUMB = Forms!frmManualMatching!frmUnMatchedBank
Set ctlUMG = Forms!frmManualMatching!frmUnMatchedGL
If (DCount(&quot;[GLAmt]&quot;, &quot;QuadrantUnmatched&quot;, &quot;[GLMatch]=True&quot) > 0 Then 'items on GL side
If (DCount(&quot;[BAmount]&quot;, &quot;BOAUnmatched&quot;, &quot;[BMatched]=True&quot) = 0 Then 'no items on bank side
'items on GL side but not bank side
If (DSum(&quot;[GLAmt]&quot;, &quot;QuadrantUnmatched&quot;, &quot;[GLMatch]=True&quot) <> 0 Then
'(If there are items on the ledger side which do not add up to 0)
MsgBox &quot;These items cannot be matched because the amounts are different. Please select items with the same dollar amount.&quot;, _
vbCritical, &quot;Incorrectly Matched Items&quot;
Else
'one-sided match situation on ledger side
Dim strConfG As String
strConfG = MsgBox(&quot;Are you sure you want to make a one-sided match on the ledger side?&quot;, vbYesNo, &quot;One-sided GL Match&quot
If strConfG = vbYes Then
CurrentDb.Execute stUser, dbFailOnError 'update &quot;matched by&quot; to current user
CurrentDb.Execute stOneB, dbFailOnError 'run one-sided match qry
'runs a query which adds the matched items to the matched items table
CurrentDb.Execute stOneB2, dbFailOnError
CurrentDb.Execute stOneB3, dbFailOnError
CurrentDb.Execute stOneB4, dbFailOnError
'deletes the matched items from the &quot;unmatched BOA items&quot; table
CurrentDb.Execute stDelBOA, dbFailOnError
'deletes matched items from &quot;unmatched ledger&quot; table
CurrentDb.Execute stDelGL, dbFailOnError
'clears the message box (re: matched or mismatched items)
Message.BackColor = 12632256
Message = &quot;&quot;
ElseIf strConfG = vbNo Then
MsgBox &quot;One-sided match not made.&quot;, vbOKOnly, &quot;No Match Made&quot;
End If
End If
ElseIf ((DSum(&quot;[BAmount]&quot;, &quot;BOAUnmatched&quot;, &quot;[BMatched]=True&quot) - (DSum(&quot;[GLAmt]&quot;, &quot;QuadrantUnmatched&quot;, &quot;[GLMatch]=True&quot)) = 0 Then
'this is a normal situation, where there are item(s) to be matched on both sides
'(if items to be matched on GL side, and the diff between sides is 0)
'then run a query which adds the matched items to the matched items table
DoEvents
CurrentDb.Execute stMatching, dbFailOnError
'deletes the matched items from the &quot;unmatched BOA items&quot; table
DoEvents
CurrentDb.Execute stDelBOA, dbFailOnError
'deletes the matched items from the &quot;unmatched GL items&quot; table
DoEvents
CurrentDb.Execute stDelGL, dbFailOnError
'clears the message box (re: matched or mismatched items)
Message.BackColor = 12632256
Message = &quot;&quot;
Else
MsgBox &quot;These items cannot be matched because the amounts are different. Please select items with the same dollar amount.&quot;, _
vbCritical, &quot;Incorrectly Matched Items&quot;
End If
Else
If (DCount(&quot;[BAmount]&quot;, &quot;BOAUnmatched&quot;, &quot;[BMatched]=True&quot) = 0 Then
MsgBox &quot;Please select one or more items to match&quot;, vbCritical, &quot;Select Items&quot;
ElseIf (DSum(&quot;[BAmount]&quot;, &quot;BOAUnmatched&quot;, &quot;[BMatched]=True&quot) <> 0 Then
'(If there are items on the Bank side which do not add up to 0)
MsgBox &quot;These items cannot be matched because the amounts are different. Please select items with the same dollar amount.&quot;, _
vbCritical, &quot;Incorrectly Matched Items&quot;
Else
'one-sided match situation on bank side
Dim strConfB As String
strConfB = MsgBox(&quot;Are you sure you want to make a one-sided match on the bank side?&quot;, vbYesNo, &quot;One-sided Bank Match&quot
If strConfB = vbYes Then
DoEvents
CurrentDb.Execute stOne, dbFailOnError 'run one-sided match qry
'runs a query which adds the matched items to the matched items table
CurrentDb.Execute stOneL2, dbFailOnError
CurrentDb.Execute stOneL3, dbFailOnError
CurrentDb.Execute stMatching, dbFailOnError
CurrentDb.Execute stOneB4, dbFailOnError
'deletes the matched items from the &quot;unmatched bank items&quot; table
CurrentDb.Execute stDelBOA, dbFailOnError
'deletes matched items from &quot;unmatched Quadrant&quot; table
CurrentDb.Execute stDelGL, dbFailOnError
'clears the message box (re: matched or mismatched items)
Message.BackColor = 12632256
Message = &quot;&quot;
ElseIf strConfB = vbNo Then
MsgBox &quot;One-sided match not made.&quot;, vbOKOnly, &quot;No Match Made&quot;
End If
End If
End If

DoEvents
Bind 're-bind subforms to recordsources
DoEvents
'requery subforms to reflect deleted records
ctlUMG.Requery
ctlUMB.Requery
DoEvents
RebindTotals
Set ctlUMB = Nothing
Set ctlUMG = Nothing

Exit_cmdMatches_Click:
Exit Sub

Err_cmdMatches_Click:
MsgBox Err.Description
Resume Exit_cmdMatches_Click

End Sub

 

[pbbriggs]

In case anyone is reading this, I finally figured out what was causing my crash and posted it in Thread 702-772398. Thank you to anyone who read my long-winded code and tried to help.

Patricia