Understanding/Controlling AutoNumber fields

[jrowland]

Trying to understand how a autonumber field works.
Currently have 1 table, with two fields:
Field Name Type
Doc_no autonumber
due_dt date (defined as being required,
cannot be blank or null)

Have designed a simple form with navigational buttons for Next,
Previous, First Record,
Last Record, Add Record, Save and Undo.


Currently the autonumber field is set at 100.
1. User clicks on the Add Record Button
2. All fields are cleared out except the Doc_no field. It shows
(Auto Number) in the field.
3. User adds a date in the due_dt field
3a. autonumber field increments to 101
4. User clicks the Undo button to abort adding a record
5. Next time user goes to add a record, the autonumber field incremented to
102

Couple of questions.
1. From what I can tell, when record pointer is at the last record in the
table and you click on the "NEXT" button, seems to going into the "Add Record
Mode" automatically. Is there any way to stop this.
2. Clicking the Undo record button does not abort the "add routine". A
record is added even though the due_dt field is set to being "Required"

Is there any way to stop the autonumber field being incremented if user
aborts out of the ADD process?

I am using the button wizards to add all my buttons

Thank You for your input.
Jeff

 

[Guest]

First, Autonumbers should not be used as meaningful data. It is guaranteed
they will not always remain sequential with no gaps. Autonumbers are
designed specifically for using as Primay keys and for relating parent/child
records as a Foreign key. I recommend you abandon using them the way you
are. Here is a way to sequentially number documents. This code would go in
the form Current event:

With Me
If .NewRecord Then
.txtDocNum = Nz(DMax("[doc_no]", "MyTableName"), 0) + 1
End If
End With

See below for answers to your specif questions.

Trying to understand how a autonumber field works.
Currently have 1 table, with two fields:
Field Name Type
Doc_no autonumber
due_dt date (defined as being required,
cannot be blank or null)

Have designed a simple form with navigational buttons for Next,
Previous, First Record,
Last Record, Add Record, Save and Undo.


Currently the autonumber field is set at 100.
1. User clicks on the Add Record Button
2. All fields are cleared out except the Doc_no field. It shows
(Auto Number) in the field.
3. User adds a date in the due_dt field
3a. autonumber field increments to 101
4. User clicks the Undo button to abort adding a record
5. Next time user goes to add a record, the autonumber field incremented to
102

Couple of questions.
1. From what I can tell, when record pointer is at the last record in the
table and you click on the "NEXT" button, seems to going into the "Add Record
Mode" automatically. Is there any way to stop this. No

2. Clicking the Undo record button does not abort the "add routine". A
record is added even though the due_dt field is set to being "Required"

Your add rountine should include Me.Undo to clear all the form's bound
controls and destroy the newly created record. The record has not really
been created in the table, only in the form's recordset. Me.Undo gets rid of
it.

Is there any way to stop the autonumber field being incremented if user
aborts out of the ADD process? No

I am using the button wizards to add all my buttons

Button wizards are a good place to start, but are not really that smart.
You can always add to, remove, or modify the code they produce.

 

[Al Campagna]

Jeff,
When a new record is created, and the user aborts that process, the autonumber just
assigned is considered used (stays in memory).
The only way to delete the 101 is to compact the database after an Undo... then the
next new record would be 101.

If successive autonumbers is critical, the try creating your own autonumber function.
Create an ID field (ex. CustID,Num. Long, indexed, no dupes)
Place this as the Default value for your CustID field...
= IIF(IsNull(CustID), 1, DMax("[CustID]", "tblCustomers") +1)

 

[Guest]

It appears we have the same basic concept. One thing about your version - If
there are existing records in the table, there will be no problem. If,
however, it is a brand new table, All you will ever get is Nulls. The DMax
will return Null on an empty recordset and Null + 1 = Null. See my version
to avoid that.

Jeff,
When a new record is created, and the user aborts that process, the autonumber just
assigned is considered used (stays in memory).
The only way to delete the 101 is to compact the database after an Undo... then the
next new record would be 101.

If successive autonumbers is critical, the try creating your own autonumber function.
Create an ID field (ex. CustID,Num. Long, indexed, no dupes)
Place this as the Default value for your CustID field...
= IIF(IsNull(CustID), 1, DMax("[CustID]", "tblCustomers") +1)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Trying to understand how a autonumber field works.
Currently have 1 table, with two fields:
Field Name Type
Doc_no autonumber
due_dt date (defined as being required,
cannot be blank or null)

Have designed a simple form with navigational buttons for Next,
Previous, First Record,
Last Record, Add Record, Save and Undo.


Currently the autonumber field is set at 100.
1. User clicks on the Add Record Button
2. All fields are cleared out except the Doc_no field. It shows
(Auto Number) in the field.
3. User adds a date in the due_dt field
3a. autonumber field increments to 101
4. User clicks the Undo button to abort adding a record
5. Next time user goes to add a record, the autonumber field incremented to
102

Couple of questions.
1. From what I can tell, when record pointer is at the last record in the
table and you click on the "NEXT" button, seems to going into the "Add Record
Mode" automatically. Is there any way to stop this.
2. Clicking the Undo record button does not abort the "add routine". A
record is added even though the due_dt field is set to being "Required"

Is there any way to stop the autonumber field being incremented if user
aborts out of the ADD process?

I am using the button wizards to add all my buttons

Thank You for your input.
Jeff

 

[Al Campagna]

Klatuu,
Doh! I Agree. I should have tested...
What I had used some years ago was...
IIf(IsNull(DLookUp("[LongNum]","tblCustomAutoNum")), 1,
DMax("[LongNum]","tblCustomAutoNum") + 1)
and that works just fine.

However, your Nz(DMax("[doc_no]", "MyTableName"), 0) + 1
is much "slicker."
---------------------------------
By the by, I see you got a "little note" from Microsoft a few weeks ago. (I won't be
specific here in public)
Best of luck... I hope it works out well for you.

Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

It appears we have the same basic concept. One thing about your version - If
there are existing records in the table, there will be no problem. If,
however, it is a brand new table, All you will ever get is Nulls. The DMax
will return Null on an empty recordset and Null + 1 = Null. See my version
to avoid that.

Jeff,
When a new record is created, and the user aborts that process, the autonumber just
assigned is considered used (stays in memory).
The only way to delete the 101 is to compact the database after an Undo... then the
next new record would be 101.

If successive autonumbers is critical, the try creating your own autonumber
function.
Create an ID field (ex. CustID,Num. Long, indexed, no dupes)
Place this as the Default value for your CustID field...
= IIF(IsNull(CustID), 1, DMax("[CustID]", "tblCustomers") +1)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Trying to understand how a autonumber field works.
Currently have 1 table, with two fields:
Field Name Type
Doc_no autonumber
due_dt date (defined as being required,
cannot be blank or null)

Have designed a simple form with navigational buttons for Next,
Previous, First Record,
Last Record, Add Record, Save and Undo.


Currently the autonumber field is set at 100.
1. User clicks on the Add Record Button
2. All fields are cleared out except the Doc_no field. It shows
(Auto Number) in the field.
3. User adds a date in the due_dt field
3a. autonumber field increments to 101
4. User clicks the Undo button to abort adding a record
5. Next time user goes to add a record, the autonumber field incremented to
102

Couple of questions.
1. From what I can tell, when record pointer is at the last record in the
table and you click on the "NEXT" button, seems to going into the "Add Record
Mode" automatically. Is there any way to stop this.
2. Clicking the Undo record button does not abort the "add routine". A
record is added even though the due_dt field is set to being "Required"

Is there any way to stop the autonumber field being incremented if user
aborts out of the ADD process?

I am using the button wizards to add all my buttons

Thank You for your input.
Jeff

 

[Guest]

I have received no note from Microsoft. I would really like to know what you
are refering to. Did I do something inappropriate? I would appreciate a
private note from you.

David DOT hargis at ngc DOT com

Klatuu,
Doh! I Agree. I should have tested...
What I had used some years ago was...
IIf(IsNull(DLookUp("[LongNum]","tblCustomAutoNum")), 1,
DMax("[LongNum]","tblCustomAutoNum") + 1)
and that works just fine.

However, your Nz(DMax("[doc_no]", "MyTableName"), 0) + 1
is much "slicker."
---------------------------------
By the by, I see you got a "little note" from Microsoft a few weeks ago. (I won't be
specific here in public)
Best of luck... I hope it works out well for you.

Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

It appears we have the same basic concept. One thing about your version - If
there are existing records in the table, there will be no problem. If,
however, it is a brand new table, All you will ever get is Nulls. The DMax
will return Null on an empty recordset and Null + 1 = Null. See my version
to avoid that.

Jeff,
When a new record is created, and the user aborts that process, the autonumber just
assigned is considered used (stays in memory).
The only way to delete the 101 is to compact the database after an Undo... then the
next new record would be 101.

If successive autonumbers is critical, the try creating your own autonumber
function.
Create an ID field (ex. CustID,Num. Long, indexed, no dupes)
Place this as the Default value for your CustID field...
= IIF(IsNull(CustID), 1, DMax("[CustID]", "tblCustomers") +1)
--
hth
Al Campagna
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


Trying to understand how a autonumber field works.
Currently have 1 table, with two fields:
Field Name Type
Doc_no autonumber
due_dt date (defined as being required,
cannot be blank or null)

Have designed a simple form with navigational buttons for Next,
Previous, First Record,
Last Record, Add Record, Save and Undo.


Currently the autonumber field is set at 100.
1. User clicks on the Add Record Button
2. All fields are cleared out except the Doc_no field. It shows
(Auto Number) in the field.
3. User adds a date in the due_dt field
3a. autonumber field increments to 101
4. User clicks the Undo button to abort adding a record
5. Next time user goes to add a record, the autonumber field incremented to
102

Couple of questions.
1. From what I can tell, when record pointer is at the last record in the
table and you click on the "NEXT" button, seems to going into the "Add Record
Mode" automatically. Is there any way to stop this.
2. Clicking the Undo record button does not abort the "add routine". A
record is added even though the due_dt field is set to being "Required"

Is there any way to stop the autonumber field being incremented if user
aborts out of the ADD process?

I am using the button wizards to add all my buttons

Thank You for your input.
Jeff