INSERTing record then Requery causes duplicate error

[RichardBott]

During the entry of data in a custom form, rather than letting Access XP add a new record when it thinks it needs to, I have provided a [New Record] button. This has code that INSERTs a new row into a table looking after all of the non-duplicate and non-null conditions. When I try to navigate off of this record, I get the Access error ".... changes were not sucessful because they would create duplicate values in the index, primary key or relationship...". I have a unique key primary constraint on the base table.

Is this happening because Access doesn't realize I have inserted the record in the subroutine associated with the [New Record] button and is trying to add it itself?

If this is the problem, how can I 'tell' access that I have added the record so that it doesn't try to add it a second time?

Thanks for any help you can give...

RjB

 

[RoyVidar]

If you are allowing the users to enter the values in controls bound the the forms recordsource, and in addition inserts the same record into the same table, then I'd anticipate such error message. Access knows of one record that is inserted, and one record that it tries to save when (trying to) leaving the record.

To make such insert, try using unbound controls for the data entry.

Roy-Vidar

 

[TheAceMan1]

How are ya RichardBott . . . . .

[blue]RoyVidar[/blue] is right, and as for the error, it is in fact your [purple]PK[/purple] thats causing this.

What you can do instead of inserting, is save the record then move to a new one. In your code, after all your validation passes, try this:

[blue]   DoCmd.RunCommand acCmdSaveRecord
   DoCmd.RunCommand acCmdRecordsGoToNew
[/blue]

See Ya! . . . . . .

 

[RichardBott]

Thanks Roy & Ace. I figured that was the problem. I guess it will be best if I let access look after adding the record!

I need to include a unique ID that is not neccessarily visable to the user. How do I get access to the record when Access is adding it to set up this UID?

 

[RoyVidar]

Not sure I understand the question completely, but if you are somehow calculating the primary key value without the user seing a control, I'd try the following.

Add a control bound to the primary key field to the form, make it not visible, if necessary, then for instance assign the calculated value to the control in the before update event of the form (which is triggered when there's an attempt to save).

BTW - if this is a multiuser app, then calculating primary keys can have some issues. Take a look at MichaelRed's faq faq700-184.

Roy-Vidar

 

[KenReay]

Hi
Roy, soory to be picky, but

"assign the calculated value to the control in the before update event of the form "

did you mean Before INSERT event, otherwise PK would change on every update or record

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[RichardBott]

Thanks Roy and thanks Ken for the clarification - I would have seen that when I dived into it!. I need to get my mind around the events associated with the RecordSource like 'before insert' etc. This sounds just like what I have been looking for.

Thanks guys!

 

[RoyVidar]

No - I meant the before update, because if it's performed in the before insert, then it will be calculated when one starts creating the new record. This would allow for some timespan between the creation of the calculated primary key value and the save, which again, should this be some kind of autonumber system, provide the possibility of more than one user creating the same calculated value -> dupes

What I perhaps should have done, is either described something like that, or perahaps given a little snippet to how to avoid the challenge you bring forth, KenReay. Perhaps one day I'll learn to put all relevant info into a reply;-)

[tt]if isnull(me("PKControl").value) then
me("PKControl").value = <some calculated value>
end if[/tt]

Roy-Vidar

 

[pweegar]

How are you inserting a new record? Are using using the SQL Insert command or ADO update?

If you are using the ADO update method (I suppose DAO update would also work) is to do the followinf:

1) Make sure you enable error handling ONLY for un-handled exceptions.

2) In the code for your button's click event, trap for error number -2147217887, which is the duplicate record found, violating unique key constraint.

3) If the error is encountered, display a message staying so, and if necc. clear out the text boxes and set the focus to the first textbox, or whatever you use to get info.

3) I also include a search function button so that the user can look info BEFORE he/she begins data entry. Then either display a msg box with the found info or a message saying nothing found and set the focus where you want it. OR you can populate the field list to show the user what already exists.

As far as adding a unique value, you can do this with the same block of code that adds your new record. (I generally use a with.. end with block for adding a new record.) The user never has to see the unique value being added.

 

[RichardBott]

Thanks PW. If I don't let Access handle the insert, I shall use SQL Insert - I like to keep as much as I can to SQL.