Vba code "Only for experts"

[deepaksharma]

Good morning!!

I have a main form which is linked to a sub form. When I enter data in the main form and go to the sub form, the data of the main form gets automatically saved in the table.
Is there any VBA code through which data saved in the table can be deleted or undo?
Many of you will say that it is not possible but it is possible I just don't know the right way to do it yet.
That's why I wrote it only for experts.


Thanks in advance.

[June7]

Data in fields can be changed or deleted, even whole records can be deleted with VBA or even macro code.

If you want to "undo" user inputs on form before fully committed to table, that means code in form BeforeUpdate event. This is data validation.

[deepaksharma]

Data in fields can be changed or deleted, even whole records can be deleted with VBA or even macro code.

If you want to "undo" user inputs on form before fully committed to table, that means code in form BeforeUpdate event. This is data validation.

June you are right but what coding can I use for this because actually there is a save button in my form I want that if the user closes the form without clicking on the save button while entering a new record Then all the current data of main form and sub form which has been saved in the table gets deleted (undo) and if the user has opened an old record in the form for editing and has closed the form without saving it. So that record also gets undo in the table.

[Welshgasman]

How many times are you going to post this question?

Make the form and subform unbound, and do all the hard work yourself?

[June7]

I design database so user must use my buttons to save record or delete or close form. First thing I do is disable the X close on every form. Disable ribbon, disable right click menu, disable shortcut keys, etc. If you don't do that and allow them to use X close, etc., how should Access "know" they haven't clicked your "Save" button? This will probably have to involve a global variable or TempVar or an unbound textbox. Whichever you prefer, it must be set to False when form opens. Then if user tries to leave form without first "Save" then code somewhere has to check value of this. If user did click "Save", code would change value to True. In which case it is okay for user to continue. If it is False then user did not click "Save" and you can take appropriate action. The trick is figuring out where to put code that will check for status of this variable or change its value. Perhaps in form Unload event.

[Minty]

By far the simplest route is to hide the subform until all the data is present you need in the main form.
If the main form data is absolutely mandatory, then do as June says and prevent the user leaving the form until all the data is filled out, or the they have to cancel the input, undo all the input and close the form.

If they press save and all the data is present then you make the subform visible, if not tell them what is missing and continue, and give them a cancel & close button if they want to escape.

This can all be called from the BeforeUpdate event.

[deepaksharma]

By far the simplest route is to hide the subform until all the data is present you need in the main form.
If the main form data is absolutely mandatory, then do as June says and prevent the user leaving the form until all the data is filled out, or the they have to cancel the input, undo all the input and close the form.

If they press save and all the data is present then you make the subform visible, if not tell them what is missing and continue, and give them a cancel & close button if they want to escape.

This can all be called from the BeforeUpdate event.

You are also right but the problem comes when the user fills all the required data of the main form and then fills the data of sub form also but instead of clicking on the save button, he closes the form directly. By then the data of the main form has been saved in the table. To undo or delete that data from the table, what coding should be done in form unload event?

[Welshgasman]

If you let Access save the subform data by being bound, then all this would be moot?
Make the forms unbound, then you can do what you like with the data. That seems to be what you want to do and avoid using Access features. Perhaps try Foxpro, that would do what you want, as there you scatter data to memvars for the forms, then gather to save the data.

[mike60smart]

Minty's method is the easiest to implement.

[Micron]

Not showing the subform solves the issue of child records that need to be deleted since they would not even get created but it doesn't handle deletion of a main form record. That record can be saved even by clicking on the subform.
For this I would probably use staging (temp) tables; 1 for main form, 1 for subform. If the user interacts with the form as desired (save button) write the record to the main tables and delete the temp records. If not, just delete. This should handle new or editing of existing records. I'd use a "mode" variable or OpenArgs to determine if the forms were opened for new or editing of records and code accordingly. If new, run append sql. If edit, run update sql.

EDIT - in a multi user environment with split db's, the temp tables should be in the front end, otherwise you'd delete other user records when clearing the temp records. Or you''d have to use user criteria to delete the proper record(s). I have used this approach for a special case in the past and it served us well.

[NoellaG]

If you are comfortable with VBA you can create a 3-layer app:

layer 1: tables
layer 2: class modules for each data class, properties = table fields (could include some lookup or calculated fields), methods = addnew/update/delete
layer 3: unbound forms

a bit more preparation work, but once you have the class modules, it works as a charm.

[Minty]

You are also right but the problem comes when the user fills all the required data of the main form and then fills the data of sub form also but instead of clicking on the save button, he closes the form directly. By then the data of the main form has been saved in the table. To undo or delete that data from the table, what coding should be done in form unload event?

You prevent them from closing the form by any means other than the save or cancel button.
Make it a modal dialog and remove the close button, and disable the right click shortcut menus.
Cancel button deletes the record, and the child record(s) if it is already saved.

The only other way out then is to use the task manager to close Access, if they do that at that point you are authorised to shoot the employee, and get a new one that follows the rules?

[deepaksharma]

The only other way out then is to use the task manager to close Access, if they do that at that point you are authorised to shoot the employee, and get a new one that follows the rules?

very funny

[Welshgasman]

@Minty
Would surrounding everything in a transaction do it?

[Edgar]

when the user fills all the required data of the main form and then fills the data of sub form also but instead of clicking on the save button, he closes the form directly. By then the data of the main form has been saved in the table. To undo or delete that data from the table, what coding should be done in form unload event?

Use another workflow. Create a new form dedicated for making, changing, and deleting PARENT records. Keep your current form just for working with child records. This makes things clear for users and keeps things simple. You'll find it easier to build and use two separate forms. People will know if they want to edit child records, they use one form, and if they want to edit parent records, they use another. This is better than having one form that does everything. Does that make sense? Avoid complicating your app, you will need to maintain this after you make it.

If you insist in doing what you want to do, it's best that you post a sample here. You're not being completely clear on what your intention is and everyone is going to just guess, as you can see. This will annoy you lots, so it's best that you post a sample explaining what you have and what you expect.