how to custom autonumber

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

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
.....
and so on.

How to do that???
 
"Custom" and "Auto" are mutually exclusive. You can write code to assign a
default value to the textbox each time a new record is added to a form. You
might consider whether storing what appears to be a date as a text field is
a good idea. If you want the default value for this field to be the first
day of the current month, you could set a default value in the table design
mode: DateSerial(year(date()), month(date()), 1).
 
Assuming an ID field (Number) and a MyDate field (Date/Time), you could bind
a text box to the ID field and have something like this as it's Default
Value property:

=Nz(DMax("[ID]","[TableName]","Year([MyDate]) = " & Year(Date())),0) + 1

Hide the text box if you like. In an unbound text box, set the Control
Source to:

=Format([ID],"00") & "/" & Year([MyDate])

You could try something like this instead, which should result in the text
box being blank until ID has a value:

=Format([ID],"00") + "/" + Year([MyDate])

Or you may need to do:

=IIf([ID] Is Null,"",Format([ID],"00") & "/" & Year([MyDate]))

Note that you do not need to add the year to the field if there is a date
field in the record. The DMax statement looks for the largest value in ID
where the year in MyDate (the date field) equals the current year, and adds
one to that value. If it is the first record of the year, the DMax
statement returns Null, which the Nz function converts to 0. Adding one to
that makes ID = 1 for the year's first record. All of this assumes you mean
to start the numbering from 1 each year.
 
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
....
and so on.

How to do that???
Click to expand...

So you will have no more than 99 records in any year?

As noted by others, this is A VERY BAD IDEA. Storing data (a year) as part of
an ID is not helpful; data is data and should be stored in its own field.

But if you really want this you can do your data entry using a Form; in the
Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iNext As Integer
strID = NZ(DMax("[ID]", "[tablename]", "[ID] 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
 
Hi John and thanks for reply,

When I applied this code on my form the following error appears:
Run time error ‘94’:
Invalid use of Null

Please advice?




John W. Vinson said:
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
....
and so on.

How to do that???
Click to expand...

So you will have no more than 99 records in any year?

As noted by others, this is A VERY BAD IDEA. Storing data (a year) as part of
an ID is not helpful; data is data and should be stored in its own field.

But if you really want this you can do your data entry using a Form; in the
Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iNext As Integer
strID = NZ(DMax("[ID]", "[tablename]", "[ID] 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
Click to expand...
 
John's code below should work for assigning the sequence number in your
present scheme. A better design would be to split the sequence number and
the year into two fields. Data fields should be atomic, without any meaning
to sub-sections of the field. So a long integer year field, with a
meaningful name like orderYear or meetingYear or whatever it is (since
'year' is a reserved word and should not be used as a field name) and a
second long integer for annualSequenceNumber. That avoids the issue John
mentioned of being limited to 99 rows per year and programming against the
data is clearer because the data is simpler. You can use an expression (in a
query, form or report) to display your current ID:
OriginalID = Format([annualSequenceNumber], "00") & "/" & [orderYear]

To get the next data values, you could use code like this:
Dim thisYear as Long
Dim seqNumNext as Long
Dim strWhere as string

thisYear = Year(Date())
strWhere = "[orderYear]=" & thisYear
seqNumNext = 1 + nz(dMax("[annualSequenceNumber]", "[tablename]", strWhere),
0)

In the table design mode you could set the default value for orderYear to be
Year(Date()), so that would automatically be entered with the correct value.
I don't think you can use the expression for seqNumNext as a default value,
but you could try a variation like this and see if it works:
1 + nz(dMax("[annualSequenceNumber]", "[tablename]", "[orderYear]=" &
Year(Date())), 0)
 
Quite similar to what I suggested yesterday. I'm not sure of the exact
limitations of what functions can be used in the Default Value in a table,
but I don't think any domain aggregate functions can be used.

Paul Shapiro said:
John's code below should work for assigning the sequence number in your
present scheme. A better design would be to split the sequence number and
the year into two fields. Data fields should be atomic, without any
meaning to sub-sections of the field. So a long integer year field, with a
meaningful name like orderYear or meetingYear or whatever it is (since
'year' is a reserved word and should not be used as a field name) and a
second long integer for annualSequenceNumber. That avoids the issue John
mentioned of being limited to 99 rows per year and programming against the
data is clearer because the data is simpler. You can use an expression (in
a query, form or report) to display your current ID:
OriginalID = Format([annualSequenceNumber], "00") & "/" & [orderYear]

To get the next data values, you could use code like this:
Dim thisYear as Long
Dim seqNumNext as Long
Dim strWhere as string

thisYear = Year(Date())
strWhere = "[orderYear]=" & thisYear
seqNumNext = 1 + nz(dMax("[annualSequenceNumber]", "[tablename]",
strWhere), 0)

In the table design mode you could set the default value for orderYear to
be Year(Date()), so that would automatically be entered with the correct
value. I don't think you can use the expression for seqNumNext as a
default value, but you could try a variation like this and see if it
works:
1 + nz(dMax("[annualSequenceNumber]", "[tablename]", "[orderYear]=" &
Year(Date())), 0)


Jon said:
Hi,
01 & 02 are not a date, they are a sequence number? Please advice???
Click to expand...
Click to expand...
 
Quite similar to what I suggested yesterday. I'm not sure of the exact
limitations of what functions can be used in the Default Value in a table,
but I don't think any domain aggregate functions can be used.
Click to expand...

No domain functions, no user-supplied functions, and no reference to any other
field in the same or any other table. Pretty limiting!
 
Hi John and thanks for reply,

When I applied this code on my form the following error appears:
Run time error ‘94’:
Invalid use of Null

Please advice?
Click to expand...

Sorry, I had a typo (misplaced quote) in my code. Try

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim strID As String
Dim iNext As Integer
strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] 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
 
I think my main use of Default Value in table fields is to add a date/time
stamp. My experience has been that domain functions and custom functions do
not work, but I didn't know the extent of the limitation, so didn't want to
make a blanket statement. Thanks for the confirmation/clarification.
 
Hi John

And thank you for reply

Still I have 2 errors one of them is in this line:
strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date() & "')"),"00/")

The error is " Expected: list separator or )"

Then second on is in this line
(Format(iNext + 1, "00") & "/" & Year(Date

The error is " invalid use of null"

Please help?

Thanks!!
 
Hi John

And thank you for reply

Still I have 2 errors one of them is in this line:
strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date() & "')"),"00/")

The error is " Expected: list separator or )"

Then second on is in this line
(Format(iNext + 1, "00") & "/" & Year(Date

The error is " invalid use of null"

Please help?

Thanks!!
Click to expand...

Sorry! Should have been:

strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date()) & "')"),"00/")

with another close paren after Date(); and

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

with one fewer paren.
 
Hi John,
thank you, but I still have an error in this line:
strID = NZ(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/' &
Year(Date()) & "')"),"00/")

in this place,
"')

Expected: list separator or )





John W. Vinson said:
Hi John

And thank you for reply

Still I have 2 errors one of them is in this line:
strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date() & "')"),"00/")

The error is " Expected: list separator or )"

Then second on is in this line
(Format(iNext + 1, "00") & "/" & Year(Date

The error is " invalid use of null"

Please help?

Thanks!!
Click to expand...

Sorry! Should have been:

strID = NZ(DMax("[ID]", "[tablename]", _
"[ID] LIKE '*/' & Year(Date()) & "')"),"00/")

with another close paren after Date(); and

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

with one fewer paren.
Click to expand...
 
Hi John,
thank you, but I still have an error in this line:
strID = NZ(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/' &
Year(Date()) & "')"),"00/")

in this place,
"')

Expected: list separator or )
Click to expand...

Grump. I didn't really want to set up a table to test it, perhaps I should
have... ok let's break this down into pieces:

strID =
NZ(
DMax(
"[ID_Number]",
"[Manpower_Request]",
"[ID] LIKE '*/' & <<<<< Ah. There it is. A ' instead of a ".
Year(Date())
& "')")
,"00/")

So try

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

What I was trying to do was to string together pieces into a single criteria
string:

[ID] LIKE
'*/
2009 (or whatever year it is, I need a computer to keep track anymore)
'

The end result would be

[ID] LIKE '*/2009'

so it would find ID values ending in the string /2009.
 
Hi, john and sorry for lateness

Now, the problem is a message in this line:
strID = Nz(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/" & _
Year(Date) & "')"), "00/")
The message is “ Invalid use of nullâ€

Please advice??


John W. Vinson said:
Hi John,
thank you, but I still have an error in this line:
strID = NZ(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/' &
Year(Date()) & "')"),"00/")

in this place,
"')

Expected: list separator or )
Click to expand...

Grump. I didn't really want to set up a table to test it, perhaps I should
have... ok let's break this down into pieces:

strID =
NZ(
DMax(
"[ID_Number]",
"[Manpower_Request]",
"[ID] LIKE '*/' & <<<<< Ah. There it is. A ' instead of a ".
Year(Date())
& "')")
,"00/")

So try

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

What I was trying to do was to string together pieces into a single criteria
string:

[ID] LIKE
'*/
2009 (or whatever year it is, I need a computer to keep track anymore)
'

The end result would be

[ID] LIKE '*/2009'

so it would find ID values ending in the string /2009.
Click to expand...
 
Hi, john and sorry for lateness

Now, the problem is a message in this line:
strID = Nz(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/" & _
Year(Date) & "')"), "00/")
The message is “ Invalid use of null”

Please advice??
Click to expand...

Does your table have fields named ID? or ID_Number? Try stepping through the
code and using the Immidiate Window to check the values, e.g. by typing

? "[ID] LIKE '*/" & Year(Date) & "')"
 
Hi John,

It is miss ID_Number and i correct it. After that, the same message appears?
please Advice??

John W. Vinson said:
Hi, john and sorry for lateness

Now, the problem is a message in this line:
strID = Nz(DMax("[ID_Number]", "[Manpower_Request]", "[ID] LIKE '*/" & _
Year(Date) & "')"), "00/")
The message is “ Invalid use of nullâ€

Please advice??
Click to expand...

Does your table have fields named ID? or ID_Number? Try stepping through the
code and using the Immidiate Window to check the values, e.g. by typing

? "[ID] LIKE '*/" & Year(Date) & "')"
Click to expand...
 
Hi John,

It is miss ID_Number and i correct it. After that, the same message appears?
please Advice??
Click to expand...

Jon, I'm sorry... but I simply cannot see what you're doing wrong, and I
cannot see what would be right; because *I cannot see your database*. I don't
know your fieldnames. I don't know your variable names. I don't know your
code.

My intention here was not to give you THE ANSWER which you can simply copy
into your database like a black box. I'm trying - not very successfully - to
show you *a way of thinking* that you can study, and understand what the code
is doing, and alter it to fit your own situation.

Could you give me a hand here? Perhaps to start - post your code, as it is,
and *explain what it's doing* line by line. If there's code that you have no
idea what it's supposed to be doing, please say so - I'll try to explain my
reasoning.
 
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

Thanks!!
 
Back
Top