Vba code "Only for experts"

[journeyman]

Good morning!!
That's why I wrote it only for experts.

In my experience, you can do almost anything you want in MS Access (Data and Office related). Access is a very easy tool to learn with a huge community.

The key limitations are your imagination and your desire to achieve your goals.

Expertise is not required (Access is a very easy tool to use) - although I would recommend a research effort first for a direction before lazily presenting a question to a forum.

Quick n dirty VBA

DoCmd.RunCommand acCmdDeleteRecord

or

strSQL = "DELETE FROM tblMyTable  WHERE ID = 3"
DoCmd.RunSQL strSQL

[deepaksharma]

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.

For example, I have created a sample program in which there is a main form in which teacher related information is filled and there is a sub form named Experience in which the working experience of the teacher is entered.
I want that if the user has filled all the required fields of main form and sub form but he has closed the form without clicking on save button then all the data in the table should get undo. If the user was entering a new record then it should be deleted and if he had opened the old record for editing then that record should be undo.
I find Micron's method more correct but I am not able to understand how to use Micron's method. If Micron changes this sample file and puts the correct file in front of us, then we all can learn a lot.

[Minty]

@Minty
Would surrounding everything in a transaction do it?

Possibly, but it adds another layer of complication.
It's probably easier to go down Micron's route of local temp tables, the only issue there is possible bloat, but if were only talking a few hundreds of records, that is inconsequential, or can be solved with a side DB.

[Micron]

I'll explain it the best I can but it has been a while. I don't have the time to take it on right now.

- Create 2 tables that mirror the main and sub tables.
- Give user choice to create new or edit or review (combobox?). When choice is made append main and sub records to temp tables, pass choice to OpenArgs ("new" or "edit" or "view") to form. Or use read only instead of view.
- In form load event code set properties according to the choice. If view, lock records using one of the methods available to you. Easiest might be to set recordsource to a query using snapshot type of recordset, else change all Allow properties accordingly
https://learn.microsoft.com/en-us/of...orm.allowedits
- If new, don't append to the tables. Fill out the form as usual.
- If edit, append temp records based on which main record user indicated they want to edit (same as for view).
- In form close event, a variable would be set by your "Save & Close" button. That button should not even be enabled if the form is not dirty. Only provide for Close. If they click close you can prompt to save changes if desired and applicable (i.e. not for view only) and form is dirty. Variable contains your clue to save changes or not:

- if not (because of discarding changes or read only), delete records from temp tables, close form. Done.
- if yes and new, append records to main tables, delete temp records, close form
- if yes and edit, run update sql on main tables using temp field values, delete temp records, close form. Done

You could even do this for forms separately i.e allow main but not sub record edits or vice versa by referring to main form open args

Cautions -
- I never had an error in updating or appending. Might want to look into transactions though, just in case.
- In a multi user environment should work ok if temp tables are in front end. I wouldn't worry about FE bloat but one could enforce a CR if db reaches a certain size on closing.
- if multi user and db is not split and you just won't do that, you need to id records based on user info, otherwise you delete everyone's records.

[deepaksharma]

I'll explain it the best I can but it has been a while. I don't have the time to take it on right now.

- Create 2 tables that mirror the main and sub tables.
- Give user choice to create new or edit or review (combobox?). When choice is made append main and sub records to temp tables, pass choice to OpenArgs ("new" or "edit" or "view") to form. Or use read only instead of view.
- In form load event code set properties according to the choice. If view, lock records using one of the methods available to you. Easiest might be to set recordsource to a query using snapshot type of recordset, else change all Allow properties accordingly
https://learn.microsoft.com/en-us/of...orm.allowedits
- If new, don't append to the tables. Fill out the form as usual.
- If edit, append temp records based on which main record user indicated they want to edit (same as for view).
- In form close event, a variable would be set by your "Save & Close" button. That button should not even be enabled if the form is not dirty. Only provide for Close. If they click close you can prompt to save changes if desired and applicable (i.e. not for view only) and form is dirty. Variable contains your clue to save changes or not:

- if not (because of discarding changes or read only), delete records from temp tables, close form. Done.
- if yes and new, append records to main tables, delete temp records, close form
- if yes and edit, run update sql on main tables using temp field values, delete temp records, close form. Done

You could even do this for forms separately i.e allow main but not sub record edits or vice versa by referring to main form open args

Cautions -
- I never had an error in updating or appending. Might want to look into transactions though, just in case.
- In a multi user environment should work ok if temp tables are in front end. I wouldn't worry about FE bloat but one could enforce a CR if db reaches a certain size on closing.
- if multi user and db is not split and you just won't do that, you need to id records based on user info, otherwise you delete everyone's records.

Thank you very much for understanding with so much effort but I want that whenever you get time, please modify and upload this above sample file so that it becomes easier for us to understand it practically.

[deepaksharma]

In my experience, you can do almost anything you want in MS Access (Data and Office related). Access is a very easy tool to learn with a huge community.

The key limitations are your imagination and your desire to achieve your goals.

Expertise is not required (Access is a very easy tool to use) - although I would recommend a research effort first for a direction before lazily presenting a question to a forum.

Quick n dirty VBA

DoCmd.RunCommand acCmdDeleteRecord

or

strSQL = "DELETE FROM tblMyTable  WHERE ID = 3"
DoCmd.RunSQL strSQL

Hello journeyman

Thanks for help
The SQL statement you have suggested deletes the data, it is good, but if an old record is opened for editing in the form and the form is closed without save button, will that statement delete that record also? There is no SQL statement that would undo the record instead of deleting it.

[Micron]

I want that whenever you get time, please modify and upload this above sample file so that it becomes easier for us to understand it practically.

I bet you do want that. YOU need to put in some work. If you get stuck, someone can help. There's a lot more I can say about your approach to forum assistance but I'll leave it at that.

[journeyman]

The SQL statement you have suggested deletes the data, it is good, but if an old record is opened for editing in the form and the form is closed without save button, will that statement delete that record also? There is no SQL statement that would undo the record instead of deleting it.

Perhaps a little advice - Don't delete the record. instead try something like this:

1. Include a Boolean field in your table that indicates if the record is to be deleted or not.
2. Create a query to display records where the Boolean value is false.
3. Use the query as the form recordset.
4. Create a button where the click event marks the selected record Boolean field as deleted. (Update query)
5. In the click event requery the form - the record ill vanish from view. But it's not actually gone! Your users will not know the difference.

Later you can review the table at your leisure to determine if the record removal is warranted or not.

You can easily undo the record deletion by simply marking the field as false.

You should consider a method like this to prevent accidental deletion - and you don't have to fluff about with undoing or losing data.

Hope that helps

[Welshgasman]

@journeyman
O/P is trying to emulate a transaction, not delete records per se.

Perhaps a little advice - Don't delete the record. instead try something like this:

1. Include a Boolean field in your table that indicates if the record is to be deleted or not.
2. Create a query to display records where the Boolean value is false.
3. Use the query as the form recordset.
4. Create a button where the click event marks the selected record Boolean field as deleted. (Update query)
5. In the click event requery the form - the record ill vanish from view. But it's not actually gone! Your users will not know the difference.

Later you can review the table at your leisure to determine if the record removal is warranted or not.

You can easily undo the record deletion by simply marking the field as false.

You should consider a method like this to prevent accidental deletion - and you don't have to fluff about with undoing or losing data.

Hope that helps

[deepaksharma]

Perhaps a little advice - Don't delete the record. instead try something like this:

1. Include a Boolean field in your table that indicates if the record is to be deleted or not.
2. Create a query to display records where the Boolean value is false.
3. Use the query as the form recordset.
4. Create a button where the click event marks the selected record Boolean field as deleted. (Update query)
5. In the click event requery the form - the record ill vanish from view. But it's not actually gone! Your users will not know the difference.

Later you can review the table at your leisure to determine if the record removal is warranted or not.

You can easily undo the record deletion by simply marking the field as false.

You should consider a method like this to prevent accidental deletion - and you don't have to fluff about with undoing or losing data.

Hope that helps

The method suggested by you is good but it works on two conditions Yes and No. Whether to delete the data or not, but if any old data is opened for change in the form and any change is made in it, then this method will not be able to stop it, it will be updated in the table.
Suppose the user opens the old record in the form for editing and changes the name in its name field and closes the form without clicking the button, then the name in the table will have been changed, this method will not be able to work.
Don't be angry with me but the solution you have suggested earlier is quite good if you put the SQL code suggested by him in the form unload event of the form and code it with if condition that if the save button is not clicked. Then the record set of this field of the table (which matches the me.id text box of the form) will be set to undo. If there is a new record then the deletion will be cleared and if any old record has been opened for editing then the deletion will be done.
Now the problem with this coding is that the coding suggested by you is not for undo but for deleting the data.
Can you suggest the correct coding to undo the data by changing this coding?
Thanks in advance

strSQL = "DELETE FROM tblMyTable WHERE ID = me.id.value"

[Minty]

I think you are trying to program out a lack of basic training skills, and it just isn't possible with a bound form.

To match you exact requirements you will have to adopt a unbound approach, either with local FE temporary tables (as per Micron's approach), or a completely unbound set of forms, with all the programming overhead that entails.
There is no other sensible solution. Bear in mind with either approach there are risks, you need to be cognisant of the possibility of two people trying to edit the same set of data and either prevent it (more programming) or handle it by some other method.

So dive in and make a start on one approach or the other, but be aware that training your users is probably a much simpler solution.

[deepaksharma]

I think you are trying to program out a lack of basic training skills, and it just isn't possible with a bound form.

To match you exact requirements you will have to adopt a unbound approach, either with local FE temporary tables (as per Micron's approach), or a completely unbound set of forms, with all the programming overhead that entails.
There is no other sensible solution. Bear in mind with either approach there are risks, you need to be cognisant of the possibility of two people trying to edit the same set of data and either prevent it (more programming) or handle it by some other method.

So dive in and make a start on one approach or the other, but be aware that training your users is probably a much simpler solution.

You are right, many experts came here and worked very hard and expressed their views but I could not get the perfect solution as I wanted from anyone. The only thing I have learned from them and you is that doing this would be very complicated and unsafe and hence now I would prefer to work on the method suggested by you earlier. Thank you

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.

[journeyman]

Can you suggest the correct coding to undo the data by changing this coding?

The solutions I offered are explicitly for record deletion.

You can Undo using the form's BeforeUpdate event. But!! This is very limited.

Something like this:

Private Sub TxtField_BeforeUpdate(Cancel As Integer)
    If MsgBox("Are you sure you want to modify this field data?", vbExclamation + vbYesNo, "Save Record") = vbNo Then
        Cancel = True
        Me.Undo
    End If
End Sub

You can use it only when leaving the field.

If you're seriously invested in undoing a record, you might need to consider a temp table that emulates the table you're working with. Whenever you make a change to a record, save that record to your temp table

The idea is to duplicate your record.... hold on. bear with me.

1. Create a temp table the duplicates the table you're working with
2. Write the current record into the temp table.
Note: that you will only hold a few records at any one time.
3. Anytime the record must be undone, write the data back to your original table.
4. Remember to delete the record in the temp table once the modification has been undo and you no longer need the temp record. (otherwise you'll build up duplicate that clutter up the place).

Note:
CREATE TABLE (SQL) on form load, or create a static table.
If static, remember to include an option to delete the data once you're satisfied the original table is clean.
If created at runtime, consider whether other users need options to access the undo table. If so, consider if the table is local or to be linked.
If the temp table is created at runtime, remember to delete on application close. this way your database only contains relevant data.
You can hold all the records you want, but remember to clear the table or delete it when the application is closed.

Remember that all this is how you think about it. The technicals of it are the easy part.

[deepaksharma]

The solutions I offered are explicitly for record deletion.

You can Undo using the form's BeforeUpdate event. But!! This is very limited.

Something like this:

Private Sub TxtField_BeforeUpdate(Cancel As Integer)
    If MsgBox("Are you sure you want to modify this field data?", vbExclamation + vbYesNo, "Save Record") = vbNo Then
        Cancel = True
        Me.Undo
    End If
End Sub

You can use it only when leaving the field.

If you're seriously invested in undoing a record, you might need to consider a temp table that emulates the table you're working with. Whenever you make a change to a record, save that record to your temp table

The idea is to duplicate your record.... hold on. bear with me.

I had uploaded a sample file earlier, can you please make changes in it and upload it because I understand till before update event but I have never used the temporary table method, if you can make changes in it and upload it. If you give, it will be a great favor from you. I had earlier also requested Micron in this regard.

[Minty]

@Jouneyman - you have picked the wrong event for your code, you need to do all the validation in the FORMS before update event, than you can issue a cancel = true and stop anyone leaving the form.
If you issue an Me.Undo it will undo the whole form.

Your idea of local temporary table(s) has already been discussed at length by two other responders and is almost as difficult to manage in a multiuser environment, when you take into consideration locking records being edited by someone else etc.

This is the second thread by the OP on this subject and he was finally coming round to accepting the limitations of his original approach, you are unfortunately covering old ground.