Prepended Data (97)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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.

Reply | Quote