Error when deleting a record

[access]

I have a form "Test_Form_To_Delete" with textbox CustomerID and a button "Delete Record"

When i select the last record (4, by pressing the next button in record toolbar below the form) and entered "0102864" and When "Delete Record" is pressed I'm getting the following error message "The changes requested for the table are not successful because they would create duplicate values...".

What i'm expecting is to delete the records present in Customers and Items table with matching customerID "0102864". (Enabled cascade delete in table relationship). Please find the attached database.


My intuition is that it is trying to create the record instead of looking up for the matching entry. Can any one let me know on how to handle this case.

[thhui]

Could you convert your access2007 file to access 2000 or 2002 format firstly?

[access]

Could you convert your access2007 file to access 2000 or 2002 format firstly?

Please find the attached DB for 2002 format.

[thhui]

In the bound form just in your case,
if you want to choose record to delete,
you must go to the record firstly.

You cannot just type the customerID in the form and expect it going to the specified record.

If you do this in the new record just as what you describe,
you are indeed creating a new record (cause you go to the last one and insert a new one) but with the same customerID.

This is prohibited as the customerID could not be duplicated by the setting in the table structure.

In your case, you should instead search the required record and go to it firstly.
Just put a combo box in the form to have its control source to select the customerID from the customer table.
then put a button on nearby side and setup a macro or vba code to
let the form goto the specified customerID record you select.

Then you can delete the record accordingly.

I recommend you to study the basic form operation of addition, deletion, searching record in some elementary access book.

[access]

Thanks for your reply. I have already figured out that record gets deleted only when you go to the specified record and delete button is clicked.

But what m looking for is, is there any other way to do for "just enter customerID and delete the record". The example which i have shown is part of my application.

In my original form, when the form is opened user fills the details in the fields of the form and selects delete. The user doesnot know where exactly the specified record is (..and its obvious) in order to go to that specified record and delete it. Also there wont be any records available in the form (to click on 'next record" below the fom), even though the form is bound to a table.

Hope you understand what i want to accomplish with the example above. I'm thinking to write a query to do this. Can you suggest on how to go about with this.

[thhui]

Don't use bound form.

Just put a combo box in the form to have its control source to select the customerID from the customer table.
then put a button on nearby side and setup a macro or vba code to
delete the record accordingly.