Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Prepended Data (97)

    1. I have 2 related tables: 1 to many: tblPeople and tblAAHistory
    2. 1 form with subform: see above: main form = frmPeople; sub form = frmAAHistory
    3. When I enter data thru the subform this occurs: Record 1 of main form leads to the subform with many records: like 4 records and it opens to record 1 of 4. (this reflects the data in the table. ) (This data was copied)(we are now entering data for the first time)
    4. Now when I am in the subform at record 1 of 4 and I hit the arrow key to create a new record like record 5 of 5........The data goes in the tblAAHistory PREPENDED(BEFORE) the already existing records. This is confusing as when I close and then open the form and go to the subform - record 1 of 5 is the last record I entered. This is not what I want. I have records for 1997, 1998, 1999, and 2000. So when I enter record 5 for 2001, I want it to be record 5 of 5.

    What have I done wrong?

    Soc

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Prepended Data (97)

    I don't think you've done anything wrong - Access doesn't necessarily present data in the order you enter it. To solve your problem, you need to sort the data in the data source for your subform. You can create a query to sort it and make that your data source, or you can invoke the query builder from the data source properties text box and do the sort as stored SQL - the latter would be my preference. When you sort the data by year, that should give you the desired result.
    Wendell

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prepended Data (97)

    Hi,
    I went into the Table Properties and in the row - Order By - I typed in year. That seemed to work, but I haven't done more than one test.

    Does that sound like it would work?

    Soc

  4. #4
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prepended Data (97)

    I've done more testing. It appears to work in my db and my version of MS Access 97. However the fix I alluded to in my previous post is not working in MS Access 2000. Any ideas?

    The previous fix: open table in design; click properties; order by row = Year; This worked in my version of MS Access.

    Soc

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Prepended Data (97)

    You are doing what is called "applying a filter" to cause the table to sort in the order that you want. What you really want to do is to put the sort into the data properties of the form. The reason is because people can turn off the filter at will by simply clicking on the remove filter button. In brief, the data property of your subform would read:
    SELECT* FROM tblAAA_History ORDER BY Year;
    Wendell

  6. #6
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Prepended Data (97)

    Are you saying:
    1. open the form in Design view.
    2. Bring up the Properties sheet
    3. Click the Data Tab
    4. In the Order By row type: SELECT* FROM tblAAA_History ORDER BY Year;

    TIA
    Soc

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Prepended Data (97)

    Nope - that isn't it. You are on track through 3, but under 4 you want to do the typing in the property field called "Record Source" - it's the top one. The order by may work for you, but it is also a filter and can be turned off. Your subform should now have tblAA_History as it value which says it pulls data from the table. When you put the SQL statement in it's place, it is now sorted in what ever fashion you specify in the ORDER By clause. You can build the SQL statement by clicking in the Record Source field, then clicking the elipsis (...) just to the right of the property box. It will ask if you want to create a query from the table, and if you say yes, it pops up the query designer grid. Hope this helps.
    Wendell

Posting Permissions

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