Sequential Record numbering

[Rick]

Okay. I'm still having a problem seting up a sequential record numbering
system for my database. I've bought the book and although I have'nt read all
1105 pages, I did manage to make it through the glossary with only a slight
head ache.

AutoNumber leaves gaps and is not preferred.

I need a form that presets a record number before I enter any data.

I've tried a couple of suggestions :

" Use this as a text box's default value if you want sequential
numbering: ..... Nz(DMax("MyField", "tblMyTable")) +1 "

This returns an error no matter how I try and rearange it.

And John, ( Thanks for all your useful advice ), I can't seem to get your
suggestion to work either:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub .

I'm not a code writer, ( Or at least I haven't been one since Basic and C+
were in fashion ), but I do need a working database with sequential record
numbering system.

I have a table named "Customer Info" with a field named "Customer InfoID"
that , until now has had it's data type set as " AutoNumber " , which I'm
told is a bad idea.

On the form I'm using, I have a field for Invoive #'s with it's control set
to "Customer InfoID". I need a definative formula, in the right place, that
will achive the results I'm looking for.

I don't ask for much, just a releif from my ignorance.

Thank You,
Rick

 

[Rick Brandt]

Okay. I'm still having a problem seting up a sequential record
numbering system for my database. I've bought the book and although I
have'nt read all 1105 pages, I did manage to make it through the
glossary with only a slight head ache.

AutoNumber leaves gaps and is not preferred.

I need a form that presets a record number before I enter any data.

I've tried a couple of suggestions :

" Use this as a text box's default value if you want sequential
numbering: ..... Nz(DMax("MyField", "tblMyTable")) +1 "

This returns an error no matter how I try and rearange it.

And John, ( Thanks for all your useful advice ), I can't seem to get
your suggestion to work either:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub .

I'm not a code writer, ( Or at least I haven't been one since Basic
and C+ were in fashion ), but I do need a working database with
sequential record numbering system.

I have a table named "Customer Info" with a field named "Customer
InfoID" that , until now has had it's data type set as " AutoNumber "
, which I'm told is a bad idea.

On the form I'm using, I have a field for Invoive #'s with it's
control set to "Customer InfoID". I need a definative formula, in
the right place, that will achive the results I'm looking for.

I don't ask for much, just a releif from my ignorance.

Thank You,
Rick

Both of those suggested methods would do what you are asking IF you replace the
posted field and table names with your actual field and table names. Based on
what you posted I would think that...

Nz(DMax("[Customer Info ID]", "[Customer Info]"),0) +1

If you use that as the default value property you need an = at the front. If in
code then...

Me!ControlName = Nz(DMax("[Customer Info ID]", "[Customer Info]"),0) +1

I used a sample control name because the terminology you used in your second
paragraph was incorrect and/or garbled. Forms don't have fields (they have
controls) and controls have ControlSource properties. The sentence...

I have a field for Invoive #'s with it's control set to "Customer InfoID".

....does not make clear to me what you really have.

 

[Arvin Meyer [MVP]]

Hi Rick,

Gaps are a fact of life. For instance if you use pre-numbered paper POs and
spill coffee on several of them, what do you do? How do you avoid just plain
mistakes. Or how about if you start and order and the customer changes his
mind, or calls back and says that he needs to cancel for a month or 2? Even
if you had the code below working perfectly, you'd still have a problem when
a customer calls back the next day and cancels.

Autonumbers are fine if you don't use them as data. Their main purpose is to
connect tables and no one cares if you deleted several of them or several
hundred.

Now for your problem, this code has been working like this for 6 versions of
Access over the past 13 years when I too was scared by gaps. It still leaves
gaps when I delete a record though. I can fix that by renumbering
everything, which is a real exercise in stupidity.:

=DMax("[InvoiceID]","[tblInvoice]")+1

Notice I don't use NZ which wasn't available 13 years ago. I surround my
names with square brackets because it's a habit, and a necessity if you have
spaces in your field or table names. That expression lives in the
DefaultValue property of the form's text box control.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Okay. I'm still having a problem seting up a sequential record numbering
system for my database. I've bought the book and although I have'nt read
all
1105 pages, I did manage to make it through the glossary with only a
slight
head ache.

AutoNumber leaves gaps and is not preferred.

I need a form that presets a record number before I enter any data.

I've tried a couple of suggestions :

" Use this as a text box's default value if you want sequential
numbering: ..... Nz(DMax("MyField", "tblMyTable")) +1 "

This returns an error no matter how I try and rearange it.

And John, ( Thanks for all your useful advice ), I can't seem to get your
suggestion to work either:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me.txtInvoiceNo = NZ(DMax("[InvoiceNo]", "[Invoices]")) + 1
End Sub .

I'm not a code writer, ( Or at least I haven't been one since Basic and C+
were in fashion ), but I do need a working database with sequential record
numbering system.

I have a table named "Customer Info" with a field named "Customer InfoID"
that , until now has had it's data type set as " AutoNumber " , which I'm
told is a bad idea.

On the form I'm using, I have a field for Invoive #'s with it's control
set
to "Customer InfoID". I need a definative formula, in the right place,
that
will achive the results I'm looking for.

I don't ask for much, just a releif from my ignorance.

Thank You,
Rick

 

[Pat Hartman \(MVP\)]

If you place the expression in the ControlSource, you need to prefix it with
an equal sign:
=Nz(DMax("MyField", "tblMyTable")) +1
Make sure that MyField is defined as an integer.

One thing you need to be careful of when assigning your own unique IDs is
that it is possible to generate duplicates in a multi-user environment and
it is your responsibility to provide the code to handle the problem. The
earlier in the process that you assign the number, the more likely you are
to encounter problems with duplicates. For example, personA opens the form
to start a new record, you assign the ID, personA answers phone. Meanwhile
personB opens the form to start a new record and is assigned the same ID
since personA hasn't saved yet. PersonA finished phone call and saves
record --- oops duplicate - cannot insert record. This is one of the
problems that autonumbers solve for you and the reason that autonumbers
generate gaps. The autonumber is committed as it is assigned so the same
number is never reissued.