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.

 

[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.

 

[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!