Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

MS Access

Apr 6, 2005 12:32AM PDT

I have a database for tracking new/old requirements for systems development. In the past these were tracked via notepad, excel, word by a number of people and our requirement numbers varied and was very confusing for our developers. What I did was create a access DB with one of the fields labeled requirement number and in this field I'm listing all of the old requirements using whatever number they originally had. Now going forward when adding a new requirement I want this number to auto-populate with the next higher number, last record + 1 kind of thing. I can't use auto-number with the existing field and I don?t want a new field with auto-number because it would start at #1 all over again.

The question is how can I make the default value of my field be last record number + 1?

Discussion is locked

- Collapse -
Potholes in the design.
Apr 6, 2005 12:50AM PDT

One of the interesting things you learn is never to use the record number for anything useful. It can change over time and upset the maintainer of the database.

Instead, make the field requirement be "unique" to disallow duplicates. You can prefill it with a command of recno()+1 with a command (your work) to get it started and add code to find the max number of the existing records and add 1 when you add a new record.

Bob

- Collapse -
Re: autonumber
Apr 6, 2005 6:00AM PDT

In this case (but it depends on the requirements indeed) you could consider to use the autonumber feature, because it's a really meaningless number.

I think you can start with defining it as long integer while you add the old records. Then use the SQL ALTER TABLE statement explained in http://support.microsoft.com/?scid=kb;en-us;884185&spid=2509&sid=global, either from the query definition screen or from VBA to change the field to autonumber with the correct start value (=seed) and increment (1).

It saves you some coding to put under the OK- (or Add) button in the screen you've made. But it's not very difficult either, of course, to fill a field with the value dmax(...)+1.

Hope this helps.


Kees