auto number format

[vb_Dumb]

Is there a way to make the auto number have 6 digits like 000001,
000002 ect... any help would be cool
Thanks in advance, Dan

 

[tkelley via AccessMonster.com]

Nope. But if you post an explanation of why you'd want such a thing, maybe
someone will have some good ideas to get you where you need to be.

 

[vb_Dumb]

ya i found it duh properties format, format, 000000 duh

 

[tkelley via AccessMonster.com]

Yeah ... that's what I meant to say. Good job.

ya i found it duh properties format, format, 000000 duh

 

[Jeff Boyce]

Dan

Access autonumbers are designed to be used to provide unique row
identifiers. They are generally unfit for human consumption.

If you are looking for a way to "format" an Autonumber, why? What will
having a formatted autonumber allow you/your users to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Linq Adams via AccessMonster.com]

As Jeff has said, Autonumbers are intended to be used for one purpose and
only one purpose, to provide a unique identifier for each record. Here's a
post I've archived from a gentleman named John Vinson, MVP, explaining how
autonumbers work:

************************** Quote ************************************
When using Autonumber, do be aware that there will be gaps in the numbering -
any record that's deleted will leave a gap; hitting <Esc> after starting a
record will leave a gap; adding records using an Append query may leave a
gap, often a huge one; replicating the database will make your invoice
numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous.

************************ End Quote ********************************
If you need something like a PO number or an account number, etc. that will
be consecutive, redefine the datatype of the field to Text and use a hack
like this for generating an auto-incrementing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.AI_Number = "000001"
Else
Me.AI_Number = Format(DMax("Val([MyAI])", "YourTable") + 1, "000000")
End If
End If
End Sub

Just use your table name for "YourTable," your textbox name for AI_Number,
and the name of the field in your table that will hold the number for MyAI.

 

[vb_Dumb]

As Jeff has said, Autonumbers are intended to be used for one purpose and
only one purpose, to provide a unique identifier for each record. Here's a
post I've archived from a gentleman named John Vinson,  MVP, explaininghow
autonumbers work:

************************** Quote ************************************
When using Autonumber, do be aware that there will be gaps in the numbering -
any record that's deleted will leave a gap;  hitting <Esc> after starting a
record will leave  a gap; adding records using an Append query may leave a
gap, often a huge one; replicating the database will make your invoice
numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and
particularly not for consumption by accountants and auditors. Invoice
sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such
people get very nervous.

************************ End Quote ********************************
If you need something like a PO number or an account number, etc. that will
be consecutive, redefine the datatype of the field to Text and use a hack
like this for generating an auto-incrementing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
 If RecordsetClone.RecordCount = 0 Then
  Me.AI_Number = "000001"
 Else
  Me.AI_Number = Format(DMax("Val([MyAI])", "YourTable") + 1, "000000")
  End If
End If
End Sub

I have used the DMax before (by used I mean copied off a website) But
the problem I have is the first record will work and the second one i
add i get an error message saying that my table cannot be found i
think the problem is that i am trying to use a linked table that is on
a network because i am in a multi-user envirorment I think I need more
code or something but i know you shouldn't use an auto number but i
can't figure out how to do it so if you can figure out a way to give
me an static "auto" number that would be awesome
Thanks for your help!
Dan

 

[Rick Brandt]

I have used the DMax before (by used I mean copied off a website) But
the problem I have is the first record will work and the second one i
add i get an error message saying that my table cannot be found i think
the problem is that i am trying to use a linked table that is on a
network because i am in a multi-user envirorment I think I need more
code or something but i know you shouldn't use an auto number but i
can't figure out how to do it so if you can figure out a way to give me
an static "auto" number that would be awesome Thanks for your help!
Dan

The DMax() + 1 method is used extensively by LOTS of people and I have
never heard of that sort of problem. What event were you using? In a
Multi-User environment the BeforeUpdate event of the form is the event
you should be using.

 

[vb_Dumb]

Yes I am using forms before update event, here is my code. should I
have the text box bound to the field on my table? i need the info in
my table for my reports. Does it matter if the forms based on a query
that's based that a table? And what should be the data type of the
field on the table... sorry i have no idea what i am doing I've done
simple forms but no vb and now I am doing a quality database for a
company for no money and i am only a temp so anyway any help would
greatly be appreciated

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.DMRnumbertxt = "000001"
Else
** Me.DMRnumbertxt = Format(DMax("Val([DMR_Number])", "ETP_Table")
+ 1, "000000")**
End If
End If
End Sub
(** Ends Here)

Thanks, Dan

 

[Rick Brandt]

Yes I am using forms before update event, here is my code. should I have
the text box bound to the field on my table?

Of course. Otherwise it won't get saved.

i need the info in my table
for my reports. Does it matter if the forms based on a query that's
based that a table? And what should be the data type of the field on the
table... sorry i have no idea what i am doing I've done simple forms but
no vb and now I am doing a quality database for a company for no money
and i am only a temp so anyway any help would greatly be appreciated

Private Sub Form_BeforeUpdate(Cancel As Integer) If Me.NewRecord Then
If RecordsetClone.RecordCount = 0 Then
Me.DMRnumbertxt = "000001"
Else
** Me.DMRnumbertxt = Format(DMax("Val([DMR_Number])", "ETP_Table")
+ 1, "000000")**
End If
End If
End Sub
(** Ends Here)

Thanks, Dan

A couple of points. First checking the RecordsetClone count is not
reliable because a filter could be applied. Just use Nz() along with
your DMax() so that if no records exist it will return zero (to which you
add 1).

Second it would be a LOT more efficient to use a numeric field for this
and then format it to show the padded zeros. Getting the maximum value
of the expression Val() cannot use an index and will be a lot slower.

 

[vb_Dumb]

Can u show me because your talking french to me vb is like trying to
dance in a full body cast to me...
Thanks Dan

 

[Rick Brandt]

Can u show me because your talking french to me vb is like trying to
dance in a full body cast to me...
Thanks Dan

If it a number field it would look like...

If Me.NewRecord Then
Me.DMRnumbertxt = Nz(DMax("DMR_Number", "ETP_Table"),0)+1
End If

 

[vb_Dumb]

Its is returning Runtime error 2001 the previous action was canceled?
doesnt make sense to me...