Changing next value of Autonumber using a module or macro

  • Thread starter Thread starter Robert Miller
  • Start date Start date
R

Robert Miller

I have been banging my head on this for several days now,
I'm really hoping someone can help me.
What I need to do is create a routine that will do the
steps necessary to change the next value of an Autonumber.
I can do it manually, but I'm trying to create a solution
for my co-workers who do not understand the procedure. So
far I can change the Required and Type fields, but I can
not change the other fields required to carry out the
steps. Here is a sample of the code I have written thus
far:

Sub NewYearUpdate()
Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb

Set tdf = db.TableDefs("tblVouchers")
tdf.Fields("VoucherNumber").Required = False
tdf.Fields("VoucherNumber").Type = dbLong

Set tdf = Nothing
End Sub

If someone has already done this, or it simply can not be
done please let me know. I need to finish this ASAP.
Thanks for any and all assistance!
 
To further clarify:
I need to change the autonumber field at the beginning of
each year to reflect the new year. Ie the field should
look like: 2003#### where the #### represents the auto-
incrementing number.
I also tried this solution of defining the autonumber
myself, but I can't get the math right.

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!VoucherNumber.DefaultValue = Year(Now()) * 10000 +
Nz(DMax("[VoucherNumber]", "tblVouchers"), 0) + 1
End If
End Sub

This adds the year portion each time.
 
I need to change the autonumber field at the beginning of
each year to reflect the new year. Ie the field should
look like: 2003#### where the #### represents the auto-
incrementing number.
Click to expand...

Bad Idea.

Two data items should be in two fields: you might try reading up on First
Normal Form.

One suggestion: have one integer field called YearCreated, with a
DefaultValue of

=Year(Date())

and another integer field called SerialNumber. If you really want this
reset to 1 every year, then you'll need to use a little VBA code; but
mostly you really don't need to if the business need is to (a) filter by
YearCreated and (b) to group/ count/ average by year.

HTH


Tim F
 
Well thanks for the reply eventhough it didn't really
answer my question. ;) I'll take what you have said into
consideration, but the business rules they have in place
is where my grief is coming from. I tried to go down the
road you suggested, but since they don't know Access they
don't understand my points (they're government
employees...).
Thanks again, and if anyone has code that can do this, or
is close that I can tweak don't hesitate to post it or
send it to me directly.
 
Hi Robert,

If you use an Append query and (callously) assign a value to the appended
record's Autonumber/Counter field, then subsequent appends will use that
value as the new base.

For example, create a new table with two fields: Id as Autonumber and
TextField as text.
Then run a test sequence (code follows) to Append with Addnew, Append query,
Append with Addnew.
The ids are allocated as 1, 4321, 4322

Which should solve your problem - although the method makes me feel a bit
nervous. Specifically, I am not sure how transportable it is.

C.D.

' ===========================
Sub TestAppends()

Dim tblName$, rs As DAO.Recordset, sql$

tblName$ = "Crunch Autonumber"
Set rs = CurrentDb.OpenRecordset(tblName$, dbOpenDynaset)
rs.AddNew
rs![TextField] = "Addnew 1st value"
rs.Update

sql = " INSERT INTO [" & tblName$ & "] (Id, TextField) SELECT 4321, 'Via
Append Query'"
CurrentDb.Execute sql

rs.AddNew
rs![TextField] = "Addnew 2nd value"
rs.Update
rs.Close
Set rs = Nothing
End Sub

' ===========================
 
To further clarify:
I need to change the autonumber field at the beginning of
each year to reflect the new year. Ie the field should
look like: 2003#### where the #### represents the auto-
incrementing number.
I also tried this solution of defining the autonumber
myself, but I can't get the math right.

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!VoucherNumber.DefaultValue = Year(Now()) * 10000 +
Nz(DMax("[VoucherNumber]", "tblVouchers"), 0) + 1
End If
End Sub
Click to expand...

Tim's right. An AUTONUMBER is *not* suitable for this purpose!

Your code is close, but it's looking up the largest existing
VoucherNumber and incrementing from there - and the largest existing
voucher number might be 20031341 (you're assuming that it's 1341).

I would suggest that Tim's suggestion isn't that far afield: use TWO
FIELDS, VoucherYear and VoucherNumber, and concatenate them for
display purposes. The users don't need to know what's "under the
hood". Consider using a textbox on the Form (or Report, or wherever)
with a control source

[VoucherYear] & Format([VoucherNumber], "0000")

or using this same expression in a Query upon which you base the form;
and use the Form's BeforeInsert event to create a new voucher number:

Me!VoucherYear = Year(Date)
Me!VoucherNumber = NZ(DMax("[VoucherNumber]", "tblVouchers",
"[VoucherYear] = " & Year(Date))) + 1
 
Robert,
it is easy to generate your own sequence number. a function like this could
even be generic
see the code below
and here is how you could call it
someVariable = NextSeqNum("CustomerID", "TblCustomers")

Ok, so you have it now.
but this potentially dangerous stuff, wspecially in multiuser situations
where a user might keep a form open for a long time

If you must use a function like this, make sure you use unbound forms and
get the newid value and insert into the table in one quick operation

HS


-----------------------------------


Function NextSeqNum(SeqColName as string, TableName as string) as long
dim CurrentNum as long
CurrentNum = dmax(SeqColName, TableName) ' lookup the largest sqeuence
number

' check for beginning of year change
if left(currentNum, 4) < datepart("YYYY",date) then
' if this is jan 1 start the numbering seq again
NextSeqNum = DatePart("YYYY", Date) & 1
elseif left(currentNum, 4) = datepart("YYYY",date)
' otherwise just increment the numbr

NextSeqNum = currentNum +1
Else ' unhandled situation
msgbox "Uh-oh!"
endif
End Function
 
Microsoft hasn't added anything along those lines because an AutoNumber is
intended for one purpose only: to provide an (almost guaranteed) unique
value that can be used for a Primary Key. What that value is shouldn't
matter to anyone: in fact, it's rare that you show the user the value of the
AutoNumber field.

If the value is so critical, follow the advice the others have given you,
and don't use an AutoNumber.

--
Doug Steele, Microsoft Access MVP



Robert Miller said:
Hmm... this makes me feel a bit uneasy as well. I tested
it and it seems to work, but I don't think I'll implement
it unless I just don't get any better methods.

I'm actually surprised that Microsoft hasn't created a
module or something themselves to take care of this
inconvenience/problem with Access. Oh well.

Thank you very much for the reply!
-----Original Message-----
Hi Robert,

If you use an Append query and (callously) assign a value to the appended
record's Autonumber/Counter field, then subsequent appends will use that
value as the new base.

For example, create a new table with two fields: Id as Autonumber and
TextField as text.
Then run a test sequence (code follows) to Append with Addnew, Append query,
Append with Addnew.
The ids are allocated as 1, 4321, 4322

Which should solve your problem - although the method makes me feel a bit
nervous. Specifically, I am not sure how transportable it is.

C.D.

' ===========================
Sub TestAppends()

Dim tblName$, rs As DAO.Recordset, sql$

tblName$ = "Crunch Autonumber"
Set rs = CurrentDb.OpenRecordset(tblName$, dbOpenDynaset)
rs.AddNew
rs![TextField] = "Addnew 1st value"
rs.Update

sql = " INSERT INTO [" & tblName$ & "] (Id, TextField) SELECT 4321, 'Via
Append Query'"
CurrentDb.Execute sql

rs.AddNew
rs![TextField] = "Addnew 2nd value"
rs.Update
rs.Close
Set rs = Nothing
End Sub

' ===========================


Robert Miller said:
To further clarify:
I need to change the autonumber field at the beginning of
each year to reflect the new year. Ie the field should
look like: 2003#### where the #### represents the auto-
incrementing number.
I also tried this solution of defining the autonumber
myself, but I can't get the math right.

Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next
Me!VoucherNumber.DefaultValue = Year(Now()) * 10000 +
Nz(DMax("[VoucherNumber]", "tblVouchers"), 0) + 1
End If
End Sub

This adds the year portion each time.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top