Auto Increment Value

[padhuka]

Hi All

How to assign value to a field based on the value of the same field in the previous record?
Autonumber has its limitations for my purpose. Because, after I start filling up a form, I some times cancel the form filling process, and it affects the continuous numbering.

I guess the issue can be resolved by using some thing like record count facility. i.e. current' record's field value = number of records till previous record + 1.

How to exactly codify or execute the above by vba coding.

The Field Name is "SerialNo.", and Table Name is "StudentData"


Thanks

Padhuka

 

[statsman]

First of all its not a good idea to put your Email in a message on this forum. There are people (non members) who troll these sites looking for addresses to spam. This why the PM or personal message option is available.

As to your problem.

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

 

[padhuka]

Further Support Required

Thanks Statsman for that quick reply

My understanding of access is pretty less. I thought I would better load the access file itself for better clarity.

There are 6 Columns (Fields) (apart from the autonumber field).

Date Column is a auto updating field
"Student Name" to be keyed in separately by me
"Registration No" is a combination of Date and Serial Number which runs as follows YYYYMMDD-001, YYYYMMDD-002.

Here, the last three characters will keep on increasing by one unit for every new record. YYYYMMDD will reflect the date on which the record was created.

I was using the concatenate (&) option for my purposes till now with Autonumber as the serial number facility. HOwever, there are times after updating just one field, I am required to cancel the updation, whereupon the Autonumber skips one number while creating a new record.

I would like support for the automatic updation of the Registration No facility.

Cheers

Padhuka

 

[joepele]

First of all its not a good idea to put your Email in a message on this forum. There are people (non members) who troll these sites looking for addresses to spam. This why the PM or personal message option is available.

As to your problem.

Create two fields in your table:
TheYear - long
TheSequence – long

Then enter this code in your form's BeforeInsert
Me.TheYear = CLng(DatePart("YYYY", Date()))
Me.TheSequence = Nz(DMax("[TheSequence]", "YourTableName", "[TheYear] = "&Me.TheYear),0) +1

That’s all the code you need. When a new year starts the DMax function returns a Null, which the Nz function changes to a 0. Then it just adds 1 to get the next value.

Why two fields? You will spend a lot of time writing code to continuously tear them apart. It’s much easier to store and group them when they're stored separately.

It’s also easier to format them in a query.

I am not sure what I am doing wrong but there was an error that popped up with this.

Hope you can help.

Thanx.

 

[Rabbie]

I am not sure what I am doing wrong but there was an error that popped up with this.

Hope you can help.

Thanx.

Firstly you should set the form BeforeInsert Event to [Event procedure] then put the code from Statsman into the procedure.

This link from Bob Larson explains how to enter the code

The code still doesnt seem to work 100% but if you input 2008 in the year field then the sequence number is calculated correctly.

 

[joepele]

Firstly you should set the form BeforeInsert Event to [Event procedure] then put the code from Statsman into the procedure.

This link from Bob Larson explains how to enter the code

The code still doesnt seem to work 100% but if you input 2008 in the year field then the sequence number is calculated correctly.

Thanx for the code.

But I thought that was what I did? But I still got an error.


Anyway,
I think I need to stop hi-jacking this thread...
I have posted more details of the request here.
http://www.access-programmers.co.uk/forums/showthread.php?t=146404

Hope to hear from you gurus.

Anyway, thanx for the link. *Great stuff for newbies like me.

 

[Pat Hartman]

@padhuka
Autonumbers have no meaning and should not be construed to be anything but a unique identifier. It makes no difference in most cases whether there are gaps in the numbers. The only application where gaps are not acceptable are those that print checks.

The solution provided by statsman resets the sequence number every year. You did not ask for that but it is just as good as anything.

I would still use an autonumber primary key and use the autonumber to manage all relationships. The generated number will be just a piece of data that you can print or search on.