how to custom autonumber

  • Thread starter Thread starter Jon
  • Start date Start date
hi John,

this is my code as it is in my form:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strID As String
Dim iNext As Integer

strID = Nz(DMax("[ID_Number]", "[Manpower_Request]", "[ID_Number] LIKE '*/"
& _
Year(Date) & "')"), "00/")


iNext = CInt(Left(strID, 2))
If iNext >= 99 Then
Cancel = True
MsgBox "Shut up shop for the year, out of ID values", vbOKOnly
Else
Me!txtID = Format(iNext + 1, "00") & "/" & Year(Date)
End If


End Sub

My Table "Manpower Request" has the following fields:
ID_Number>>>>Text and not Primary Key
RequestNumber>>>Text and it is Primary Key
AssignedTo>>>>Text
Justification>>>>>Memo
Click to expand...

Ok, so I'm obviously very confused. Your original post said:

<quote>
I have text box "ID" and it is text and I would like to custom it to be auto
number as the following format:
01/2009
02/2009
<end quote>

You did say ID, not ID_Number; ID_Number isn't a number; ID_Number isn't an
autonumber; ID_Number isn't even the primary key of this table, therefore it
is not (needed to be) unique. So I don't understand a) why you're assigning it
in this form at all and b) why you're trying to increment it.

I really think you're either on the wrong track, or that I'm totally
misunderstanding the structure of the database and what you're attempting to
accomplish.
 
Back
Top