W
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!!!
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!!!