Autonumber Restart

[Guest]

I am using the following code to restart the sequence of my AutoNumber after
a record has been deleted. When a record is deleted it restarts the
AutoNumber back to the next sequential number in the sequence so when I
create a new record it will not lose its sequence. This has worked fine up
until the time when I split my database. I'm now getting an error like this:

"3611 - Cannot execute data definition statements on linked data sources"

Here is the code I am using to restart the sequencing:
---------------
Private Sub Form_AfterDelConfirm(Status As Integer)
On Error GoTo ErrTrap
Dim Rsc As String

Rsc = Me.RecordSource
' Take action only if the deleted records are contiguous to the New Record
If Me.NewRecord = True Then
DoCmd.Echo False
Me.RecordSource = ""
P_SetAutoNum "tblARRMA", "ID"
Me.RecordSource = Rsc
DoCmd.Echo True
Me.Repaint
DoCmd.GoToRecord , , acNewRec
End If ' Me.NewRecord = True

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint

Call AuditDelEnd("audTmpARRMA", "audARRMA", Status)

End Sub
---------------
Sub P_SetAutoNum(ByVal Tnm As String, Pkn As String)
On Error GoTo ErrTrap
Dim Qst As String, NumStart As Long

' Set Next AutNumber as one more than the max of
' those now existing
NumStart = Nz(DMax(Pkn, Tnm), 0) + 1
Qst = "ALTER TABLE " & Tnm & " ALTER COLUMN " & _
Pkn & " COUNTER (" & NumStart & ", 1);"
CurrentDb.Execute Qst, dbFailOnError

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint
End Sub
---------------

Can anyone shed some light on how I can make this work in my split database?

Thanks
SS

 

[Graham Mandeno]

Hi SS

Resetting an autonumber is generally considered A Bad Idea. If you really
want to have a numeric primary key that does not have gaps where the last
record has been deleted or never written, then use a long integer field and
set its default value to:

=Nz(Dmax("[FieldName]", "[TableName]"), 0) + 1

That said, you *can* execute a DDL statement against a database other than
CurrentDb, but you must first open that database and assign it to a Database
object:

Dim LinkedDb as DAO.Database
Set LinkedDB = OpenDatabase("path to your back-end")
LinkedDb.Execute ...
LinkedDb.Close

BTW, how did you get on with the email checkboxes?

 

[Guest]

Hi Graham,

First off let me say that the email checkboxes worked perfectly! I forgot to
post back to you on the other thread. But thank you very very much for all
your work with that.

I thought about using the long integer version instead of the autonumber but
I'm already using an autonumber in another table in my db and I was trying to
keep things uniformed. And since I already have the other tables filled with
records it might be better just to continue this way instead of trying to go
back an reinvent the wheel, so to speak.

If I was to execute the DDL statement where would I put that code you wrote?
Also, will that code open the BE when a record is to be deleted and then
close it when it's done? That's what it looks like to me but I could be
wrong.

Hi SS

Resetting an autonumber is generally considered A Bad Idea. If you really
want to have a numeric primary key that does not have gaps where the last
record has been deleted or never written, then use a long integer field and
set its default value to:

=Nz(Dmax("[FieldName]", "[TableName]"), 0) + 1

That said, you *can* execute a DDL statement against a database other than
CurrentDb, but you must first open that database and assign it to a Database
object:

Dim LinkedDb as DAO.Database
Set LinkedDB = OpenDatabase("path to your back-end")
LinkedDb.Execute ...
LinkedDb.Close

BTW, how did you get on with the email checkboxes?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the following code to restart the sequence of my AutoNumber
after
a record has been deleted. When a record is deleted it restarts the
AutoNumber back to the next sequential number in the sequence so when I
create a new record it will not lose its sequence. This has worked fine up
until the time when I split my database. I'm now getting an error like
this:

"3611 - Cannot execute data definition statements on linked data sources"

Here is the code I am using to restart the sequencing:
---------------
Private Sub Form_AfterDelConfirm(Status As Integer)
On Error GoTo ErrTrap
Dim Rsc As String

Rsc = Me.RecordSource
' Take action only if the deleted records are contiguous to the New
Record
If Me.NewRecord = True Then
DoCmd.Echo False
Me.RecordSource = ""
P_SetAutoNum "tblARRMA", "ID"
Me.RecordSource = Rsc
DoCmd.Echo True
Me.Repaint
DoCmd.GoToRecord , , acNewRec
End If ' Me.NewRecord = True

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint

Call AuditDelEnd("audTmpARRMA", "audARRMA", Status)

End Sub
---------------
Sub P_SetAutoNum(ByVal Tnm As String, Pkn As String)
On Error GoTo ErrTrap
Dim Qst As String, NumStart As Long

' Set Next AutNumber as one more than the max of
' those now existing
NumStart = Nz(DMax(Pkn, Tnm), 0) + 1
Qst = "ALTER TABLE " & Tnm & " ALTER COLUMN " & _
Pkn & " COUNTER (" & NumStart & ", 1);"
CurrentDb.Execute Qst, dbFailOnError

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint
End Sub
---------------

Can anyone shed some light on how I can make this work in my split
database?

Thanks
SS

 

[Guest]

After sitting here dwelling on this and tinkering a bit with the numeric
primary key I'm thinking about changing them all from autonumber to this way.
It doesn't lose any of the record id's I have existing in the table and it
seems to just start where I left off. So knowing this makes me feel
comfortable that everything will be seemless if I was to change all my
autonumber fields. Am I correct in assuming this? Also, if I do this will it
affect my queries, reports, etc? Will everything operate the same as it is
now if I just change the primary key from an autonumber to a numeric number?

Hi SS

Resetting an autonumber is generally considered A Bad Idea. If you really
want to have a numeric primary key that does not have gaps where the last
record has been deleted or never written, then use a long integer field and
set its default value to:

=Nz(Dmax("[FieldName]", "[TableName]"), 0) + 1

That said, you *can* execute a DDL statement against a database other than
CurrentDb, but you must first open that database and assign it to a Database
object:

Dim LinkedDb as DAO.Database
Set LinkedDB = OpenDatabase("path to your back-end")
LinkedDb.Execute ...
LinkedDb.Close

BTW, how did you get on with the email checkboxes?
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I am using the following code to restart the sequence of my AutoNumber
after
a record has been deleted. When a record is deleted it restarts the
AutoNumber back to the next sequential number in the sequence so when I
create a new record it will not lose its sequence. This has worked fine up
until the time when I split my database. I'm now getting an error like
this:

"3611 - Cannot execute data definition statements on linked data sources"

Here is the code I am using to restart the sequencing:
---------------
Private Sub Form_AfterDelConfirm(Status As Integer)
On Error GoTo ErrTrap
Dim Rsc As String

Rsc = Me.RecordSource
' Take action only if the deleted records are contiguous to the New
Record
If Me.NewRecord = True Then
DoCmd.Echo False
Me.RecordSource = ""
P_SetAutoNum "tblARRMA", "ID"
Me.RecordSource = Rsc
DoCmd.Echo True
Me.Repaint
DoCmd.GoToRecord , , acNewRec
End If ' Me.NewRecord = True

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint

Call AuditDelEnd("audTmpARRMA", "audARRMA", Status)

End Sub
---------------
Sub P_SetAutoNum(ByVal Tnm As String, Pkn As String)
On Error GoTo ErrTrap
Dim Qst As String, NumStart As Long

' Set Next AutNumber as one more than the max of
' those now existing
NumStart = Nz(DMax(Pkn, Tnm), 0) + 1
Qst = "ALTER TABLE " & Tnm & " ALTER COLUMN " & _
Pkn & " COUNTER (" & NumStart & ", 1);"
CurrentDb.Execute Qst, dbFailOnError

ExitPoint:
On Error GoTo 0
Exit Sub

ErrTrap:
MsgBox err.Number & " - " & err.Description
Resume ExitPoint
End Sub
---------------

Can anyone shed some light on how I can make this work in my split
database?

Thanks
SS

 

[John Vinson]

Will everything operate the same as it is
now if I just change the primary key from an autonumber to a numeric number?

Yes. An autonumber IS a numeric number - a Long Integer to be exact -
which just gets automatically assigned. If you use a Long Integer and
use Graham's code to automatically assign it, all your queries, forms,
etc. will work just as they do now; the only thing you lose (and IMO
it's irrelevant) is the ability to enter records directly into the
Table or by pasting them in from outside sources (since these will
bypass the increment code).

John W. Vinson[MVP]

 

[Graham Mandeno]

Thanks, John - couldn't have put it better myself

Just to add a bit about my personal preferences: I use autonumber keys
extensively, but not in situations where the "natural key" is numeric and is
"for public consumption". Examples of this are invoice numbers, quote
numbers, steps in a sequence, etc.

An autonumber is great, but if you are in any way concerned about what the
number might be, then don't use 'em.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Guest]

Thanks Graham! I will take your advice and change them.

Thanks, John - couldn't have put it better myself

Just to add a bit about my personal preferences: I use autonumber keys
extensively, but not in situations where the "natural key" is numeric and is
"for public consumption". Examples of this are invoice numbers, quote
numbers, steps in a sequence, etc.

An autonumber is great, but if you are in any way concerned about what the
number might be, then don't use 'em.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Yes. An autonumber IS a numeric number - a Long Integer to be exact -
which just gets automatically assigned. If you use a Long Integer and
use Graham's code to automatically assign it, all your queries, forms,
etc. will work just as they do now; the only thing you lose (and IMO
it's irrelevant) is the ability to enter records directly into the
Table or by pasting them in from outside sources (since these will
bypass the increment code).

John W. Vinson[MVP]

 

[Guest]

Thanks for your advice John.

Yes. An autonumber IS a numeric number - a Long Integer to be exact -
which just gets automatically assigned. If you use a Long Integer and
use Graham's code to automatically assign it, all your queries, forms,
etc. will work just as they do now; the only thing you lose (and IMO
it's irrelevant) is the ability to enter records directly into the
Table or by pasting them in from outside sources (since these will
bypass the increment code).

John W. Vinson[MVP]