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...b;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

(E-Mail Removed) wrote:
> 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...b;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

A little more information please. Is the number use by humans? That is
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 out http://allenbrowne.com/ser-40.html


--
Joseph Meehan

Dia 's Muire duit

On Jan 25, 1:04 pm, "Joseph Meehan" <sligoNoSPAM...@hotmail.com> wrote:
> dja...@gmail.com wrote:
> > 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.

>
> > Deb A little more information please. Is the number use by humans? That is
> 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
>
> --
> Joseph Meehan

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