Cancel,Save and Delete buttons for Order form

[TMK]

so i have a main form called orders, and subform called order dtls, they are linked so that if order is deleted the sub dtls also delete, but i want to do this in the form of buttons.

save- lets u save ur progress only if all the details are entered till then can't save record,
delete- deletes the record
cancel- if not saved or new order, and not saved, then it deletes any entered data, but if its saved, and cancel is clicked then is undo changes till last save

there will be a confirmation box for all cancel and delete.

now i was able to do this for customer form, but in order form i cant get this to happen, my guess is because the order form has many calculations with requeries and recalc events,so it's not possible. what do u guys think?

 

[GaP42]

Before commenting on the functioning of the buttons i would suspect that the operation of creating an order and its progression in a workflow could be more sophisticated: eg deleting an "order" record - do you really want this to happen? A processed order needs to be retained in the system, even if it contains an error - set up a status flag (and reason field).
Save: only if all details are entered:
- have you determined what all details means? Presumably at least one valid order detail record exists and the customer, date etc has been entered on the main order.
- On the Order detail record have you written the validation rules in vba on the before update event?
- On the order form have you written the validation rules in the before update event?
Placing these rules in the before update event ensures that any changes to existing records and new records are checked. If a record does not pass a check then the save can be abandoned (Cancel = true) and the event terminated with focus back to the offending control.
Create a Save button that simply checks if the record is dirty (in a state of change), if it is then in vba tell access me.dirty = false - which will initiate the save process - checking the validation rules you have set up before saving as described above.
Delete: assuming you are placing this in the parent / Order form (and given an appreciation of the situation described above), you need to delete the child records first - using a SQL Delete statement for the order details records with the FK for the Order in the where clause. Then delete the parent record with the Order No (PK) with another SQL statement in vba - execute using DoCmd.runsql strSQLfordeletingOrderDetail and DoCmd.runsql strSQLfordeletingOrder. A message and response need to be shown before running either (with an appropriate if statement to react to the response )
Cancel: changes to a record in a state of change or the addition of a new record can be cancelled using the undo command: me.undo - reverts all values to the values before the last save. Again, simple message and response to complete the action or not before committing the undo.\

NOTE: If you are performing a requery - the changes have already been committed - so a CANCEL (undo) and SAVE will not do anything (although with save the validation rules will have been executed if you define them).
Recalc is performed on the afterupdate event - so too late to cancel and any save has been done. recalcs are done on the actual saved data

 

[theDBguy]

now i was able to do this for customer form, but in order form i cant get this to happen,

If you were able to do it on one form but not on another, then you'll have to determine what is different between the two. If you think it's the requeries and recalcs, try removing them to see if you can get it to work. If you still need help, maybe post a sample db with test data.

 

[Pat Hartman]

save- lets u save ur progress only if all the details are entered till then can't save record,

To start with, that isn't the way Access naturally works. The data of the mainform must be saved BEFORE you can enter ANY details using the subform. Therefore, the Order is created as soon as focus moves from the main form to the subform.

So, if you really insist on this and I strongly recommend against it, you'll need to go with unbound forms.

A better solution is to include a "complete" flag in the Order header. It remains at false until the user presses the complete button and your form validates the entered data. Once the complete flag is set to true, the Order may not be updated again without first setting complete back to incomplete.

 

[Pat Hartman]

my guess is because the order form has many calculations with requeries and recalc events,so it's not possible. what do u guys think?

I think that you should never requery a form before data entry is complete because that prevents you from doing adequate validation. Using my suggested method, NONE of the validation should happen (except for required fields) until the user presses the complete button. Then it is all done at once and resets the complete flag if it succeeds.

Also, since you are creating records that are technically incomplete, you should run a query every time you log in that looks for incomplete orders and reminds you to finish or delete them so they don't accumulate.

 

[Pat Hartman]

PS, The relationship Order/OrderDetails should include Cascade Delete assuming you allow deleting of orders because the details have no meaning without the Order header. However, given the discussion so far, I would only allow delete if the Order is marked incomplete. And in other applications that don't use a CompleteYN flag, I would probably not allow deletes at all as @GaP42 recommended. I would add an ArchivedDT and just enter a date to indicate when the record was archived. So you can do both here. Allow the delete if CompleteYN = false and update the ArchiveDT if CompleteYN = true