Order of Records in a Sub Form Table? Macros!

[Reimondo]

Hi,

I've got a sub form table which is created via a "create table" button on the form which is linked to a macro. The macro just contains a series of runSQL actions which insert records into the database and I then have a "ShowAllRecords" action at the end which refreshes the table and displays the newly inserted records.

But my problem is that occasionally the table does not display the records in the same order they were inserted.

Any one know why this might happen?
Most of the time, it will display the records exactly in the order I inserted them by. As I don't have any sort filters or anything on the tables so they should just display the records as inserted?


The second problem i have is that when i am creating a new record, I have to run the macro twice in order for the records to be displayed in the table (and for the data to be inserted into the database)? Which effectively runs the series of runSQL statements twice and inserts them in twice.
But if it is not a new record, I can simply execute the macro once, and it inserts the records into the database and displays the new records with no problems at all?


Any help with any of these problems will be most appreciated.

Also, as I'm not very familar with VB, then non-VB advice will be most appreciated.


Thanks,

Raymond

 

[scriverb]

Without just making wild guesses it would be better if you would post the macros SQL actions that you are running. It is hard to say what is causing these type of problems without seeing the details.

I will take a look and see if we can help you. Bob Scriver

 

[JeremyNYC]

Tables do not store records in any particular order, and you can not safely count on them to do so. If you need your records ordered, you have to get them from a query. If you don't have an appropriate field on which to sort (autonum, for example), just add either an autonumber field or a field that gets filled with the current time (Now()) and order by that field.

Try putting in a requery command after you insert the records.

Also, bag the macros. They're a pain once you start getting into anything interesting with Access, they do not allow any error handling, and they are a bit mysterious. It will hurt some while you make the transition to VBA, but you'll be quite happy you did.

Jeremy ==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[scriverb]

JeremyNYC: Haven't seen you in any posts lately. Maybe we have been just selecting opposite threads or something. You had asked about emailing me previously. Did you get my response? If you need to get in touch just go to my profile. Bob Scriver