Access has encountered a problem and must close

Topic

New Reply

I am using Access 2003 fully patched.

I have a form with a subform and a sub-subform. Prior to the following changes I did not get this problem.

I had to move the form footer controls onto the subform footer for reasons which don’t really matter. I checked all the controls and the calculated controls and they are all pointing to the correct location for the data. I also made sure all the VBA code is doing the same thing. The problem is that whenever I do a recalc I get this Microsoft error. I am assuming I have missed something in the changes but not sure if there is an easy way to find it.

Reply | Quote

Replies

I should have added one more thing. If I open the form in read only mode all the calculated controls work perfectly, but if I hit F9 the error occurs.

Reply | Quote

I am using Access 2003 fully patched.

I have a form with a subform and a sub-subform. Prior to the following changes I did not get this problem.

I had to move the form footer controls onto the subform footer for reasons which don’t really matter. I checked all the controls and the calculated controls and they are all pointing to the correct location for the data. I also made sure all the VBA code is doing the same thing. The problem is that whenever I do a recalc I get this Microsoft error. I am assuming I have missed something in the changes but not sure if there is an easy way to find it.

I’m not sure what causes this problem, but I find that decompiling the database often cures it. I keep a shortcut on my desktop that points to my version of Access and then opens it with the /decompile switch. It looks like this (of course, you location may be different):

“C:Program FilesMicrosoft OfficeOFFICE11MSACCESS.EXE” /decompile

Then just choose which db you want to open. Or, you could modify it to directly open the database:

“C:Program FilesMicrosoft OfficeOFFICE11MSACCESS.EXE” “…path to your db here…” /decompile

I then compact/repair and recompile.

Reply | Quote

I tried that Mark and no luck. However I have finally been able to track down an idea of the cause. If I open the form directly (i.e. from the forms list) everything appears to work correctly. However if I open it from the calling form, which is a list of available quotations that can be filtered by the user to find the one they want, then it crashes. Below is the code I use to call the form that is having problems:

Hierarchy:
frmQuotes_List (contains subQuotes_List subform) – selects the Quote to edit
calls
frmQuote_Edit (in either read only or edit mode) – has subform subQuote_edit_Room which in turn has subform subQuote_Edit_Room_Item
*** this is the form that on Recalc generates the error

Code use to open frmQuote_Edit

Code:

    stLinkCriteria = "[Quote_ID] = " & Forms!frmQuotes_List!subQuotes_List!Quote_ID _
        & " AND [Version_No] = " & Forms!frmQuotes_List!subQuotes_List![Version_No]
    
    'Check users security group
    If CheckGroup("GTWReadOnly") Then 
        stOpenArgs = "Readonly"
        DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly, , stOpenArgs
    Else
       'Only the latest revision of the Quote can be edited
       If Me!ComboRevisions = "Latest Version" Then
            If MsgBox("Do you wish to edit this Quotation?", vbYesNo) = vbYes Then
                'If Quote already in use by another user then cannot edit
                 If Forms!frmQuotes_List!subQuotes_List![QuoteLocked] = True Then
                    MsgBox ("This Quote is already locked by another user. You cannot make any changes")
                    stOpenArgs = "Readonly"
                    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly, , stOpenArgs
                Else
                    Response = AddNewRevision(Forms!frmQuotes_List!subQuotes_List![Quote_ID], Forms!frmQuotes_List!subQuotes_List![Version_No], "List")
                    stLinkCriteria = "[Quote_ID] = " & Forms!frmQuotes_List!subQuotes_List!Quote_ID _
                        & " AND [Version_No] = " & Forms!frmQuotes_List!subQuotes_List![Version_No]
                    stOpenArgs = "Edit"
                    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit, , stOpenArgs
                End If
            Else
                stOpenArgs = "Readonly"
                DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly, , stOpenArgs
            End If
        Else
            stOpenArgs = "Readonly"
            DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly, , stOpenArgs
        End If
    End If

I cannot see anything immediately wrong with this code and in fact I am using almost identical code in another database with no problems at all

Reply | Quote

… However if I open it from the calling form, which is a list of available quotations that can be filtered by the user to find the one they want, then it crashes.

Have you tried putting a breakpoint at the beginning of the procedure you listed, and then single-stepping through it until you hit the line of code where it crashes? That should give you some additional clues as to what is causing the problem.

Reply | Quote

stLinkCriteria = “[Quote_ID] = ” & Forms!frmQuotes_List!subQuotes_List!Quote_ID _
& ” AND [Version_No] = ” & Forms!frmQuotes_List!subQuotes_List![Version_No]

I don’t know if this has anything to do with it, but the syntax is not usual when referring to a control within a subform. Here is a more historically standard syntax:

stLinkCriteria = “[Quote_ID] = ” & forms(“frmQuotes_list”)!subQuotes_List.form![Quote_ID] & …

I prefer using the dot notation myself. You have to make sure your control on the form does not have the same name as the field or Access can get confused (and maybe exit?). When you do this, the dot notation works well and you get syntax checking at compile time – which is always nicer than run time!

And if you are running this code within the form “frmQuotes_List”, then change “forms(“frmQuotes_List”) to “me”. Then we get “me.subQuotes_list.form.Quote_ID”. Way simpler and clearer.

I think I have all this right. If you have questions about syntax, any of the Access xxxx Developer’s Handbooks by Litwin, Getz, and company are excellent references (xxxx = Access version).

Reply | Quote

The only thing I can suggest is to put is to go to debug (or even just insert some messages boxes) so you can see exactly what is being contained in those option variables (stLinkCriteria, stOpenArgs, etc.).

Reply | Quote

Tried that and everything looks OK. I have even tried creating a temporary list form that has almost no code in it other than opening the edit form in readonly mode for a particular Quote – same problem. I am not sure if it has any significance but stOpenArgs is a public variable defined as variant. Could this affect anything?

Reply | Quote

Tried that and everything looks OK. I have even tried creating a temporary list form that has almost no code in it other than opening the edit form in readonly mode for a particular Quote – same problem. I am not sure if it has any significance but stOpenArgs is a public variable defined as variant. Could this affect anything?

I don’t think so, but you might want to change your .OpenForm so it reads …, stOpenArgs & “”

This would handle the situation where stOpenArgs is Null, which is a valid value for a variant, in case Access doesn’t like a Null there.

BTW, the reason one uses a prefix of “st” or “str” is to denote a string field, so that whenever you see this field, you know what type of field it is. Kind of counter-productive to then define the field as a variant. You should make it varOpenArgs. This will save you from many problems in the future.

Reply | Quote

Yes I had already tried that and the error occured after all the code had apparently executed.

I have now solved the problem by going back to the version of the edit forms prior to any changes.. The objective of the changes was to make sure that after a recalc the subform returned to the correct record. The easiest and most sensible was to move the calculated fields to the subform and then use the Bookmark feature. Anyway what I have done is by storing the relevant infobefore the recalc, use setfocus to go back to the subfom and then use the bookmark code.

It would be nice to know why it didn’t work but no more time to waste.

Many thanks for all your help

Reply | Quote

I have had this problem and it only seems to occur when there is a calculated control on the subform. When there is an event that causes the calculated control to be recalculated the error occurs and Access shuts down.

Although it may be a bit of tough redesign work I would urge you to move your calculated controls back onto the main form. Another option would be to modify the recordsource of the subform so that the calculation occurs within the recordsource (query) and not in the control itself.

HTH

Reply | Quote