Delete Records In Subform

[MEGDZIA]

Could you please advise whether it is possible to delete records in suborm.
When I highlight whole record it won't let me do it.
I've chcecked all properties and it should allow to delete records..
Main form is Purchase Order2 and Subform Name is PO1 Subform,

Thanks

[mshmyob]

Yes you can. Depends how you have your settings.

If you have the record selector property set to Yes then you can right click on the far left (record selector) and a little menu will pop up to allow you to delete. Of course your allow deletions property must also be set.

Or you can add a button that allows deletions.

cheers,

[MEGDZIA]

Thank you for your help.Unfortunately none of those options works as the subform is a datasheet so no button available and when I click on the record selector the delete option is not available either. I've chcked all the allows and everything should work fine but it's not unfortunately (:O(

[missinglinq]

Access handles AllowEdits/AllowDeletions/AllowAdditions differently when you're dealing with a form/subform situation.


The first thing you have to understand is that how these properties are set on the form being used as a subform means absolutely nothing! If you run the form independently, they will perform as expected. But if the form is used as a subform, they are completely ignored! The subform "container" is actually a control on the main form, and as such, it is ruled by how these properties are set on the main form.

If AllowDeletions is set to No on the main form, fields cannot be deleted on the main form or on the subform. And to make things really confusing, since the subform is considered to be a control on the main form, if AllowEdits is set to No on the main form, records on the subform cannot be deleted! This is true even if AllowDeletions is set to Yes on the main form ! That's because Access doesn't consider what you're doing as "deleting a record" on the subform, but rather as trying to "edit a field in a record" on the main form!

So, to summarize, to be able to delete a record in a subform, the AllowEdits and the AllowDeletions properties both must be set to Yes on the main form.

If AllowEdits and AllowDeletions are set to Yes on the main form, the next thing to consider is whether or not the main form can be edited. Not all forms can be edited; if a form is based on a non-editable (Read-Only) query, then subform records cannot be deleted, regardless of how the mentioned properties are set on either form or subform. Allen Browne has a short article that explains why some queries are read-only:

http://allenbrowne.com/ser-61.html


Linq ;0)>

[MEGDZIA]

I have checked everything as per your directions and still cannot delete an record...don't really know what else it can be:o(

[missinglinq]

Can you delete a record in the main form?

Linq ;0)>

[MEGDZIA]

yes I can (doing that whole form will be deleted)

[mshmyob]

Do you by chance have referential entegrity enforced and your error trapping turned off? If referential entegrity is on then maybe it can't delete because of a referenced value in another linked table.

Just an idea.

cheers,

[MitchellSpruill]

After many,many hours af research, I have found the answer to this problem.
Here is the setup I have:
Main form (frmCustomers) which displays as a single form. frmCustomers contains a subform(frmCustomersSubform1) which displays as a datasheet. frmCustomersSubform1 contains as subform(frmCustomersSubform2) that also displays as a datasheet. When a user tries to delete from frmCustomersSubform2, it doesn't prompt for delete conformation, but appears too delete. A second attempt causes a conformation prompt and again appears to delete. However when you exit and reenter, the records are still there.

I found that by changing the middle form(frmCustomersSubform1) display from datasheet to single form the problem was solved. However that makes for a ugly setup. Therefore I pull my 3rd subform(frmCustomersSubform2) out and made it a seprate form below my datasheet and added code to populate it from the OnCurrent event in frmCustomersSubform1. This works like a charm. I did not have this problem in 2000-2003 but it sure died in 2007.

Hope this helps someone.
MS

[mshmyob]

??????????????????????????

cheers,