Hi Dave
I'm afraid I'm going to give you more questions than answers!
What sort of field is your record number? Is it an AutoNumber? If so, is
its NewValues property set to Increment or Random?
If it is not an AutoNumber, how are new values generated?
When you say there are no "breaks" in the table, do you mean there are no
gaps in the record number sequence? If so, are these new records being
created with the same values as existing records?
Is the record number field the primary key of your table?
If the field in question is an incremental autonumber and is the primary
key, then the following function will reset the "seed" to one greater than
the current maximum value:
Public Function ResetAutonumber( _
sTable As String, _
Optional sField As String, _
Optional lSeed As Long _
) As Long
'sTable = Table containing autonumber field
'sField = Name of the autonumber field
' (default is the first Autonumber field found in the table)
'lSeed = Long integer value you want to use for next AutoNumber
' (default is one more than the current maximum)
Dim cnn As Object 'ADODB.Connection
Dim cat As Object 'ADOX.Catalog
Dim col As Object 'ADOX.Column
Dim tbl As Object 'ADOX.Table
Dim sRemoteTable As String
On Error GoTo ProcErr
Set cat = CreateObject("ADOX.Catalog")
cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables(sTable)
If tbl.Type = "LINK" Then
Set cnn = CreateObject("ADODB.Connection")
cnn.Open _
CurrentProject.Connection.ConnectionString & ";" & _
"Data Source=" & _
tbl.Properties("Jet OLEDB:Link Datasource")
cat.ActiveConnection = cnn
sRemoteTable = tbl.Properties("Jet OLEDB:Remote Table Name")
Set tbl = cat.Tables(sRemoteTable)
End If
If Len(sField) = 0 Then
For Each col In tbl.Columns
If col.Properties("AutoIncrement") Then
sField = col.Name
Exit For
End If
Next
If Len(sField) = 0 Then GoTo ProcEnd
Else
Set col = tbl.Columns(sField)
End If
If lSeed = 0 Then
lSeed = Nz(DMax(sField, sTable), 0) + 1
End If
col.Properties("Seed") = lSeed
ResetAutonumber = lSeed
ProcEnd:
On Error Resume Next
If Not cnn Is Nothing Then
cnn.Close
Set cnn = Nothing
End If
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
ProcErr:
MsgBox Err.Description
Resume ProcEnd
End Function
--
Good Luck
Graham Mandeno [Access MVP]
Auckland, New Zealand
Dave Couch said:
I have a macro that opens a form in add mode for the table Transmittals.
However, when I try to save the record, the record number is somewhere in
the
middle of the record numbers and will not let me save. I have tried the
"repair and compact" with no help. I don't appear to have any "breaks" in
the table. Any help would be appreciated.
Dave Couch
