Simple delete button on form

[chessico]

I am using Access 2003 and need to add a delete button to my membership form. I have a main form which also has a subform in it showing the payment record for the member selected in the main form. My form is also a ‘tab’ form with the family record in the tab behind the main form of entry.


I have ticked all the boxes to update, cascade etc in the relationships and these are all linked as they should be 1 to many etc.
Until recently everything worked ok but the last person who took over the data entry overwrote old members with new members details and ignored me when I said not to do this. So that meant the ‘new’ members ended up with family and payments records that belonged to the original old member. It has taken me forever restoring from an older backup version and then reinserting all the members over again. I always thought it was bad practise to ‘delete’ records but in hindsight had I put a delete button on my form at least the old members would have been deleted by the user rather than causing the complete chaos he has done to all my records. It has also made me aware that an inexperienced data entry clerk may make a mistake and cannot delete the mistake for instance if they enter a duplicate record. Anyway the point is simply that using the command button wizard I have added a delete button for record operations ‘delete record’ however this does not appear to work and does not delete the record I have in my form. I’ve searched for code snippets in VB which are practically the same and tried these but these don’t work either. What have I done wrong or rather not done?
My code is as follows
Private Sub CommandDelete_Click()
On Error GoTo Err_CommandDelete_Click

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_ CommandDelete_Click:
Exit Sub

Err_ CommandDelete_Click:
MsgBox Err.Description
Resume Exit_ CommandDelete_Click

End Sub

[RuralGuy]

I would still recommend not deleting records but simply marking them as Active/NotActive. Duplicate records will always be a problem but you can do searches and at least eliminate *identical* duplicates from the BeforeUpdate event of your controls.

[RuralGuy]

I also do not recommend turning on Cascade updates or deletes.

[chessico]

Can you tell me why you dont recommend turning on Cascade/Delet updates?
Also how do you mark records as active or not active for a form? I.e how does a user delete the record if they have made a mistake. I know you dont like the term delete but thats what the user will expect to see on a button. So what do you recommend on a form. Access automatically saves the record and you just click forward to add a new record so most people expect to see a delete button as well.

[RuralGuy]

Your form should be based on a query and the query can have a criteria of Active so your delete button could simply set the Active field (an Integer) to 0 and a requery makes it vanish. It would take another form to set the Active field back to -1 if you wanted to. Cascading PrimaryKey updates and deletes is way too powerful and allows you to mess up at the speed of a computer. It is really just a personal thing but there should really never be a reason to update or delete a PrimaryKey. If there is then I would have to examine the design to see why it is necessary.

[chessico]

Ok thanks for the reply. Not quite sure where to go from here because I feel as if we have skirted around the issue of delete. I understand what you say about active or inactive and if this is the case then surely the delete button command would do this action? It doesnt appear to do so and I am not really certain exactly what the code means but it does appear to do what it says and that is delete (although it does not work). I am presuming it is because of the comlexity of my form having a subform for payments. So why is there a facility in Access then to add a delete button? Why do other databases have Delete buttons on their forms and in any case after having used the wizard for command buttons to add this command to my form (the code which was generated by the wizard, I posted in my first post for help) - does it not work? If nothing else and I go about this in a different way I would still like to know whats wrong with the code or my form.

[dcrake]

If you think through this in relation to invoices, there are two things that should never occur.

Invoice headers with invoice lines - Parents with no children

Invoice lines without invoice header - Orphans

So if you create an invoice you then need to add the invoice lines. In order to delete an invoice you must first delete the lines. The only time you should need to delete the invoice is when it has no children. From an accountancy point of view and for full traceability the invoice should only be marked for deletion because if you have gaps in your numbering system C & E will want to know why.

So will the internal auditors. by flagging them as deleted, thus preventing any re use or editing of the invoice you are covering you back against fraud. Also if someone deletes a record by mistake that has say 50 invoice lines on it. The person who processed the original invoice will be very upset if you told them to re do it. As an administrator you can simply reset the flag to Active.

The percentage of invoices that are entered by accident should be very minimal. That is of course if you have got duff data input clerks. Then that becomes a training issue.

As long as you can provide full traceability for every invoice raised and every line raised on that invoice then you will have your back covered, because as the programmer you become the point of least resistance, the one that carries the buck.

David

[RuralGuy]

Here's a site to help you convert the DoMenu commands which are no longer used except by the wizards. Are you getting an error when you use the current code? Do you have Warnings turned off?

[rdbrown]

Another thing I do with database development is to have a menu where you can 'add new' (form opens in data entry mode) or 'edit existing' (uses a query to find appropriate records. It's a little cleaner when there are times you need to edit and times you need to add...

[oddsteven]

Another thing to remember when programming for the users - they might want to delete the record, so make the button say "Delete this record" and just have it set it to make the record inactive or whatever criteria you want to use for that. Let them think they're deleting it and it will make your and their jobs easier.