Solved

Changing code for custom autonumbering

Posted on 2003-10-29
16
1,114 Views
Last Modified: 2012-06-27
Hi, how do I change the next code, so I get Custom autonumbering in the format mentioned under the code:

=====original code found at www.candace-tripp.com/download/autonum2k.zip =================
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:

(year-suppliercode-(customercode-incr.orderno))
(3-001-001-01)

examples:

for customer1
in 2003:
3-182-001-28
3-182-001-29

in 2004:
4-182-001-30
4-182-001-31

for customer2
in 2003:
3-182-002-12
3-182-002-13

in 2004:
4-182-002-14
4-182-002-15

regards,
JvdWal
0
Comment
Question by:jvdwal
  • 5
  • 5
  • 3
  • +1
16 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9640524
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

Alan
0
 
LVL 8

Expert Comment

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

Haydn.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9640901
Bait !!!

LOL :)
0
 
LVL 8

Expert Comment

by:HaydnH
ID: 9640958
=P
0
 
LVL 2

Expert Comment

by:thorkyl
ID: 9642860
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

tbl_NEXT_NBR
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
    else
        '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

Thorkyl
0
 
LVL 8

Expert Comment

by:HaydnH
ID: 9642917
Thorkyl,

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

Haydn.
0
 
LVL 2

Expert Comment

by:thorkyl
ID: 9643482
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-#####

0
 

Author Comment

by:jvdwal
ID: 9644742
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)

JvdWal
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 26

Expert Comment

by:Alan Warren
ID: 9646150
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


Alan
0
 

Author Comment

by:jvdwal
ID: 9658232
Hello,

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:

Nz(DMax("[ProjectNo]","YourTable","[YearCreated]=Year(Date())"),0)+1

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"..??

Regards,
Jvdwal
0
 
LVL 2

Expert Comment

by:thorkyl
ID: 9658678
I created a database with my code in it for you to play with

here it is

ftp://ftp.inspectra.net/pub/outgoing/Next_Number_Table.zip
0
 

Author Comment

by:jvdwal
ID: 9659372
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 ;-)

Regards

Jvdwal
0
 
LVL 2

Accepted Solution

by:
thorkyl earned 500 total points
ID: 9659760
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.

Thorkyl
0
 

Author Comment

by:jvdwal
ID: 9660347
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:
03---1
03---2
03---3
03---4
etc.

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

What am I doing wrong here..?!?

JvdWal
0
 

Author Comment

by:jvdwal
ID: 9661040
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,
JvdWal
0
 
LVL 2

Expert Comment

by:thorkyl
ID: 9671035
Sorry for the delay
I only turn on my machines during the week.

Hope all goes well with it.

Thorkyl
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now