Select and delete a record

[novice]

Hi,

I have a form (with some fields) bound to a table. I use this form to add and delete records present in the table using save and delete buttons on the form.

I have entered all the details (with unique primary key field) in the form and hit save and able to save the record.

I have closed the form and reentered all the details which i have just now saved and clicked on delete. Then i'm getting the following error message
"The http://www.ozgrid.com/forum/autolink.php?id=51&script=showthread&forumid=7changes you requested to the table were not successful because they would create duplicate values.....".

I'm unable to understand why this error message is popping up. as i'm trying to delete the record, but instead its trying to create the record i guess.

I have the following code for delete button
Private Sub Command89_Click()

On Error GoTo Err_Command89_Click

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_Command89_Click:
Exit Sub

Err_Command89_Click:
MsgBox Err.Description
Resume Exit_Command89_Click

End Sub
Can any one help me out in resolving this issue. Thanks.

 

[The_Doc_Man]

Not gonna swear to it, but something you said makes me think I've got at least a clue:

I have closed the form and reentered all the details which i have just now saved

If that is the case, your problem is that when you click the Delete button (on the Icon tool bar, perhaps), you do something that forces a type of navigation. But navigation implies a SAVE action before you change records.

The question is whether you can use only the navigation buttons to get to the required record and whether the Delete button works in that case.

If so, your problem is that you cannot perform a random selection from that form as designed, you can only do data entry - and that's what it tried to do.

One way around this is to have an extra control on the form, perhaps a list-box or combo-box, that shows the available records and lets you select one. The wizard for the combo box setup will ask you whether you want to STORE data or SEEK data or something else. If you use the extra control as a record finder, that would avoid the problem of creating a dup record.

 

[novice]

Thanks for your reply. But having a combox box to select a particular record is not feasible solution as:
1. The record entries are huge (~thousands).
2. Looking up a record is based on multiple fields and selecting multi fields using combo boxes is also difficult.

Is there any other way to do this?. Thanks.