Create an autonumber field

[Lynn_AccessUser]

I have searched the forum and haven't been able to find the exact solution to my problem.

I need to create a primary key field that is an autonumber field; however, I need to generate the field via a different means that the datatype = autonumber.

The reason I need to do this is as follows.

The database has a field in it called ProjectNumber. Every year we upload some of the projects from the prior year for the new year. Some of the projects numbers are as follows.

2004001
2004002
2004998
2004999

Right now I have a primary key field called ProjectID. So for 2004 the data is as follows:

ProjectID ProjectNumber
2004001 2004001
2004002 2004002
2004003 2004998
2004004 2004999

The ProjectNumbers for the 2004998 and 2004999 have to be those exact numbers.

But I am having issues with combo boxes that filter other combo boxes and the copy function.

For example, the value bound to a combo box is the primary key but the value shown is the ProjectNumber. I am having issues where the ID is being saved in the table as the foreign key but the ProjectNumber won't show in the combo box record the next time the record is opened. Also, when a user copies the record it copies the wrong value if the ID and ProjectNumber fields are different.

The issues all disappear when I include the ID field in the list for the combo box and do not make it invisible by setting the column size = 0.

However, the users do not go by the ID but rather the ProjectNumber.

As a result, I would like to get rid of the ID field all together and make the ProjectNumber field the Primary Key.

If a new record is added I want the next ProjectNumber to be 2004003. The examples I have found looks for the max number and adds 1. This doesn't work because it would set the ProjectNumber = 2005000 since the last ProjectNumber is 2004999.

Hope didn't confuse everyone.

Thanks!!

 

[Lynn_AccessUser]

Basically 2004002 is the last record prior to 20049?? so I want the new record to be 2004003.

I am thinking that I might be able to something like:

New ProjectNumber will be max ProjectNumber less than 2004900 plus 1.

In any given year the ProjectNumbers should never reach 2004900 so I can hard code that number.

Do you think this will work?

 

[Lynn_AccessUser]

Pat,

I understand what you are saying but unfortunately that doesn't work with the way the users want the data stored. The reason the ID field is a combination of the year and an ID is because they use the same ID over year after year. For example, 2003001 and 2004001 refer to the same project but a different year.

Thanks for your suggestion though.