auto increment if newdata ???

[luanhoxung]

hi all !
I have a text field with contruction: 0001-COM.
I need to it automatically increase 0002-COM when add new record and
.....
Any help with appreciated.
Luan

[Rick Brandt]

lu********@yahoo.com wrote:

hi all !
I have a text field with contruction: 0001-COM.
I need to it automatically increase 0002-COM when add new record and
....
Any help with appreciated.
Luan

If the text suffix is always going to be "-COM" then there is no reason to store
this and good reasons NOT to. If you just use formatting of a number field
(format = "0000-COM") then it is trivial to get the maximum numeric value
already in use and add 1 to it for new records. I would use the Form's
BeforeUpdate event...

If Me.NewRecord Then
Me!ID = Nz(DMax("ID", "TableName"), 0) + 1
End If

Assuming the ID field is indexed the above will be very efficient. To do the
same with a Text field containing the "-COM" on the end will be more complicated
and inefficient since an index cannot be used.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[luanhoxung]

thanks a lot, Rick.
It is great.
Luan
Rick Brandt wrote:

lu********@yahoo.com wrote:

hi all !
I have a text field with contruction: 0001-COM.
I need to it automatically increase 0002-COM when add new record and
....
Any help with appreciated.
Luan

If the text suffix is always going to be "-COM" then there is no reason to store
this and good reasons NOT to. If you just use formatting of a number field
(format = "0000-COM") then it is trivial to get the maximum numeric value
already in use and add 1 to it for new records. I would use the Form's
BeforeUpdate event...

If Me.NewRecord Then
Me!ID = Nz(DMax("ID", "TableName"), 0) + 1
End If

Assuming the ID field is indexed the above will be very efficient. To do the
same with a Text field containing the "-COM" on the end will be more complicated
and inefficient since an index cannot be used.
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[luanhoxung]

hi Rick & all !
Maybe i need your help revising my code.
i create my invoice number as code below.
It nearly work as my mine.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Dim Str As String
Dim i As Integer

Set DB = CurrentDb
Set RS = DB.OpenRecordset("TCHITIETHANGXUAT", dbOpenSnapshot)
Str = "-" & Format(Month(Date), "00") & Right(Year(Date), 2) & "/VF"
Me.INV.Format = Str
If RS.RecordCount 0 Then
i = Left(DMax("inv", "tchitiethangxuat"), 4) + 1
Me.INV = Format(i.Text, "0000") & Str ------i want cover i to text but not
succeed, i want to store i with 4 characters---
Else
Me.INV = "0001" & Str
End If
End Sub

how can i do this ??
Any ideas will be appreciated.
thansk in advance
Luan from VietNam

[luanhoxung]

sorry Rick & all !
I found my stupid mistake.:)
just release ---.text----
Thanks all.

[Larry Linson]

Start by revising your storage... store the sequential number, as a numeric
integer or long integer, separately from the text portion of your invoice
number... format and concatenate them when you need to display or use them.
That will simplify incrementing the sequential number...

In the future, it would be helpful if you would tell us _what_ you are
trying to do, instead of giving code that "almost works" for us to either
determine, or guess, what you are trying to do from the "how you thought it
would work and it almost did".

Trying to have "coded" identification, as your invoice number, was
convenient in manual systems, but rarely necessary in automated systems. A
number which uniquely identifies the entity works nicely, and information
such as date, category, etc., are easily retrievable. If it is necessary to
satisfy the managment, or users, with "coded" identification, it is much
easier to form the coded identification when needed to be displayed to the
humans to whome it may be meaningful, rather than storing it in humanly
convenient form in a medium (rotating memory on a computer) that a human
can't even see.

Larry Linson
Microsoft Access MVP

"luanhoxung" <lu********@yahoo.comwrote in message
news:4f******************************@localhost.ta lkaboutdatabases.com...

hi Rick & all !
Maybe i need your help revising my code.
i create my invoice number as code below.
It nearly work as my mine.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Dim Str As String
Dim i As Integer

Set DB = CurrentDb
Set RS = DB.OpenRecordset("TCHITIETHANGXUAT", dbOpenSnapshot)
Str = "-" & Format(Month(Date), "00") & Right(Year(Date), 2) & "/VF"
Me.INV.Format = Str
If RS.RecordCount 0 Then
i = Left(DMax("inv", "tchitiethangxuat"), 4) + 1
Me.INV = Format(i.Text, "0000") & Str ------i want cover i to text but not
succeed, i want to store i with 4 characters---
Else
Me.INV = "0001" & Str
End If
End Sub

how can i do this ??
Any ideas will be appreciated.
thansk in advance
Luan from VietNam

[luanhoxung]

Hi Larry !

You & others here are kind and knowledge.
I got a lot abt knowledge and behaviour from here.
I remember ur advise to post my question in later :)
Thanks.
Luan
Larry Linson wrote:

Start by revising your storage... store the sequential number, as a numeric
integer or long integer, separately from the text portion of your invoice
number... format and concatenate them when you need to display or use them.
That will simplify incrementing the sequential number...

In the future, it would be helpful if you would tell us _what_ you are
trying to do, instead of giving code that "almost works" for us to either
determine, or guess, what you are trying to do from the "how you thought it
would work and it almost did".

Trying to have "coded" identification, as your invoice number, was
convenient in manual systems, but rarely necessary in automated systems. A
number which uniquely identifies the entity works nicely, and information
such as date, category, etc., are easily retrievable. If it is necessary to
satisfy the managment, or users, with "coded" identification, it is much
easier to form the coded identification when needed to be displayed to the
humans to whome it may be meaningful, rather than storing it in humanly
convenient form in a medium (rotating memory on a computer) that a human
can't even see.

Larry Linson
Microsoft Access MVP

"luanhoxung" <lu********@yahoo.comwrote in message
news:4f******************************@localhost.ta lkaboutdatabases.com...

hi Rick & all !
Maybe i need your help revising my code.
i create my invoice number as code below.
It nearly work as my mine.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim DB As Database
Dim RS As Recordset
Dim Str As String
Dim i As Integer

Set DB = CurrentDb
Set RS = DB.OpenRecordset("TCHITIETHANGXUAT", dbOpenSnapshot)
Str = "-" & Format(Month(Date), "00") & Right(Year(Date), 2) & "/VF"
Me.INV.Format = Str
If RS.RecordCount 0 Then
i = Left(DMax("inv", "tchitiethangxuat"), 4) + 1
Me.INV = Format(i.Text, "0000") & Str ------i want cover i to text but not
succeed, i want to store i with 4 characters---
Else
Me.INV = "0001" & Str
End If
End Sub

how can i do this ??
Any ideas will be appreciated.
thansk in advance
Luan from VietNam