Autonumber

[Guest]

Hello,

I want to make my auto-number in one of my tables to reference the year, and
have it be in this format 05-000.

I want the "05" part to be the year, and the "000" to be incremental.
I have a table with the current year on a field of course, then i have
another table with the actual incremental number 05-100 for example. But
when the year changes to 2006, i want it to change to 06-000 when the year
changes.

How can i do this?

Thanks much!

 

[BruceM]

You cannot manipulate an autonumber as you seek to do, but you can simulate
an autonumber. You could check
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
(watch out for line wrapping in your newsreader) for a look at some code
that addresses the problem.

Here is some code adapted from something I have used:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RecordID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RecordID", "tblMain", strWhere)

If IsNull(varResult) Then
Me.RecordID = Format(Date, "yy") & "-001"
Else
Me.RecordID = Left(varResult, 3) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

tblMain is the name of the table in which the text field RecordID is
located. Let me emphasize that word "Text". Record ID is defined in table
design view as a text field, and as a primary key. (It doesn't necessarily
need to be the primary key, but it does need to be indexed, no duplicates).
Build a form (I will call it frmMain) based on tblMain, and add the code to
the form's Current event. If it is to be a multi-user database, and if the
user does not need to see the number until after the record is complete, you
would do better to add the code to the form's Before Update event. See the
code in the link I provided for other options in a multi-user environment.

Use your actual table and field names, of course. Simple yet descriptive
names containing only alphanumeric characters and underscores are best,
IMHO.

 

[Guest]

I new that you couldn't really manipulate the auto-number, but since i am
able to change the format from blank(default) to 05-000 and it returns
05-0000 incrementally changing 0000 to 0001, 0002 etc. I was hoping i can
change the 05 reading from a field in a table referencing the current year in
05, 06 format.

I can manually change the format from 05-0000 to 06-0000, i just like things
automated. Plus, i love to see if i can make things happen when i have an
idea in my head...everything works perfectly in my head..haha.

Thanks very much for your quick response. I have always had excellent
success getting help here.

 

[BruceM]

You never tried the code, did you? Try the code I provided, then change
your computer clock to next year and create another record. The code I
provided is completely automated. You never need to change anything, as
long as you don't exceed 999 records in a year. If you need four digits,
just tweak the code:

If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "RecordID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("RecordID", "tblMain", strWhere)

If IsNull(varResult) Then
Me.RecordID = Format(Date, "yy") & "-0001"
Else
Me.RecordID = Left(varResult, 3) & _
Format(Val(Right(varResult, 4)) + 1, "0000")
End If
End If

You don't need a separate year field. The year is built into the code. If
there is no record for the current year, varResult will be Null, in which
case the RecordID is the last two digits of the year plus "-0001"; otherwise
the largest RecordID value is incremented by 1.

 

[Guest]

Actually, i did try it.

I have another question.

How do i extract 622 out of an address, up to the space after the 2? Example
622 Highway Road.

Thanks.

 

[Guest]

I tried to get it to work again, the field is blank.

Here is the code:

Private Sub SchedulingID_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Dim strWhere As String
Dim varResult As Variant

strWhere = "SchedulingID Like """ & Format(Date, "yy") & "*"""
varResult = DMax("SchedulingID", "tblScheduling", strWhere)

If IsNull(varResult) Then
Me.SchedulingID = Format(Date, "yy") & "-001"
Else
Me.SchedulingID = Left(varResult, 3) & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If
End Sub

tblScheduling is my table
SchedulingID is my column

 

[BruceM]

The code needs to be in the form's Current event. To get to the form's
properties, click the little square just off the top left corner of the form
(it is a sort of small square within a slightly larger square) and click
View > Properties. However, if this is to be a multi-user database then
then simplest was to avoid confusing error messages if two people are
creating records concurrently is to place the code in the form's Before
Update event. Not completely foolproof, but it should work in most
situations. Worst case is a confusing error message. Or see the code in
the link to Roger's Access Library.
To your other question about extracting part of a field, you could use the
Left function, or maybe the Mid function if the number you want to "extract"
is a variable number of digits. See Help on both of these. For anything
more specific you will need to explain just what you mean by "extract". Do
you just want to display the number? Do you want to create a new table
field? None of the above?

 

[Guest]

I figured out the numbers from a string....val.

 

[Guest]

Ahh yeah, that works beautifully! Perfect thank you.

About the address...i got it to work using var() function.

Left didnt work correctly. That only returns the amount of characters from
the left. I can't use specific spaces from the left. I just needed to pull
out the numbers, and val() worked perfectly.

Thanks so much for your help.

 

[John Vinson]

Left didnt work correctly. That only returns the amount of characters from
the left. I can't use specific spaces from the left. I just needed to pull
out the numbers, and val() worked perfectly.

Left([Address], InStr([Address], " ") - 1)

will return the substring (numeric or otherwise) up to the first
blank.

John W. Vinson[MVP]