Problem ~ New Record and Primary Key

[tweetysamm]

Everyone,

I need some help here. currently i have

table : customer
primary key : account number
button : New Record embedded with this code

DoCmd.GoToRecord , , acNewRec

when i try to insert the same account number and click the New Record button it give me this error :

Run-time error '2105':
You can't go to the specified record.

but when i click on the navigation button to the next record it will show this :

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the fields or that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again

actually my intention is to have the custom/automatic error msg to indicate the duplicate when you click on the NEW RECORD button !! but my coding fail to do so..can anyone help me on this? Thanks a lot

 

[BLeslie88]

do you use dao hard coding...?

if so I recommend you code adding new records yourself.

I have encountered problems historically with access because for some dumb reason it loses track of the autonumber index and rewinds in time...

so... I would ....

set recordset on table to add record

rst.movelast

IDCNT = rst(0)
IDCNT = IDCNT + 1

rst.addnew
rst(0) = IDCNT
rst.update

 

[usermj]

set recordset on table to add record

rst.movelast

IDCNT = rst(0)
IDCNT = IDCNT + 1

rst.addnew
rst(0) = IDCNT
rst.update

yep, I did this before as well, which can avoid the false operation on adding a new record.
The IDCNT value can be shown on the screen, while the actual value hasn't been added into the table.
It will only be assigned into the table after executing the add new record statement.

 

[Pat Hartman]

There are problems with this method.
1. It assumes a certain order to the rows in a recordset. Since "last" means the last record read as opposed to the last record stored or the record with the highest key value (which is what you really want)
2. It gets more inefficient as the table grows since it reads every row in the table.

Whenever you want to generate your own sequence number, use DMax() to find the highest value key. There is no need to read an entire recordset to find the last record which might not even be the one with the highest key.

 

[BLeslie88]

Pat.

Can you supply a 'example' function using dmax() ...

Or do you use it on text box?

I have not used this function before, though I have read on it.

Do you use it coding vba, or on the form or either or...?

Or on error...?

Or all the above.

 

[Pat Hartman]

In the BeforeInsert event of the form:

IDCNT = Nz(DMax("IDCNT", "yourtable"),0) + 1

Don't forget to add error trapping and looping to handle potential multi-user conflicts. This is always a problem when you create your own "autonumber". Access takes care of the problem for you when you use a real autonumber.

 

[BLeslie88]

Thanks

Sweet...

 

[BLeslie88]

Must commend you Pat..

I just used this in an append query!!! lol

And it worked incrementing the ID . . . . .

So cool. Excellent little bit of code!