Solved

Auto Number

Posted on 2003-11-11
55
2,919 Views
Last Modified: 2012-06-27
I have two tables and both of them have a field called PDR. The PDR is in the following format (nnnnyy),like this '000103'. '0001' can be the auto number or add up by 1 for every new record. '03' is the current year. I can seperate PDR. That is, 'ID' field and 'year' field. Then combine them later.

The PDR field can be auto-number or something else. But I want the PDR number be radomly picked up for these 2 tables. That is if the new record in Table One gets '000103', then the next new record in Table Two gets '000203'.

The hard part is once we move to the next year,say '04', I want to reset the 'nnnn' to zero and start over again.

Thanks in advance.
0
Comment
Question by:mrong
  • 25
  • 20
  • 10
55 Comments
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
First create a field in both your tables called "Key", then create a text box on your form called "MyKeyBox", and then create a BeforeInsert event for the form as follows:

Private Sub Form_BeforeInsert(Cancel As Integer)
  T1Max = DMax(left("Key", 4), "Table1", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
  T2Max = DMax(left("Key", 4), "Table2", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
 
  If (IsNull(T1Max) And IsNull(T2Max)) Then
    Me.MyKeyBox = "0001" & right(Year(Now), 2)
 
  ElseIf (T1Max > T2Max) Then
    Me.MyKeyBox = T1Max + 100
   
  Else
    Me.MyKeyBox = T2Max + 100
   
  End If
End Sub


Remember to change the name of the tables to match.

Regards,

Haydn.
0
 
LVL 8

Accepted Solution

by:
HaydnH earned 40 total points
Comment Utility
Sorry - a few changes:


Private Sub Form_BeforeInsert(Cancel As Integer)
  T1Max = DMax(left("Key", 4), "Table4", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
  T2Max = DMax(left("Key", 4), "Table5", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
 
  If (IsNull(T1Max) And IsNull(T2Max)) Then
    Me.MyKeyBox = "0001" & right(Year(Now), 2)
 
  ElseIf (Nz(T1Max, 0) > Nz(T2Max, 0)) Then
    Me.MyKeyBox = right(CStr(1000000 + T1Max + 100), 6)
   
  Else
    Me.MyKeyBox = right(CStr(1000000 + T2Max + 100), 6)
   
  End If
End Sub
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
p.s: Make sure that the MyKeyBox is bound to the Key field!
0
 

Author Comment

by:mrong
Comment Utility
Hi HaydnH,

1)
Could you please explain line->  T1Max = DMax(left("Key", 4), "Table4", "right(Key, 2) = '" & right(Year(Now), 2) & "'")

What is T1Max & DMax?

2)
What is Nz(T1Max, 0)?

3)
How does the number get reset once move to the next year.

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
See thread:

http://www.experts-exchange.com/Databases/MS_Access/Q_20781437.html

Sample File:

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

With a little change it will do exactly what you want

Thorkyl
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

I can't download your sample code. Is there anything wrong?

Thanks a lot.

0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
mmm
odd, not that I know of.
was there an error message?

I was able to dload it and its on a public ftp on my server.

If your firewall / security is doing it let me know and I will get it to you.
0
 

Author Comment

by:mrong
Comment Utility
I got your sample already and look at it right now. I will get back to you once I finish it.
Thanks:)
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
1)
Could you please explain line->  T1Max = DMax(left("Key", 4), "Table4", "right(Key, 2) = '" & right(Year(Now), 2) & "'")

What is T1Max & DMax?


This checks Table4 to find the highest value of the 4 left most digits of the Key field where the right 2 digits of the key field match the current year. T1Max is a variable that will store the highest value returned.

So lets say we have table 4 with the key field values:

000102
000202
000302
000103
000203

As the current year is '03' the T1Max variable will be set to 000203



2)
What is Nz(T1Max, 0)?


The NZ function converts null strings in to the character you specify. So if we are saying:

Nz(T1Max, 0) > Nz(T2Max, 0)

...and either T1Max or T2Max is null (i.e: the DMax function didn't find a value for the field key in Table4 or Table5) then we substitute a 0 for the null string. This is because we can't compare a greater than with a null string.

Haydn.
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

You had 3 primary keys-> YR,SCD,CCD How did you make them? I thought I can only have one primary key.

You increment ODRNBR by 1 once gets more orders. I guess I can use the same strategy for my 'nnnn' field,right? But I need to assign 'nnnn' field for two tables. How should I keep track of it? Or just keeping adding? How can I reset to '0' once move to a NEW year?

For your YR field, it is the last 2 digits of the current year when the record gets inserted, right? Why you didn't just set a default value for YR on the table level?

Thanks a lot.


0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
right
The table was set up for a different user in the above thread I mentioned

As for the Key its a three part key (Select all three fields and make them the key)

You will only need two.

YR - Text 2
NN - Number - Long

e.g.
YR     NN
03       1

Then Next year you have in the table
YR     NN
03       1
04       1

And the year after that and so on

YR     NN
03       1
04       1
05       1

The code looks at the current year (right(datepart("yy",date()),2)

If the YR column does not have that entry then it adds it and starts the numbering

Now you can also add a table column to it so you can have somthing like this.


YR     NN           TBL
03       7         TABLE1
03       11        TABLE2
03       100      TABLE3

Now each auto next number is table specific

As for the YR and default?
I dunno know - I try to never use default values at the table level.
I like to control the data with code rather than counting on the DB to do it.
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
"You had 3 primary keys-> YR,SCD,CCD How did you make them? I thought I can only have one primary key."

You can only have 1 "primary key" per table, but a primary key can be made up of more than 1 field.

So lets say you have 3 fields x, y & z. In each field you can have duplicates but you can't have a record with the same x, y & z as any other record. Therefore a primary key consisting of these 3 fields would be acceptable.

To create a multiple field primary key, in the table design view:

   - select 1 of the fields you wish as part of you PKey
   - then while holding the ctrl key click the other fields
   - click the Primary key button on the tool bar

Haydn.
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl & HaydnH,

Both of your explainations help me a lot to solve our problem.

The only question left for me is how to distribute the 'nnnn' to 2 tables. I want to keep it as 4 digits number only. If I set is as auto-number field, it will reach '10000' after 9999. Same thing will happen if I use customized number(manually add by 1). So I need to reset it when it reach '9999' or reset it once turn into a New year. The primary key in my table will be 'nnnn' & 'YY' combination. It can be guaranteed this combination will always be unique since the number will never reach '9999' in a single year.

Please let me know your suggestions. I am getting no idea now:(

Thanks.


0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
Thanks HaydnH

You explained it better than I could.
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
np =)
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
give me a few and I will recreate the it for you

0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

I have two tables T1 and T2.

T1 & T2 has the following field:
--------------------------------------
PDR                 AutoNumber/Number (4 digits of number)
YR                   Number (last 2 digits of year)
Details             Text  (Deatail Info)

The PDR need to be reset to '0' once new year arrives.

Please let me know if you need more info.
Thanks a lot.





0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

It seems there is misunderstanding here. Might coz I didn't explain clearly.

I need to PDR number being picked up one by one for both tables.

That is, if the last record in T1 is '000503', then the new record in T2 will get '000603'. If the new record goes to T1 again, then the new record in T1 will be '000603'.

Thanks.




0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
ok just to make sure I have this right.

I create a new record in T1
    Generate 000103
I create a new record in T1
    Generate 000203
I create a new record in T1
    Generate 000303
I create a new record in T1
    Generate 000403

I create a new record in T2
    Generate 000503
I create a new record in T1
    Generate 000503
I create a new record in T1
    Generate 000603
I create a new record in T1
    Generate 000703
I create a new record in T2
    Generate 000803
I create a new record in T1
    Generate 000803


Basicly generate numbers all day long in T1
but if a record gets created in T2 use its number in T1 also?

Or did I just confuse myself?
0
 

Author Comment

by:mrong
Comment Utility
Like the following:

I create a new record in T1
    Generate 000103
I create a new record in T1
    Generate 000203
I create a new record in T1
    Generate 000303
I create a new record in T1
    Generate 000403

I create a new record in T2
    Generate 000503
I create a new record in T1
    Generate 000603
I create a new record in T1
    Generate 000703
I create a new record in T1
    Generate 000803
I create a new record in T2
    Generate 000903
I create a new record in T1
    Generate 001003

The number just keep adding and doesn't matter which table it goes.

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
"
I have two tables T1 and T2.

T1 & T2 has the following field:
--------------------------------------
PDR                 AutoNumber/Number (4 digits of number)
YR                   Number (last 2 digits of year)
Details             Text  (Deatail Info)

The PDR need to be reset to '0' once new year arrives.
"


ok
I think this helps
in the befor update of the form

  dim x as long
  'Get the last number used for PDR for this Year - returns 0 if no record
  x=nz(clng(dmax("PDR",T1","YR=" & right(datepart("YY",date()),2)),0)
  me.txtPDR = right(cstr("00000" & x+1),4) ' Generate PDR 000x +1 - Add 1 to the PDR
  me.YR = right(datepart("YY",date()),2) 'This year

0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

Could you please edit it in the previous function?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    'In this sample onbr would be your bound control for the
    'forms recordsource.
    If IsNull(Me.onbr) Or Me.onbr = "" Then
        Me.onbr = tkb_GET_NN(Me.cbo_TABLE_NM)
    End If
End Sub

Thanks.
0
 

Author Comment

by:mrong
Comment Utility
More points has been added to this question.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
Private Sub Form_BeforeUpdate(Cancel As Integer)
    'In this sample onbr would be your bound control for the
    'forms recordsource.
    If IsNull(Me.onbr) Or Me.onbr = "" Then
        Me.onbr = tkb_GET_NPDR(Me.cbo_TABLE_NM)
        if isnull(me.txtYR) or me.txtYR = "" then
            me.txtYR = right(datepart("YY",date()),2)
        end if
    End If
End Sub
Public Function tkb_GET_NEXT_NPDR(sTABLE as String) As String
    dim x as long
    x=nz(clng(dmax("PDR",sTABLE,"YR=" & right(datepart("YY",date()),2)),0)
    tkb_GET_NEXT_NPDR = right(cstr("00000" & x+1),4)
End Function
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
Mrong,

"More points has been added to this question."

What was the problem with my solution?

Haydn.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
LOL
Haydon,

If this is what he is looking for then I just missunderstood the question.
and all of my stuff was for not as you nailed it dang close right off the bat.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:mrong
Comment Utility
Hi Haydn,

It only insert the first 'nnnn' 'YR' into the table. I have to double check it to avoid any missing.

Thanks.
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

How to modified your code so it will add record into table instead of just adding numbers to 'NN' field.

Thanks.
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

The following line turns into red color in the VB window. I ma not sure if it gets execute.
x=nz(clng(dmax("PDR",sTABLE,"YR=" & right(datepart("YY",date()),2)),0)

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
ok, the NN field  in tbl_NEXT_NN is only a referance table and has nothing to do with your data.
It allows you lookup up the next number to use for a given year.
This works well with transactions where you are inserting large amounts of data and have rollbacks. Thus you dont loose the sequintial numbering.

Primarily I use it for generating Invoice and PO numbers.



0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
oops
missed a )

x = Nz(CLng(DMax("PDR", sTABLE, "YR=" & Right(DatePart("YY", Date), 2))), 0)
0
 

Author Comment

by:mrong
Comment Utility
Hi HaydnH,

I have looked throught it again. Still only insert the first one into key field. Any thoughts?

Thanks.

0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

What does your tkb_GET_NEXT_NPDR( ) function do?
Thanks.

0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
It looks in the table you pass it

Gets the last number generated ( 0006 ) for the year

You mentioned you had 2 columns YR and PDR

Adds one to the last PDR in the table for the current year if htere is a record
If there is not a record the it uses 0001 for the PDR and the right 2 of the year (2003)

e.g. Pass it T1 and you get this
--Assuming you have this data in the table and it is 2004
YR        PDR       Description
03        0001      Test 1
03        0002      Test 2
03        0003      Test 3
03        0004      Test 4
03        0005      Test 5
04        0001      Test 6
04        0002      Test 7

You will get:
04        0003      Test 22
0
 

Author Comment

by:mrong
Comment Utility
Your previous code did that already:) You don't need to add any function.
How about distribute PDR for two tables? I tried HaydnH's code but didn't work out.

I need to have PDR & YR to be distributed into 2 tables in the following order. That is, It will never have the same PDR&YR combination from 2 tables.

000103
000203
000303
000403
.
.
.

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
ok do I understand you correctly
"That is, It will never have the same PDR&YR combination from 2 tables"

000103 is in T1 and therefor can not be in T2
000203 is in T2 and therefor can not be in T1


If this is the case
It can be done but will require some modification of my original code.
0
 

Author Comment

by:mrong
Comment Utility
Yes, You got it 100% correct this time:)

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
now there are two way too do this

the first is to look in each table and see which has the highest number
(this is real slow)
the second is with the next number table
I will post both on the ftp here in a few for your review.
0
 

Author Comment

by:mrong
Comment Utility
Thanks !
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
Here you go.

ftp://ftp.inspectra.net/pub/outgoing/V1_V2_Next_Number_Table.zip

Version 1 uses the next number table:
I prefer to use it as it is:
    faster when you have large amounts of data
    easer to see what the next number to be used is
    easer to recover a number if you want to
    easer to set a starting number e.g. 01001
    easer to add the numbering scheme to other tables

Version 2 looks at each table then figures out what number to use
   It has less code
   code has to be changed if you want to add more tables


Thorkyl
0
 

Author Comment

by:mrong
Comment Utility
Hi thorkyl,

I tried your Version1 and It is works. Only one down side, I have to add the initial value in the tbl_NEXT_NBR table for each year. Otherwise it return me the "duplicated index" error msg when move to a new year. How to fix it?

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
mmm
Will look at it and post fix in the morning.
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
"I have looked throught it again. Still only insert the first one into key field. Any thoughts?"

What do you mean by this? the function I posted to start with had a few errors so I made changes as below:

Private Sub Form_BeforeInsert(Cancel As Integer)
  T1Max = DMax(left("Key", 4), "Table4", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
  T2Max = DMax(left("Key", 4), "Table5", "right(Key, 2) = '" & right(Year(Now), 2) & "'")
 
  If (IsNull(T1Max) And IsNull(T2Max)) Then
    Me.MyKeyBox = "0001" & right(Year(Now), 2)
 
  ElseIf (Nz(T1Max, 0) > Nz(T2Max, 0)) Then
    Me.MyKeyBox = right(CStr(1000000 + T1Max + 100), 6)
   
  Else
    Me.MyKeyBox = right(CStr(1000000 + T2Max + 100), 6)
   
  End If
End Sub


All you need to change is the Table4 & Table5's to the name of your table and the Key field to the name of your key. Also, make sure the MyKeyBox on your form has the control source "Key".

Haydn.
0
 

Author Comment

by:mrong
Comment Utility
Hi HaydnH,

I only got the first record working by using your code. After that, nothing was inserted into key field for both table.

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
Ok I found out where I fat fingered the code


Error is in tkb_GET_NN

Change:

    If DCount("YR", "tbl_NEXT_NBR", "YR='" & sYR & "'") = 0 Then
        'no record - lets create one for this year
        CurrentDb.Execute ("INSERT INTO tbl_NEXT_NBR (YR,NN) " & _
                                    "VALUES ('" & sYR & "'," & 1 & ")") '<====== the 1 should be a 2
        'first record is created now lets use it
        tkb_GET_NN = Right("00001", 4)
        Exit Function
    Else





TO




    If DCount("YR", "tbl_NEXT_NBR", "YR='" & sYR & "'") = 0 Then
        'no record - lets create one for this year
        CurrentDb.Execute ("INSERT INTO tbl_NEXT_NBR (YR,NN) " & _
                                    "VALUES ('" & sYR & "'," & 2 & ")")
        'first record is created now lets use it
        tkb_GET_NN = Right("00001", 4)
        Exit Function
    Else


And it will work correctlly

Sorry about the delay.
0
 

Author Comment

by:mrong
Comment Utility
So the 'NN' will start from '2' initially?

Thanks.
0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
"I only got the first record working by using your code. After that, nothing was inserted into key field for both table."

Are you running my code in the before insert event of a form?? Does the first record have the value 000103 in the table's Key field after you insert the record? what happens to the Key field on the form for the second record?

Haydn.
0
 

Author Comment

by:mrong
Comment Utility
Hi Haydn,

I was  running your code in the before insert event of a form. The first record had the value 000103 in the table's Key field after I inserted the record. The 2nd record didn't have the value 000203 in the table's Key field after I inserted the 2nd one.

Thanks.
0
 
LVL 2

Expert Comment

by:thorkyl
Comment Utility
no
The first record will be 1
the next record will be 2


The next number table stores the next record to be used
since there is no record we start with 1 then insert 2 since there is no need to insert 1 then look it up then update it to 2 only to use 1

so we use 1 and insert 2

0
 
LVL 8

Expert Comment

by:HaydnH
Comment Utility
Can you add the lines:

MsgBox "1: " & T1Max
MsgBox "2: " & T2Max

After the 2 DMax lines and tell me what they say for the second record - I reckon the DMax functions have been "translated" incorrectly for your table structure.

Haydn.
0
 

Author Comment

by:mrong
Comment Utility
Your following line insert '2',but you use '1' for the first record?

CurrentDb.Execute ("INSERT INTO tbl_NEXT_NBR (YR,NN) " & _
                                    "VALUES ('" & sYR & "'," & 2 & ")")

Thanks.
0
 
LVL 2

Assisted Solution

by:thorkyl
thorkyl earned 80 total points
Comment Utility
Yes

    If DCount("YR", "tbl_NEXT_NBR", "YR='" & sYR & "'") = 0 Then
        'no record - lets create one for this year
        CurrentDb.Execute ("INSERT INTO tbl_NEXT_NBR (YR,NN) " & _
                                    "VALUES ('" & sYR & "'," & 2 & ")")<=========INSERT
        'first record is created now lets use it
        tkb_GET_NN = Right("00001", 4)<================Number to use
0
 

Author Comment

by:mrong
Comment Utility
thorkyl & HaydnH,

Thanks a lot for your help.

mrong
0
 

Author Comment

by:mrong
Comment Utility
I used thorkyl's code and it works perfect.
0

Featured Post

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

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

772 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

8 Experts available now in Live!

Get 1:1 Help Now