Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Peter_Thesiger View Post
    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 Files\Microsoft Office\OFFICE11\MSACCESS.EXE" /decompile

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

    "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "...path to your db here..." /decompile

    I then compact/repair and recompile.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by Peter_Thesiger View Post
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by Peter_Thesiger View Post
    ... 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.
    Wendell

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

  10. #10
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Peter_Thesiger View Post
    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).

  11. #11
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •