Douglas, very sorry and it now males sense.
there is a function and the code is as follows
Function AutoNumFix() As Long
'Purpose: Find and optionally fix tables in current project where
' Autonumber is negative or below actual values.
'Return: Number of tables where seed was reset.
'Reply to dialog: Yes = change table. No = skip table. Cancel = quit
searching.
'Note: Requires reference to Microsoft ADO Ext. library.
Dim cat As New ADOX.Catalog 'Catalog of current project.
Dim tbl As ADOX.Table 'Each table.
Dim col As ADOX.Column 'Each field
Dim varMaxID As Variant 'Highest existing field value.
Dim lngOldSeed As Long 'Seed found.
Dim lngNewSeed As Long 'Seed after change.
Dim strTable As String 'Name of table.
Dim strMsg As String 'MsgBox message.
Dim lngAnswer As Long 'Response to MsgBox.
Dim lngKt As Long 'Count of changes.
Set cat.ActiveConnection = CurrentProject.Connection
'Loop through all tables.
For Each tbl In cat.Tables
lngAnswer = 0&
If tbl.Type = "TABLE" Then 'Not views.
strTable = tbl.Name 'Not system/temp tables.
If Left(strTable, 4) <> "Msys" And Left(strTable, 1) <> "~"
Then
'Find the AutoNumber column.
For Each col In tbl.Columns
If col.Properties("Autoincrement") Then
If col.Type = adInteger Then
'Is seed negative or below existing values?
lngOldSeed = col.Properties("Seed")
varMaxID = DMax("[" & col.Name & "]", "[" &
strTable & "]")
If lngOldSeed < 0& Or lngOldSeed <= varMaxID
Then
'Offer the next available value above 0.
lngNewSeed = Nz(varMaxID, 0) + 1&
If lngNewSeed < 1& Then
lngNewSeed = 1&
End If
'Get confirmation before changing this
table.
strMsg = "Table:" & vbTab & strTable &
vbCrLf & _
"Field:" & vbTab & col.Name & vbCrLf
&
_
"Max: " & vbTab & varMaxID & vbCrLf
&
_
"Seed: " & vbTab &
col.Properties("Seed") & _
vbCrLf & vbCrLf & "Reset seed to " &
lngNewSeed & "?"
lngAnswer = MsgBox(strMsg, vbYesNoCancel
+
vbQuestion, _
"Alter the AutoNumber for this
table?")
If lngAnswer = vbYes Then 'Set the
value.
col.Properties("Seed") = lngNewSeed
lngKt = lngKt + 1&
'Write a trail in the Immediate
Window.
Debug.Print strTable, col.Name,
lngOldSeed, " => " & lngNewSeed
End If
End If
End If
Exit For 'Table can have only one AutoNumber.
End If
Next 'Next column
End If
End If
'If the user chose Cancel, no more tables.
If lngAnswer = vbCancel Then
Exit For
End If
Next 'Next table.
'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
AutoNumFix = lngKt
End Function
i still can not work this over a split database, but no problems on a
non
split database.
thanks
richard
:
Sorry, but there MUST be code.
As I said, there's no "autonumfix" built into Access, so the only way
anything could be happening when you type it into the Immediate Window
is
if
you've got a routine named autonumfix somewhere in your application.
Type autonumfix into the Immediate window, hold your mouse over it and
right-click. Choose Definition from the context menu.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
sorry douglas, my misunderstanding.
there is no code for this error. when the auto number fails, access
offers
a message telling the user that they are trying to create a
duplicate
record
and can not proceed.
the line of code ? autonumfix() resolves the issue, but will not
work
on a
split database.
i hope this makes sense, regards, richard
:
As I've already said, unless you post the code that's causing the
error,
no
one is going to be able to help you!
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
message
Hi Douglas,
i obtained the code from communities. itis as simple as
typingthe
following
in to the immediate window in VB.
? autonumfix()
hope this helps regards richard
:
There's no "autonumfix" built into Access, so it's obviously
code
you've
written (or copied from somewhere).
Without knowing what that code is, there's no way to offer
suggestions.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
in
message
Hi Douglas,
the auto number fix - ? autonumfix() - is used in the
immediate
window
when
access tries to assign an auto number to a new record which
has
already
been
used. this then finds the last record and starts the next
record
to
continue
the numbered sequence.
the table does have a lot of relationships, any suggesstions
would
be
appreciated.
thanks
richard
:
Without know what "the auto num fix" is, it's pretty hard for
us
to
offer
any suggestions.
As to query 2, yes, it's possible, but how you do it depends
on
whether
the
existing table is in any relationships with other tables.
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
"richard harris" <
[email protected]>
wrote
in
message
Hi, I have successfully used the auto num fix (as per this
title)
to
correct
auto number failures in the past. however since i split
the
database
the
fix
no longer works, or I cant get it to work.
i ended up putting the table in the front end, fixing and
then
back
into
the
back end.
so i have two questions
1. does anyone know how to fix when the database is split
or
2. can i drop the autonumber and go to something like
number
+1,
bearing
mind that there are a few thousand records and a lot of
tables.
I only seem to get the auto number problem on the main
table
and
not
any
related tables.
thanks
richard
