# Auto Number

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.

###### Who is Participating?

x
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.

Commented:
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.
Commented:
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

Experts Exchange Solution brought to you by

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

Commented:
p.s: Make sure that the MyKeyBox is bound to the Key field!
Author Commented:
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.
Commented:

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
Author Commented:
Hi thorkyl,

Thanks a lot.

Commented:
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.
Author Commented:
I got your sample already and look at it right now. I will get back to you once I finish it.
Thanks:)
Commented:
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.
Author Commented:
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.

Commented:
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.
Commented:
"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.
Author Commented:
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.

Commented:
Thanks HaydnH

You explained it better than I could.
Commented:
np =)
Commented:
give me a few and I will recreate the it for you

Author Commented:
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.

Thanks a lot.

Commented:
Author Commented:
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.

Commented:
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?
Author Commented:
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.
Commented:
"
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

Author Commented:
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.
Author Commented:
More points has been added to this question.
Commented:
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
Commented:
Mrong,

"More points has been added to this question."

What was the problem with my solution?

Haydn.
Commented:
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.
Author Commented:
Hi Haydn,

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

Thanks.
Author Commented:
Hi thorkyl,

Thanks.
Author Commented:
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.
Commented:
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.

Commented:
oops
missed a )

x = Nz(CLng(DMax("PDR", sTABLE, "YR=" & Right(DatePart("YY", Date), 2))), 0)
Author Commented:
Hi HaydnH,

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

Thanks.

Author Commented:
Hi thorkyl,

What does your tkb_GET_NEXT_NPDR( ) function do?
Thanks.

Commented:
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
Author Commented:
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.
Commented:
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.
Author Commented:
Yes, You got it 100% correct this time:)

Thanks.
Commented:
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.
Author Commented:
Thanks !
Commented:
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
Author Commented:
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.
Commented:
mmm
Will look at it and post fix in the morning.
Commented:
"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.
Author Commented:
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.
Commented:
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

Author Commented:
So the 'NN' will start from '2' initially?

Thanks.
Commented:
"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.
Author Commented:
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.
Commented:
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

Commented:

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.
Author Commented:
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.
Commented:
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
Author Commented:
thorkyl & HaydnH,

Thanks a lot for your help.

mrong
Author Commented:
I used thorkyl's code and it works perfect.
###### 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.