In a form, I am trying to generate an automatic serial number so when the user hits new record, they would not have to enter in a new serial number. The code I have so far:

If Me.NewRecord Then
Me.[SerialNumber] = Format(Now(), "yyyy") & "-" & Format((Nz(DMax("[LCellsID]", "tblLCells"), 0) + 1), "0000")
End If

I placed it in On Click for the serial number text box. Not what I prefer but it's the only way I could get it to work. Additionally, I have it based off of the primary key for tblLCells, which again is not what I want it to do. The numbers do not match for LCellsID and SerialNumber. SerialNumber's format is 2011-xxxx while LCellsID is simply an autonumber field. I need the numbers for SerialNumber to run sequentially, where as when it is running off of LCellsID it sometimes can skip a number. I hope that makes sense. BTW, I tried basing it off of SerialNumber but kept getting an error. Any ideas?

Hi

Have a look at Jack's code archive demo on generating sequential numbers.

Also, to get just the current year you don't need Format(Now(), "yyyy"), you can just use Year(Date())

In my database I have a singel record table called Parameters.
I put a 'next number' field in it, and have a function that

1) Gets the next number from the table
2) Increases the umber in the table by 1
3) Applies any other logic, such as leading zeroes or prefixes
4) Returns the new number.


It is called with something like:
me.serialnumber = nextnumber.

And has the following advantages:

1) it works very nicely in multi user application because getting the number and increasing ready for the next user is very fast.
2) You can if you want, skip numbers just by updating the parameter table. - Som people want to start at 1 again for a new year.
3) I think that you may get the saem number twice. Access doesn't save a record till you move on ot another. So all the time a new recrod is being added, , different users could get the same serial number.



Any use??

1. Does the Sequential component of the SerialNumber reset back to 1 when a new year starts?

2. Do you actually have "YYYY-Sequential component" stored as a single Field value?

Alan: Thanks for providing that resource. It's going to take me some time to analyze it, though it may be a bit beyond the scope of my project.

dmh: I will give it a shot and let you nkow.

vtd: 1. I did not think about that, I guess that is a possibility. Would it complicate things much more if I wanted it do that?
2. I do currently have it stored in a single field, is that part of the problem?

>>2. I do currently have it stored in a single field, is that part of the problem?<<
You Field stores 2 data items in a single Field value which violates the First Normal Form (Field Value must be atomic, i.e. each Field value stores a single item of data). This makes it harder to process data, especially if you want to select by year. You can simply store your SerialNumber as 2 separate Field values, 1 for the Sequential number and 1 for the year (unless you can derive the year component of the SerialNumber from other data items already stored).

>>1. I did not think about that, I guess that is a possibility. Would it complicate things much more if I wanted it do that?<<
If you have 2 Fields for the SerialNumber as per answer to your Q2 above, it is not much harder. If you store the SerialNumber as 1 Field value as posted, it is certainly harder since you have to seperate out the 2 components. The First Normal Form actually tries to prevent us from falling into this trap which reduces the data processing efficiency.

What you say definitely makes sense. I am however having trouble as to how I would have separate fields for the serial number show up in one text box on the form. Not only that, but to have whatever is written in the text box be stored in two separate fields. Seems complicated...

Since you want both components to be automatically calculated, all you need is 3 TextBoxes

* 2 invisible TextBoxes bound to the 2 component Fields and use code to assign the values for these 2 TextBoxes on NewRecord addition.

* 1 visible Calculated TextBox that concatenates the 2 component values and displays the concatenated String (a calculated value, not stored in the Table) for the human reader.

What do you guys think of this code?



tblLCells is my main table, Serial2 is a field in that table where I am capturing the 0001, 0002, etc. I have this in a command button for adding a new record. It seems to be working so far, however I am noticing it is increasing the autonumber primary key field on tblLCells by 4. So it's going from 6 to 10, 10 to 14, and so on. Should I be worried about this?

???

I thought you are doing this as an "on-Form" process???

If so, why do you use VBA code to open an independent Recordset to add the new Record?

This is probably the cause of the jump in AutoNumber value. Have you checked in the DatasheetView of the Table and see if there are more Records than expected?

What do you mean on-Form process? I am using VBA code because I want to give the user a button instead of the navigation arrows at the bottom of the form, which I will eventually hide. And the table is not independent, it is the record source for the form.

I just checked the table in Datasheetview and there are no more records than there should be.

In the first post you wrote:

What did you mean with the above quoted description/code if you are not adding a new record by using an "on-Form" process, especially the If Me.NewRecord???

Remember that Access handles the record addition via the Form completely independent of any other record addition via Recordset in your VBA code.

I use the following code for my Sequential "mock" Auto-numbering for my reports. It will change year on year change, and I can reset the number at anytime by changing the number in the table.


FILENUMBER - is the field in the table tblSEQ
tblSEQ - is a table that has 1 PK named FILENUMBER



The event code is placed in the AfterInsert portion of the main form.

Hope this helps!