..would create duplicate values - error 3022

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi guys,

I have VBA code with AddNew line that puts a new record
into the table that has autonumber field - OrderID-key
field.

I fill out the fields for new record like this:
!OrderDate = Date
!ServicePlaceID = rs_frmRepetitive!ServicePlaceID
!OrderDescription = rs_frmRepetitive!OrderDescription

(Where rs_frmRepetitive is a recordset clone of some form)

and assume that Access will fill out the autonumber field
itself. And it does but with the number that already
exists in the table! It does not see the whole scope or
something?!?
Question is: How to make sure that the OrderID field
(autonumber format) gets the unique number as it should?

thanks in advance.
 
Your table's primary should be based solely on the
AutoNumber. If this is the case, are there any other
unique indexes on the table that could be causing this
error message? Perhaps they need to be re-evaluated?
I've never seen .AddNew generate a duplicate AutoNumber
value.
 
Neither have I..
I have checked all other fields..They are not indexed and
duplicates OK.

In the code, I don't mention at all the OrderID field.
It is key field, autonumber, and I thought addNew will
take care about it.

If there were higher number designated and deleted after,
there is a gap in the numbers.

When VB adds using AddNew, it finds the first number in
the gap and starts adding in sequence. Sooner or Later it
hits the end of the gap (with some record already having
that "gap's edge" number), finds that created autonumber
already exists and gives me an error.
What the hell? Can't it recognize that that number in
sequence already exist?

Any ideas? Should i use Random? But will it garantee that
it won't take the number already assigned?
 
Try open the table, and manaully fill in data, and see if the autonumner is
filled in by itself and unique.
Also make sure OrderID has a key symbol next to it in the design mode.
 
New thought. Your code works with a RecordsetClone. Is
the 'New Record' current while your code is executing? If
so, try saving the record first. You may be inadvertently
locking something up by adding 2 records concurrently via
the same form.

If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord
 
Back
Top