Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Posts
    30

    Unanswered: Subform won't open if DisplayOrder is set

    Access 2002

    I have a subform applied to a form via the wizard. The subform opens on its own (directly as an independent form) without error and with or without the OrderBy property set to a column in the query.

    However, as a subform, it won't open if the OrderBy property is set on the subform. I get an error saying that the name of the subform is not a table or a view in the query.

    ???? What's up with that?

    Thanks for any help!
    FlyGirl

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by gailschipper View Post

    ...it won't open if the OrderBy property is set on the subform...
    It's probably just me, but I'm not clear as to what you're actually saying, here! Are you saying you have this problem if the Form the Subform is based on has its OrderBy Property set to something, or are you saying if the Main Form has its OrderBy Property set to the Subform Control?

    If it is the latter, that's not surprising. OrderBy, after all, is merely sorting, and while you can sort on a Field, you cannot sort on a Form, which is what a Subform Control holds.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2002
    Posts
    30
    Sorry if that was unclear. Let's try this:

    I have two forms: frmChildData and frmParentData. I wish to use frmChildData as a subform on frmParentData. I wish to have the data in frmChildData sort by FieldX which is a field, any field, in the data set for frmChildData.

    No error occurs and everything is just fine if I open frmChildData separately with its OrderBy set to FieldX.

    No error occurs and everything is just fine if I open frmParentData with frmChildData as a subform IF there is no OrderBy set on frmChildData.

    However, if I have frmChildData as a subform on frmParentData AND frmChildData has it's OrderBy set to FieldX, I get an error. After clearing the error frmParentData will open but the subform, frmChildData, will not display. I was incorrect about with my initial statement of the error message. It actually says something about 'frmChildData' is incorrect as a column prefix in the query. The error occurs prior to the FormLoad event.

    My best guess is that frmParentData is corrupted and I'll have to rebuild it but I was wondering if anyone had an idea on a fix for this.
    FlyGirl

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Having never heard of this problem before, and not being able to reproduce the problem, using 2003 or 2007, I suspect that you may be correct! There are a number of approaches for addressing corruption, but the first thing to try, when suspecting corruption at a Form-level or entire Database-level, is to create a new, blank file and import everything into it. Simple enough thing to do, only takes a minute or two, and works a huge percentage of the time!

    Here's a link to Allen Browne's article giving other strategies for recovering from corruption

    Microsoft Access tips: Standard recovery sequency

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by gailschipper View Post
    It actually says something about 'frmChildData' is incorrect as a column prefix in the query. The error occurs prior to the FormLoad event.
    I don't believe there's something wrong with your database nor with one of the forms.

    If there is a reference to frmChildData in the query (which the error message seems to indicate), this query can only return a data set when the form frmChildData is open or, more precisely, when frmChildData is a member of the Forms collection of the Application object. However, when a form is open as a subform, it does not appear in the Forms collection. This explains why you can open frmChildData as an independent form without any problem but not as a subform.
    Have a nice day!

  6. #6
    Join Date
    Nov 2002
    Posts
    30
    Today I did have a few minutes to create a new form with the same base data set as frmParentData and added frmChildData as a subform with the OrderBy set. It opens with no errors. I'm voting for a corrupted form definition in the mighty Access Black Box.

    Sinndho--an elegant discussion of the theory. What would be the practice for fixing the issue? There is no reference to the data in the child form anywhere in the parent form's query or code.
    FlyGirl

Posting Permissions

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