Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Order by Subform Value (Access 2002)

    I would like to sort the records in a form by a value in a subform. I've tried entering the following in the Order By property box of the form: [subCnt].[Form]![Cnt]. I also tried setting a textbox to this value and using this in the property box. Neither of these sorted the records. What is the correct way to do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order by Subform Value (Access 2002)

    I don't think it makes much sense to sort a main form by a value in a subform. A subform can contain multiple records for each record in the main form, so how should the main form be sorted? On the first value in the subform, or the last, the lowest, the highest, or the average of the values?

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Order by Subform Value (Access 2002)

    Let me explain. The value I want to sort the form by is generated by a query. At first I used this query as part of the recordsource for the form so I could sort by the value. This worked fine, but the form became unupdateable. I thought that by putting this value in a subform I could sort by it and the form would still be updateable.

    What is the correct way to accomplish this?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order by Subform Value (Access 2002)

    That depends on the exact situation. For example:
    <UL><LI>A query involving more than one table is not updateable if there is no unique key on the "one" side of the join between the tables.
    <LI>Sometimes it helps to set the "Unique Records" property of the query to Yes (this corresponds to SELECT DISTINCTROW in SQL.)
    <LI>Totals queries are not updateable.[/list]If you would like more help, post some details about the table(s) and query involved.

  5. #5
    Lounger
    Join Date
    May 2003
    Location
    Whittier, California
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Order by Subform Value (Access 2002)

    The involved query is a totals query. The records on the form are students and I want the ones lowest in a requirement to appear first. I have to generate their present credits by totaling a field in another table. The 2 tables are linked by a one-to-many relationship, but having to use a totals query causes the problem. Any ideas?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order by Subform Value (Access 2002)

    Create a query based on the Students table only. Add a calculated field:
    Credits: DSum("SomeField", "SomeTable", "StudentID=" & [StudentID])
    where "SomeTable" is the name of the other table, "SomeField" is the name of the field you have to total, and "StudentID" is the name of the field in "SomeTable" that links it to the Students table. I have assumed that it is numeric. You can sort on this calculated field. The query will be updateable.

Posting Permissions

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