May I insert a record into the top of a table?

[jokelogpop]

May I insert a record into the top of a table?
Please help me! Thanks very much!
My email: jokelogpop@yahoo.ca

 

[KenHigg]

No. You can sort it so that it winds up there

 

[The_Doc_Man]

As far as I know, Access tables are stored in the order that the records appeared with only one exception. Otherwise, their actual physical order within the DB is totally unknowable - and immaterial. What matters is any index you have placed on the table. It will be displayed in index order no matter how it was stored.

Ken's suggestion is the way to go. Make the record have a prime key that puts it at the top of the list.

The exception I mentioned is that if you COMPRESS a database, all tables are rewritten to the compressed copy in the same order as indicated by the prime key. That is the ONLY time I know of where you could predict the order of appearance of any db records.

 

[KenHigg]

...if you COMPRESS a database, all tables are rewritten to the compressed copy in the same order as indicated by the prime key.

Hum... I did not know that - Thanks

kh

 

[The_Doc_Man]

Yeah, Ken, it works that way 'cause the prime key identifies the order in which the table is read (no matter how it is used) when you do not provide another sort order. I guess compression must not supply a sort order.

The way compression works, Access identifies how much space is needed, then it allocates the space, builds the empty file, and starts copying one table at a time in prime-key order. Which is why post-compression is also the only time tables are contiguous.

 

[jokelogpop]

May I sort a subform?

I have a subform of a table. when I insert a new record, the new record is inserted into the bottom of the tabble. It is so inconvenient. I want to see the new record that I have just insert to the table. May I sort a subform??
This is my problem!
Thanks all of you!

 

[KenHigg]

Yes, you can sort a subform. One way is to use OrderBy property of the form.

But what I suspect you are interested in is having the subform insert a new record at the top and force the existing records down instead of tacking new records on to the bottom. I am not aware of a way to do this in Access...

kh

 

[The_Doc_Man]

Add new record, let it autonumber, then on the subform, sort by that autonumber field in descending order.

 

[KenHigg]

Doc, You know how when you have a subform, when you add a new record, the blank new record always appears at the bottom of the subform? I think this is what they want to show up at the top of the subform...

kh

 

[The_Doc_Man]

The only way I know of to NOT have the new record at the bottom of the auto-scrolling sub-form is to not enter it in the sub-form in the first place. Which kind of defeats the purpose of sub-forms, I guess. Still, it is possible to do this. Requires a couple of control buttons and some VBA code, but it is possible.

Have a set of boxes at the top of the scrolling area that looks just like the records of the subform. But actually have them as unbound fields on the parent form. When the user clicks an "add" button, use recordset operations to update the child table, requery the form, repaint it, and reset the data-entry text boxes. Disallow data entry through the subform. Then that blank record won't be at the bottom.

For completeness, if you have an "Add" button, you need a separate "Abort" button that just erases the data entry boxes. Since those boxes are unbound in this context, it is OK to just erase them.

For sanity, if you click the "Add" button, you should check that at least some of the data-entry text boxes are NOT empty before you store anything. If at least one critical item is NOT in a valid format, highlight it (perhaps with a color change?) and don't Add or Abort quite yet.