custom autonumber

K

krishnanhemanth

Registered User.

Local time

Today, 08:06

Joined

Jun 12, 2009

Messages

115

• Sep 8, 2009

• #1

hi everybody

iam presently using the below code for my auto number

Private Sub Form_Current()
Me!NO.DefaultValue = Nz(DMax("[NO]", "TEST"), 0) + 1
End Sub

TEST is the TABLE
NO is the FIELD

it is working smoothly

In the format of NO field i am using ----- "DKVP-"&&&&&
it gives me DKVP-01

WHAT I WANT IS DKVP-01-0909 ( THE YEAR AND MONTH )

PLEASE HELP

 

G

Galaxiom

Super Moderator

Staff member

Local time

Today, 13:36

Joined

Jan 20, 2009

Messages

12,878

• Sep 8, 2009

• #2

It won't work smoothly for long. The DMax will include the DKVP- section. While it works on the first round it won't work again. The number cannot be added to the alphanumeric string so it will concatenate.

I would drop the DKVP and the date from the field just keeping the number formatted as integer. Store the YearMonth field separately.

Concatenate for display: "DKVP-" & test.no & "-" & test.YearMonth

Also note that using DMax to find the largest value will cause duplicates where two users enter new records at the same time.

 

A

Atomic Shrimp

Humanoid lifeform

Local time

Today, 02:36

Joined

Jun 16, 2000

Messages

1,954

• Sep 8, 2009

• #3

Galaxiom said:

...Store the YearMonth field separately...

Click to expand...

Or store a proper date and derive the year+month in an expression in a query. The stored date will probably be useful elsewhere anyway.

 

K

krishnanhemanth

Registered User.

Local time

Today, 08:06

Joined

Jun 12, 2009

Messages

115

• Sep 8, 2009

• #4

thanks galaxiom and atom for your valuable ides and time

i have a problem
i am not able to store the joined ( cocanated ) field in a table.
how do i do that

 

A

Atomic Shrimp

Humanoid lifeform

Local time

Today, 02:36

Joined

Jun 16, 2000

Messages

1,954

• Sep 8, 2009

• #5

I think the point of both of our posts was that you shouldn't store the concatenated value in a table, because it will cause all kinds of problems.

Just store the numeric part and calculate the whole concatenated value when you need to see it that way.

 

K

krishnanhemanth

Registered User.

Local time

Today, 08:06

Joined

Jun 12, 2009

Messages

115

• Sep 8, 2009

• #6

thanks

i got it

keep the serial number in the table

the rest can always be arrived at

 

missinglinq

AWF VIP

Local time

, 22:36

Joined

Jun 20, 2003

Messages

6,420

• Sep 8, 2009

• #7

An important point everyone has missed here is that, regardless of how this "number" is generated, using this

Code:

Private Sub Form_Current()
 ' Code to generate number goes here
End Sub

is going to change the serial number every time the record is accessed, not just when a new record is created! The code needs to be

Code:

Private Sub Form_Current()
 If Me.NewRecord Then 
' Code to generate number goes here
 End If
End Sub

As for Galaxiom's concern about multiple users simultaneously generating the same number, the odds of this happening will go dramatically down if the code is executed at the last possible second before the record is saved; I always place it at the end of any other code in the Form_BeforeUpdate event.