Prevent Sorting in a Form

[yippie_ky_yay]

Hello Forum,

a co-worker asked me to prevent their form from sorting. She wanted the records to be sorted in the order they were entered, but they kept sorting by the id number (which was the primary key).

I figured it was because of this that it kept sorting - to accomplish her request, I added a new auto-number field to her table and sorted on that.

I'd hate to have added a field for no reason - was that the best way do you think? or could I have done something else?

Thanks in advance,

-Sean

 

[pono1]

YKY,

In the absence of any more information (like, did she ask nicely? why no autonumber to begin with? etc) I like your solution.

(Prognosticating: One day she may ask if you can sort it by the actual date the record was created -- which the autonumber effectively does -- and, too, if she can see that date on each record.)

Tim

 

[yippie_ky_yay]

Thanks for replying Tim!

Is there not something else behind the scenes though? I notice that when I initially looked at the table, it was sorted by the primary key - when I added the autonumber it correctly numbered them in the order they were created (so the sequence went 1, 2, 8, 5, 3, 4 ...etc).

I'm thinking she may have ordered it by the first field and then saved it. Is there a way to turn the sorting off so I could verify this?

Thanks,

-Sean

 

[Jack Cowley]

Hmmm. The ID number is the Primary Key and it does not sort properly on that number? Is the number alpha-numeric? In my opinon the Autonumber should be the Primary key, not the ID number unless the ID number is numeric and sequential. It sounds like adding the Autonumber was a good solution...

Jack

 

[yippie_ky_yay]

Hello again Jack - thanks for replying (and thank goodness your working on a Sunday too!)

The problem is that, before I added the autonumber (I still haven't officially done this - just in a copy to test it) it did sort by the primary key (which is alpha - ie 1a, 2a, 3c, etc...).

The forms' control source is this table - so when they shut down the form and reload it, they expected the results to be in the order they entered them.

There were already about 40 records in the table and they were all ordered by their primary key. When I added the autonumber, it didn't give them the number according to the sort order - it gave them the number by which the record was created! (which was what I was praying for). This also leads me to believe that Access must keep track of record creation - so I am wondering how I can tell Access to sort based on that and eliminate the need for a new autonumber field.

I'll probably end up using the autonumber, but now I am mostly just curious!

Thanks,

-Sean

 

[Jack Cowley]

As you have found out Access sorts 'text' numbers differently than 'number' numbers. With your current primary key Access will not necessarily display the records in the order in which the records were entered. You can use a query and the Val() function to sort your form so the records are in the order entered. Create a query based on the table and in a new column put this in top (Field) line:

Sorted: Val([NameOfPrimaryField])

In the Sort field select Ascending. Uncheck the show box if you like.

Use this query as the Record Source for your form.

hth,
Jack

 

[yippie_ky_yay]

Thanks again Jack - that's what I'll do!

-Sean

 

[Jack Cowley]

You are welcome.

I would recommend that in future databases you use always use the Autonumber as your primary key and if you want some other ID, as you are doing now, then have that as just another field in your table.

Jack

 

[yippie_ky_yay]

Hey Jack - I always do! It's just that this person's form had 6 subforms in it - all linked by that primary key. It would have been a lot of work for me to give her a "proper" database. It's kinda confusing too because they used the wizard for a lot of stuff so I just wanted to give them the best solution given the time frame (they need it tomorrow am).

I just finished and it's all working great!

Thanks again!

-Sean

 

[Jack Cowley]

Sean -

Ah ha! The old 'fix the current problem and I need it tomorrow' syndrome! Isn't it fun...

Glad you got the problem sorted and now you will be a hero of immense proportions...

Jack

 

[yippie_ky_yay]

Wait until I explain that I had to re-configure the server and update the registry on all the client computers in order to get it to work - they'll think I'm a genius! (...the guilt goes after a while)

-Sean

 

[Jack Cowley]

Zowie! That ought to earn you a few points and a merit raise!

Jack

 

[yippie_ky_yay]

Thank you Pat - that does explain everything! I always thought that compacting only dealt with freeing memory - now I know better!

-Sean