Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Changing code for custom autonumbering

Posted on 2003-10-29
16
Medium Priority
?
1,137 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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