code to manually increment a number in a field

[wellssh]

greetings all,

I have a need for access to assign and manually increment a number in a field
each time a new record is created. My table structure is as follows,

Table1
EstimatelogID (autonumber) PK
Estimate number (text field, needs to remain a text field, and increment by
one upon each entry)
Estimate Type
Date Created

As stated above the Estimate number field is a text field, and needs to
remain a text field, the reason is, this field is appended into from another
table, which is a text field, I am not allowed to modify this other table.

I need one of 2 things, whichever one is easiest, they have asked for the
Estimate number to be created upon data entry, in one of the 2 following
formats

05-00001
05-00002 etc etc

OR

ES-00001
ES-00002 etc etc

In the first possibility (05-00001) 05 represents the year, I have attempted
it with the following code I found, but get a series of syntax errors upon
entering the code, hopefully someone can see the error in code and point me
in the right direction.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim DefaultNum As String
Dim intIncrement As Integer

DefaultNum = Right(Format(Me.txtCreateDate, "yy") & "00000"),2)
intIncrement = Nz(DMax("[Estimate_number]","Table1","Year([CreateDate]) =
Year(Date())),0) + 1
Me.txtestimate_number = intIncrement
Me.txtestimate_numberDisp = DefaultNum & Format(intIncrement, "0000000")
End Sub

I am a novice at code, the first question I have, in the code above, where
it says "CreateDate" - is this part of the code or supposed to reflect the
name of my date created field?

Would it be easier to use the 2nd option?
ES-00001
ES-00002
ES-00003

If yes, I would need some assistance to develope the code

I am nearing the deadline on this project, and cant get past this hurdle,

Any and all help is greatly appreciated

Thank You!!!

 

[Marshall Barton]

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim DefaultNum As String
Dim intIncrement As Integer

DefaultNum = Right(Format(Me.txtCreateDate, "yy") & "00000"),2)
intIncrement = Nz(DMax("[Estimate_number]","Table1","Year([CreateDate]) =
Year(Date())),0) + 1
Me.txtestimate_number = intIncrement
Me.txtestimate_numberDisp = DefaultNum & Format(intIncrement, "0000000")
End Sub

I'm not sure I've got the names straight or what all of your
code was trying to do, but here's my guess at what you want:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intNext As Integer
If Me.NewRecord Then
intNext = Nz(DMax("Right(Estimate_number,5)", _
"zzzztest", "Left(Estimate_number,2) = " _
& Format(Date, "yy")), 0) + 1
Me.txtestimate_number = Format(Date, "yy") & "-" & _
Format(intNext, "00000")
End If
End Sub

 

[Van T. Dinh]

Since you already got the "05" from the [Date Created], why would you want
to store "05" again in the [Estimate Number]?

It is much better to simply store the sequential number for the year, i.e.
starting the SeqNo every year.

If the users want to see the "intelligent key" such as "05-0001" then simply
concatenate the year value (from [Date Created]) with the SeqNo.

 

[wellssh via AccessMonster.com]

Gentlemen, Thank you very much for the replies, but alas, I could not get
it to work. BUT no fear, I just happened to call the owner of the project,
and convinced him to take a different route on this field. I will post my
question in a new post so we dont get everything all mixed up.

Again, thank both of you very very much!!