Sub form coding for stop dirty

[deepaksharma]

I have a form which has a save button and a sub form. When the user opens an old record and changing anything in the sub form And without clicking on the save button, if you close the form then the data of the sub form gets auto saved. so I want to do the coding in the before update event of the sub form.
That if the sub form is dirty and the save button on the main form is not clicked then a dialog box of Yes No should be shown, if the user clicks on Yes then the data gets saved otherwise the data entry gets undone, what will be the coding for this.


Thanks.

[Minty]

You have to put the code for this in the Subform's before update event.
Nothing else will capture it, for the reasons you have stated.

[deepaksharma]

You have to put the code for this in the Subform's before update event.
Nothing else will capture it, for the reasons you have stated.

Can't the sub form's Before Update event be coded to check whether the Save button on the main form has been clicked or not?

[Micron]

This one seems to be the same issue asked over and over. You've been given plenty of answers, including using staging tables. Rather than build these tables yourself and implementing the solution you expected me and/or others to build that for you. People are going to start ignoring your threads if you continue on this path.

Data auto save problem with sub form
https://www.accessforums.net/showthread.php?t=88388

Data undo problem
https://www.accessforums.net/showthread.php?t=88729

Vba code "Only for experts"
https://www.accessforums.net/showthread.php?t=88789

Sub form coding for stop dirty
https://www.accessforums.net/showthread.php?t=88859

[Welshgasman]

Already on my IL.

[deepaksharma]

This one seems to be the same issue asked over and over. You've been given plenty of answers, including using staging tables. Rather than build these tables yourself and implementing the solution you expected me and/or others to build that for you. People are going to start ignoring your threads if you continue on this path.

Data auto save problem with sub form
https://www.accessforums.net/showthread.php?t=88388

Data undo problem
https://www.accessforums.net/showthread.php?t=88729

Vba code "Only for experts"
https://www.accessforums.net/showthread.php?t=88789

Sub form coding for stop dirty
https://www.accessforums.net/showthread.php?t=88859

Looks like you're very upset with me
I have only asked for a code that applies to one condition.

[Micron]

Nope, not upset at all. Just making sure others are aware of the situation in case they want to take it under consideration.
As for "one condition", they all look like the same problem or at least contain elements of the same problem - and they contain viable solutions yet here we still are. When and if you move on from this problem I will probably engage if I can help but I'm not going to repeat here what I and others have said about undoing or preventing form or subform edits.

[Minty]

Can't the sub form's Before Update event be coded to check whether the Save button on the main form has been clicked or not?

I'll give you one more bit of a steer....

NO - You could check If Me.Parent.Dirty = True, BUT that in theory would never be true because as soon as you move to the Subform the parent form will save the underlying record main record.

So, you are back to making sure your main record is correct before allowing access to the sub-form.
And also making sure that you validate the Subform data in a similar manner before leaving it.

I'm going to repeat myself and other posters, you could nail this down with staging tables, or unbound forms, but the added work is substantial.
You'd be much better off to either allow users to edit things and trust they know what they doing as they are paid to do it, or have a supervisor to check the data and correct it.

[Micron]

but the added work is substantial.

Not so much for staging tables. All it takes is code that checks if the button was ever clicked or not when form(s) close. If not, close and run delete query. If yes, close and run either append query for new record or update query for existing record, then delete query to delete temp data. Don't have to bother with form BeforeUpdate events for this at all.

[deepaksharma]

Ok I understand there is no such method.
Now I have done this coding to prevent data editing in BeforeUpdate event of sub form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If Me.Dirty = True Then
    If MsgBox("are you sure to update the record?", vbYesNo) <> vbYes Then
    Me.Undo
    Cancel = True
    End If
    End If

End Sub

This coding is working properly but there are two problems in it.
First of all, the message box is being shown even when the user enters a new record, whereas the message box should be shown only while editing an old record.
The second problem is that as soon as the user makes corrections in one line of the sub form and goes to the next line, the message box is shown, then after completing the next line, the message box is shown again.
Is there any way to show the message box only once when leaving the sub form

[Minty]

First of all, the message box is being shown even when the user enters a new record, whereas the message box should be shown only while editing an old record.

You can check if you are on a new record in the code.

The second problem is that as soon as the user makes corrections in one line of the sub form and goes to the next line, the message box is shown, then after completing the next line, the message box is shown again.
Is there any way to show the message box only once when leaving the sub form

No, because you have changed one of the sub form records, and you want to prevent changes to a record without confirmation of saving those changes.
It's Chicken and Egg, you can't selectively impose rules on record updates, unless you write a lot of code or use staging tables.

[deepaksharma]

Ohh thank you very much for explaining.

=Minty;517938]You can check if you are on a new record in the code.

Can you tell the coding of this.
thank you.

[Minty]

You should learn to google a little bit....

If Me.NewRecord

https://learn.microsoft.com/en-us/of...Form.NewRecord

[deepaksharma]

thank you,

"If Me.NewRecord =true/false" is work fine...

and https://learn.microsoft.com/en-us/of...Form.NewRecord related to access 95-97

[Welshgasman]

thank you,

"If Me.NewRecord =true/false" is work fine...

and https://learn.microsoft.com/en-us/of...Form.NewRecord related to access 95-97

I sincerely doubt that, as it states Article 01/22/2022