Automatically create a unique ID

[Swins]

We are currently moving a paper based work system to access database, I
have created the tables and linked them to forms, but I am struggling
to make a field that will continue our numbering system automatically.

We currently use a system of month/year/unique number from 001
increasing by one for each job.
i.e. 08/06/123, the month and year are based on the time the record is
created.

I have tried several different ways, but not had complete success. I
can't change the numbering system so don't suggest this.

Any other ways of doing this?

Thanks.

 

[BruceM]

Does the numbering revert to zero monthly? Annually? Ever?

 

[Guest]

Idealy, you should carry these as two separate fields. One for the
month/year and one for the job. The month/year text should be string and
carried as MMYY, without the "/". That can be inserted using formatting when
you want to display it. The job number should be carried as numeric, long
integer. Here is a function that will return the next job number for your:

Me.txtJobNo = Nz(DMax("[JobNumber]", "MyTableName", "[JobDate] = '" & _
Me.txtJobDate & "'"), 0) + 1

To format it like you show in your post:

=Format(strJobDate, "00/00") & "/" & Format(lngJobNumber, "000")

 

[Swins]

Does the numbering revert to zero monthly? Annually? Ever?

Sorry, Annually.

 

[Swins]

Sorry, Annually.

Thanks

Does the numbering revert to zero monthly? Annually? Ever?

 

[BruceM]

Does Klatuu's posting help?

Sorry, Annually.

Thanks

 

[Swins]

OK, Sorry for the delay, major HW failure, but new laptop is on the
way!

It helps, but I'm not a regular Access user, Whilst I understand the
code, how and where should I enter it? Most of the design has been done
in form or table views, sorry to seem so stupid, but having never used
Access I'm on a step learning curve. The rest of the database work
fine, just this little issue. If this is way to hard to explain, don't
worry, I'm still learning, so might work it out very soon.

Thanks.

Idealy, you should carry these as two separate fields. One for the
month/year and one for the job. The month/year text should be string and
carried as MMYY, without the "/". That can be inserted using formatting when
you want to display it. The job number should be carried as numeric, long
integer. Here is a function that will return the next job number for your:

Me.txtJobNo = Nz(DMax("[JobNumber]", "MyTableName", "[JobDate] = '" & _
Me.txtJobDate & "'"), 0) + 1

To format it like you show in your post:

=Format(strJobDate, "00/00") & "/" & Format(lngJobNumber, "000")

We are currently moving a paper based work system to access database, I
have created the tables and linked them to forms, but I am struggling
to make a field that will continue our numbering system automatically.

We currently use a system of month/year/unique number from 001
increasing by one for each job.
i.e. 08/06/123, the month and year are based on the time the record is
created.

I have tried several different ways, but not had complete success. I
can't change the numbering system so don't suggest this.

Any other ways of doing this?

Thanks.