Any Access experts out there?

[BookstoreEscapee]

This is a problem I've had for the last year; I couldn't figure it out so I created a work-around, but I'd like to fix it for next year (which starts in a couple weeks) if at all possible.

I have a database for tracking projects at work that uses an auto-number field to automatically assign the next number to each new record; there are several other people who enter information by using a form (another department usually starts the projects). There is another form that I use to track the projects, and a third department also fills in another section on that form.

The problem I'm having is that the project numbers don't stay in order. When I open the form it starts on #55, instead of the last one in the list (same thing if I open the underlying table). I have previous and next record buttons but they don't go in order. To avoid having to click through until you find the one you're looking for (there are about 120 in there by now), I had to make a search button (with instructions, because some of the people using the database are rather computer-illiterate).

I need to create a new database for the next fiscal year, which starts in May, so I'm wondering if anyone has any ideas on how I can have it automatically assign the next number without using the autonumber; or if there is a way to use autonumber but still make it stay in order.

I've tried to figure this out on and off since I created the current database, but I haven't managed to get it to work.

[Geek King]

It sounds like the Previous and Next buttons may be linked to the wrong field, or else the field may be formatted non-numeric so it gets wierd results. Have a look at that first.

I might also suggest adding a first record and last record buttons once you have the issue resolved? They can be more useful than you realize.

[BookstoreEscapee]

I have first and last buttons, too. Problem is it thinks 55 is first. Last takes me to 124...there are 127 records at the moment. The field type is AutoNumber and the field size is long integer. The other option for field size is Replication ID but I don't know what that means. I'll have to look that up.

I also have a few queries that tell me which records need attention (need approval, what I've sent to the floor and what's been complete, etc.) so I know which ones I need to look up.

[Andara Bledin]

If it thinks that 55 is first, and it keeps trying to use 55 for the new record every time you go in, then it looks like your problems are related.

I would suggest first going under the hood and seeing how it is supposed to determine the next available number, as clearing that up will likely change how the other issue resolves.

Unfortunately, I don't really have any experience with the program except in a general user fashion.

^-.-^

[Lachrymose]

Go into the design view of the underlying table.

Under the Field Properties for your autonumber field, is the New Values property set to "Random" instead of "Increment" by any chance?

[BookstoreEscapee]

No, it's set to increment.

It's assigning the next available number properly, it's just that the table doesn't stay in that order. It's really more annoying than a true problem, since I can work around it. But since I need to start a new database for next year, I would like to fix it if I can; it would make things that much easier for certain other, less computer-literate folks who need to put info in. I don't know anything about the programming (Visual Basic, etc) that underlies it all. I never even used Access until I got into this job, other than entering information into a couple databases in my old department, but I couldn't actually do anything with that information or make changes to the database itself. I've learned most of this by doing.

[Geek King]

I understand BE, I learned much the same way, and took a class later to mesh all the little things I learned into a more useful over knowledge. Unfortunately, I've not needed that know-how for many years, and at least a couple of versions. But I'm trying to remember any useful info for you.

It's assigning the next available number properly, it's just that the table doesn't stay in that order.

What field on the table is the main 'key' field on that table? If I remember correctly, you'll need to set the numbering field to be the main key in order to sort the table on it.

[BookstoreEscapee]

What field on the table is the main 'key' field on that table? If I remember correctly, you'll need to set the numbering field to be the main key in order to sort the table on it.

Hmm...there is no primary key. We never use primary keys in our databases...I only have a vague idea of how they work. So I didn't even think of that - from what I have read on other help sites I got the impression it might be something with the AutoNumber field type. But who knows.. I'll try it and see what happens...

(I probably won't get to this until tomorrow or Friday, at the earliest, though.)

[BookstoreEscapee]

In case anyone's wondering, I made a copy of my database today and put a primary key on the field in question, and it put it in the right order. So it looks like that is the problem. I just have to make sure it doesn't affect anything else. It shouldn't though, since the only reporting that comes off that table is pretty simple, and it doesn't get linked to anything else, either.

Thanks for the suggestions