How to delete a row in a sub table when I key DEL on my keyboard?

[jazee007]

Hi every body,
Please can somebody help me to find a solution how to delete a row in a sub table. Each time I try to delete the row, I got a pop up menu saying "You are about to delete 1 records. If you click yes you won't be able to undo the delete operation."

When I click yes, it shows that the record is deleted. But as soon as I refresh. the records is still there. It remains in the table.

Your solution please..

[zmbd]

Have you closed the form and re-opened?
Have you actually looked at the data tables?

One thing to note: In VBA, Refresh for a form is not the same as Requery. Refresh will not show any additions or deletions to a bound form's recordset whereas the Requery forces the form to go back to the data table and require the records.

[jazee007]

I haven't written any code for the moment. I refresh it with F5. Please what is the VBA code to force to delete the row in the sub table?

[jazee007]

I have closed and re-opened. Its the same. I've checked the relation ship as well, its ok. The recordset type have been change from dynaset to dynaset(inconsistent update). Still the same. I think we need some codes to force it to delete...

[Seth Schrock]

I think that to do this you would have to set the relationship to cascade delete. The only other option would be to create a button on a form and then code the deletion of the record in the related table first and then the main table's record.

[jazee007]

Can't we use the event "On Delete"?

[zmbd]

BACKUP YOUR DATABASE FILE!

Inconsistent update allows a whole host of ugly nasty stuff to happen and should not (IMHO) normally be used in a properly normalized database > Database Normalization and Table Structures.(the fact that it is in use at all points to some serious flaws in the database structure!)

I suspect that your "subtable" record is related to a record in one, or more, other table(s) that you are unaware of. You will need to go back thru your database design and fix all of this... more than likely from scratch...

Can't we use the event "On Delete"?

No, This is an event that is fired when a record is deleted, not a means to force delete.

[zmbd]

BTW: Is this a split database? If so, what is the backend data source?

[Seth Schrock]

There is a Before Delete and After Delete event in Access 2010 (what version are you using?). However, these are linked to macros and I'm not sure how to delete a record in another table with a macro. I also find troubleshooting macros difficult.

@Z Wouldn't the Before Delete allow you to delete the record in the related table first and then allow the record to delete from the current table?

[zmbd]

If you know which tables are related... and I can certainly pull that information out of the database via VBA; however, OP need to go in to table relationships and determine what is going on with the records before I'd provide any more help.

My fear here is that the data will become orphaned due to the use of inconsistent updates.

There is a very good reason behind why that record isn't going away as OP intends from that one single view of the data. Datasheets with subtables is a very limited means of viewing the information.

[jazee007]

I have the Customers and Sales table which are related from one to many. The table Customers contain only the particulars of the Customers and the Sales table contain all the particulars of a sales transaction.

For example if during a Sales transaction where there are many articles being scanned and one have been scanned twice by wrong manipulation of the sales person, I clicked on the row which have been entered twice and key in Delete on the keyboard. It won't go away.

If there are 3 rows of data, the data won't become orpahned if I delete one. At least it should go away.

Can we write a VBA code like if there are more than one records(to avoid orphaned), force deletion?

[zmbd]

For example if during a Sales transaction where there are many articles being scanned and one have been scanned twice by wrong manipulation of the sales person, I clicked on the row which have been entered twice and key in Delete on the keyboard. It won't go away

Normally in a POS when an item is scanned/sold, the item is removed from inventory in some manner. I would suspect that this is the underlying cause as to why you can not simply delete the duplicate sale... you need to add that item back to the inventory. Hence my position in post#10

SO within this thread, we can take a look at how to back out the "transaction" by streaching the question a tad by looking at the code behind the "sale." Understanding how that record is created is importaint to understanding how to remove the record; thus, "deleting" the record as requested. Otherwise we're looking at a new thread.


So, let's start with your database design at a high level view, what I have so far is:

tbl_customer : this table has the details behind your customer
tbl_sales : relates the tbl_customer to the product sold in some manner.
tbl_? : the other tables please and how they are related.

We then need to see the code and sqls behind the "Sales" transaction. It is realy very poor programming to not provide a "LineItemVoid" and "VoidSale" even the old mechanical cash registers could do that!

Before posting any of your code, sql, etc... please do a few things.
1) make sure you have a backup of the production database.
2) make a second copy for us to use as a development database
3) read thru >> Before Posting (VBA or SQL) Code.
4) read thru Database Normalization and Table Structures.
5) answer Post#8
6) which version of Access are you using

#3 and 4 are essential for you to understand as these are the core upon which most of the experts on-site start and it will help you to understand us.
#5 and #6 are essential in that any solution we may comeup with will vary based on these conditions.

Until we have this information, this thread is stalled.