Thanks, Graham, it turned out to be simpler. I just copied the file, then
deleted the old one and renamed the copy. That seemed to fix the problem.
Dave
"Graham Mandeno" wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:008F91E7-A2D7-4F8B-A828-(E-Mail Removed)...
> >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
>
>
>
|
Similar Threads
