Changing code for custom autonumbering

Hi, how do I change the next code, so I get Custom autonumbering in the format mentioned under the code:

=====original code found at =================
Option Compare Database
Option Explicit

This form demonstrates a method of incrementing an index without using Autonumber.
Look at the code on the form's  "On Current" event.

Private Sub Form_Current()
  If Me.NewRecord Then
    On Error Resume Next 'It should never occur, just to be sure...
    Me!MailingListID.DefaultValue = Nz(DMax("[MailingListID]", "tblMailingList"), 0) + 1
  End If
End Sub

So each time there is a record made in the form, there a autonumber being inserted
in the primary-key form the main-table.
To be changed to the next new format:



for customer1
in 2003:

in 2004:

for customer2
in 2003:

in 2004:

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alan WarrenApplications DeveloperCommented:
Hi JvdWal,

If you have a field called suppliercode and a field called customercode in your forms recordset this should give you a string like your example.

Me!MailingListID.DefaultValue = Right(date(),1) & "-" & suppliercode  & "-" & customercode & "-" & Nz(DMax("[MailingListID]", "tblMailingList"), 0) + 1

Yup - Alan's code looks good - guess tht fishing did him good... or perhaps squid really is brain food ;P

Alan WarrenApplications DeveloperCommented:
Bait !!!

LOL :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

I use a "Next Number Table"
By doing it this way I can go back and resue a number or if I want
I can jump forward to a set of numbers.
I can also look back and see what I have used and what was skipped

YR - text - 2 - recomend using at least 2 for the year or your schema is only good for 9 years
SCD - text - 3
CCD - text - 3
CCDONBR - number - long

call tkb_GET_NN(sSCD,sCCD)

Public Function tkb_GET_NN(sSCD as String,sCCD as String) as String
    dim sYR as String
    dim lCCDONNBR as Long
    sYR = right(datepard("yyyy",now()),2) 'Get the current year
    If Dcount("YR","tbl_NEXT_NBR","SCD='" & sSCD & "',CCD='" & sCCD & "'")=0 then
        'no record - lets create the first one for the year
        currentdb.execute ("INSERT INTO tbl_NEXT_NBR (YR,SCD,CCD,CCDNONBR) " & _
                                    "VALUES ('" & sYR & "','" & sSCD & "','" & sCCD & "'," & 1)
        tkb_GET_NN = sYR & "-" & sSCD & "-" & sCCD & "-1"
        exit function
        'we have on for this year so lets grab it and use it
        lCCDONNBR = Dlookup("CCDNONBR","tbl_NEXT_NBR","SCD='" & _
                                          sSCD & "',CCD='" & sCCD & "'")
        'we now have it so lets set it up for the next user
        currentdb.execute("UPDATE tbl_NEXT_NBR SET CCDNONBR = " & lCCDONNBR  + 1 " & _
                                   "WHERE SCD='" & sSCD & "',CCD='" & sCCD & "'")
        tkb_GET_NN = sYR & "-" & sSCD & "-" & sCCD & "-" & lCCDONNBR
    end if
End Function


Whats the benefits of doing it that way? it seems a lot less efficient.

It is a little slower but some of the advantages are
1) I can look at the table and see where we stopped 2 years ago for a givin customer
2) I can set a customer to start at 100 or 10,000
3) I can at a glance see how many orders a given customer has made this year or four years
    ago and so on.
4) If I have a failure and I loose a number I can go back and use it. All I have to do is see
    if it has been used or not (by looking in the invoice table) and if has not been used and is less
    than the next number in the table then let the user use it. Thus I never have a skipped number.

Most of the time that I use this type of table I generate an invoice 03-10001 or YR-MM-#####

jvdwalAuthor Commented:
Hi alanwarren and thorkyl,

First of all thorkyl, It seems that your code is way above my MS Access skills... (Although, especially the advantages seem perfect, and in this the speed-performance is not really an issue..) Which other things should be changed or added compared with the "zipped" situation... new table, other changes...

Alanwarren: I even can't get your code to work, (although it "looks" pretty simple) when I straightforward replace the old code with yours (with the 2 new fields added in "tblMailingList" both "number - long") the result in the table is getting next:

MailingListID      suppliercode      customercode      Prefix
0                      23                      12      
2                      23                      13      
0 (#error, cant fill more, autoid is zero again)

(I'm btw using Access 2003 US)

Alan WarrenApplications DeveloperCommented:
Hi JvdWal,

Are you trying to populate an autonumber field with this concatenation?
I usually let Access manage it's own autonumber fields, and create my own key column for things like MemberNum, ContractNum, MailingListIDNum. I index them so there are no duplicates then apply my custom concatenation string.

This wont work for an autonumber field, but will work for an indexed/noDuplicates field of type text:

Me.MailingListIDNum = Right(date(),1) & "-" & me.suppliercode  & "-" & me.customercode & "-" & Nz(DMax("[MailingListID]", "tblMailingList"), 0) + 1

jvdwalAuthor Commented:

After trying lots of different situations, with diff sorts fieldtypes etc. etc. I cant get it to work..
A friend of mine sended me a page with the next sample strings:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!UniqueFieldName = Nz(DMax("[UniqueFieldName]", "[tblUnique]", "[DateField]=Date()"), 0) + 1
    Me!DateField = Date
End Sub

and an other dmax-sample string:


Since I can't get your code to work, what am I doing wrong..? is above code an idea to use (modified) as a "newbie"..??

I created a database with my code in it for you to play with

here it is
jvdwalAuthor Commented:
Hi Thorkyl

This works great! Actually it is 99% what I was searching for for..
But how I can use this code in my "Northwind-style" of db.

1. when I open the main-form (say main-record-input with "Orders.Order-id" in it,
   (this field is right now the standard autonumbering from MS Access, which is
   bothering a lot  ;-)
2. how can I put the code, with the fields like in your Form1, but without the
   button, in this main-form:


The custom autonumber should be generated, when the form opens and a new record
can be added, the autonumber should be then 03-000-000-1 (I use for the
"table-fields" Supplier & Customer-code both "000" as the default value, these
fields are as well in the Orders-table)

When adding a record in the main-form, it should be possible to change the custom
autonumber lateron or when the supplier and/or customer is known at that time.

I was thinking something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then
==Your code==          
End If
End Sub

3. and that the generated code will be put to the "Orders.Order-id"

This would be a superb solution if this is going to work out...!!!
Is this difficult (to do for me) ?
Hopefully this text makes a bit sence, took me a while typing it..
(has bin a long day ;-)


You can do it one of two ways.

First requires the button.
Place the button and the text box on your form and bind the text box
to you column in the forms record set.

let the user enter the data and then make them push the button or manually enter a number.
(set its "allow zero length string" to false in the table)

Second is to place the text box on the form and bind it then in the befor update of the form

if me.txt_Order_NBR ="" or isnull(me.txt_Order_NBR) then me.txt_Order_NBR=tkb_GET_NN("","")

The first way at the top forces the user to generate the number and allows them to make one up.

The second way does not force the use to do anything as the number is generated atomatically if it is not there.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jvdwalAuthor Commented:
Hello Thorkyl,

I'm getting quite far here!
But there is a little (I think) problem left somewhere..

First of all, I took your second solution:

The Order-ID field in the form has now the name "txt_Order_NBR", (this was "onbr" before)
and with data-source "Order-id" from the Orders table.
The data-source of the two fields Supplier- & Customer-code are the appropriate ones as
in the Orders-table. The names of these 2 fields are still CCD and SCD.

The underlaying code in the form is:

Option Compare Database
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        If Me.txt_Order_NBR = "" Or IsNull(Me.txt_Order_NBR) Then Me.txt_Order_NBR = tkb_GET_NN("", "")
  End If
End Sub

The problem now is that, the CCD and SCD fields are not putted in the order-id(txt_Order_NBR)field.

Order-id's which are generated now:

So everything is working great, except the Supplier- & Customer-code in the generated string..

What am I doing wrong here..?!?

jvdwalAuthor Commented:
Hello Thorkyl!

Hopefully you weren't looking already for the fault in the second solution.
After trying the first one as well, it turned out that the first one is preferable over no.2!!
Thanks for the help mate!! You deserve the points..

Best regards,
Sorry for the delay
I only turn on my machines during the week.

Hope all goes well with it.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.