Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Changing code for custom autonumbering

Posted on 2003-10-29
16
Medium Priority
?
1,141 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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
 
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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

927 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