Can't add record to table and KB article solution doesn't work

[djaneb]

Hello

I'm running across a problem where I can't add a record to a table due
to duplicate in ID field, which is a primary key. I have 150 records in
the table already and when I try to enter a new record it duplicates
one of the earlier numbers, even though it is an autonumber field.

I searched this newsgroup and found reference to a KB article
(http://support.microsoft.com/default.aspx?scid=kb;en-us;884185 ) which
offers the following fix for the error which may occur in Access 2003.
I delete all the relationships and tried the fix.

I ran the routine below, which is the suggested fix in the KB article,
but get an error when I run it

Sub ResetAuto()

Dim iMaxID As Long
Dim sqlFixID As String

iMaxID = DMax("<AutonumberFieldName>", "<TableName>") + 1

sqlFixID = "ALTER TABLE <TableName> ALTER COLUMN
<AutonumberFieldName> COUNTER(" & <iMaxID> & ",1)"

DoCmd.RunSQL sqlFixID

End Sub

The TableName is Child and the AutonumberFieldName is ID.

Do I replace both the values above with those names? I get a syntax
error whatever I do. Should I be specifying a value for iMaxID?

I have been using this database for 2 years with no trouble so you can
imagine this is a real pain and any help fixing it would be much
appreciated. Please let me know if you need any more information. Thank
you.

Deb

 

[djaneb]

do people see it and might they worry if a number is missing or the see
something like 1 2 3 -45 8 9 10 .... ?

Is it a single table or is the table related to a second table and does
that relation ship use this field as the link?

Check outhttp://allenbrowne.com/ser-40.html

Thank you for your reply -

No, the number never gets seen.
The table is linked to another table but this field isn't the link.
The main table is <Family> and each record on this table can have a
number of entries in the <Child> table. The link is the primary key in
the <Family> table but not the primary key in the <Child> table.

I had a cursory look at the link you gave me and will work on the
solutions and let you know how they go.

Many thanks

Deb