S
Secret Squirrel
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
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