Data undo problem

[deepaksharma]

I have a main form which has 1 sub form.


When I open an old record in this form for viewing or editing or open a new record, the data should not be auto saved, so I have coded to delete the current data in the form unload event ( If the user does not click on the save button, the form data will be deleted)
Now the problem arises that if the user opens an old record and closes the form without clicking on the save button, then that record gets deleted.
I want that the data should not be deleted and it should be undo or the record set of the main table which is linked to the sub table should be undo. What coding can I use for this?
My coding as under for (current form, unload form and save button)

Option Compare Database
Private ASAVED As Boolean
Dim DB As DAO.Database

Private Sub Form_Unload(Cancel As Integer)
Set DB = CurrentDb
Set RST = DB.OpenRecordset("main_table")

If ASAVED = False Then

RST.MoveFirst
Do Until RST.EOF
If RST.Fields(1) = Me.ID Then
RST.Delete
Exit Sub
End If
RST.MoveNext
Loop
End If
End Sub


Private Sub Form_Current()
ASAVED = False
End Sub

Private Sub SAVE_Click()
ASAVED = True
DoCmd.RunCommand (acCmdRecordsGoToNew)

End Sub

[Welshgasman]

If you do not want the data amended (after all it already exists) then check for Me.Dirty and then use Undo if record is Dirty. Or set AllowEdits = False.

Deleting data, like you are doing is just wrong.

Learn how Access works, and work with it, rather than trying to fight it.

[deepaksharma]

If you do not want the data amended (after all it already exists) then check for Me.Dirty and then use Undo if record is Dirty. Or set AllowEdits = False.

Deleting data, like you are doing is just wrong.

Learn how Access works, and work with it, rather than trying to fight it.

I understood your point but when work is done in both main form and sub form, then undo command does not work because as soon as the user goes from main form to sub form, the data of the main form is saved in the table. which cannot be undo.

[Welshgasman]

You can issue an Undo in a form event like LostFocus or Deactivate?
Google for form events sequence.

[Minty]

This makes no sense to me.

If you know you are opening the form on what should be a locked record (which by your own description you do), then simply disable edits, or lock all the controls you don't want them to edit.
You can simply unlock those controls, if they open the form to add a new record, or click on a command button to add a new record. As soon as that new record is saved you relock everything , if that's what is supposed to happen.

Allowing the changes in the first place is causing all your difficulties, so don't allow them!

[deepaksharma]

This makes no sense to me.

If you know you are opening the form on what should be a locked record (which by your own description you do), then simply disable edits, or lock all the controls you don't want them to edit.
You can simply unlock those controls, if they open the form to add a new record, or click on a command button to add a new record. As soon as that new record is saved you relock everything , if that's what is supposed to happen.

Allowing the changes in the first place is causing all your difficulties, so don't allow them!

I agree with you, if the user has to add a new record, then he has to click on the Add Record button, which will unlock all the locks, then he can easily add new data, but if he closes the form while entering the data. then the record will be auto saved at that time, how to stop it?

[Welshgasman]

I agree with you, if the user has to add a new record, then he has to click on the Add Record button, which will unlock all the locks, then he can easily add new data, but if he closes the form while entering the data. then the record will be auto saved at that time, how to stop it?

It is up to you to validate what has been entered and what is required and what is not.
You use the BeforeUpdate event of the form for that. It has a cancel option.

Watch these videos.
https://www.access-programmers.co.uk...-and-2.324342/

[deepaksharma]

It is up to you to validate what has been entered and what is required and what is not.
You use the BeforeUpdate event of the form for that. It has a cancel option.

Watch these videos.
https://www.access-programmers.co.uk...-and-2.324342/

You are also right, I had also tried making a program in which the text boxes of the main form were placed in the validation rules before update event, but the problem came when the user went to the sub form, the data of the main form was save without filling the sub form. It used to be auto saved.

[Minty]

The Before Update Form event will capture that as it will attempt to save when you move to the subform and fire.

Just because you have cancelled the save, doesn't mean you can't put up a suitable message telling the user what they must fill out before using the sub form.
If they then fill it out, then the subform will become available, as the before update event will "let them through" by saving the main forms data.

If it is a new record, you can't enter data on the subform without saving the parent record anyway, as there would be no Primary key field data to save it against.

If it is an existing record then it should already have the key data present.

[deepaksharma]

The Before Update Form event will capture that as it will attempt to save when you move to the subform and fire.

Just because you have cancelled the save, doesn't mean you can't put up a suitable message telling the user what they must fill out before using the sub form.
If they then fill it out, then the subform will become available, as the before update event will "let them through" by saving the main forms data.

If it is a new record, you can't enter data on the subform without saving the parent record anyway, as there would be no Primary key field data to save it against.

If it is an existing record then it should already have the key data present.

I have put this coding in the before update event. while feeding a new record, if the user closes the form without filling the txt2 field, then it asks the user to answer yes or no. If the user answers in no, then on the txt2 field Instead of being directly focused. this message is being shown.
Where am I going wrong?

Private Sub Form_BeforeUpdate(Cancel As Integer)

Set DB = CurrentDb
Set RST = DB.OpenRecordset("GREY_OFFER")

If (IsNull(Me![txt2].Value)) Or (Me.txt2.Value = "") Then

Dim answer As Integer
answer = MsgBox("Do you Wish to exit program without save data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
If answer = vbNo Then

Me.txt2.SetFocus
Cancel = True

Else

DoCmd.RunCommand acCmdUndo

Exit Sub
End If
End If

Me.OfferNumber = Nz(DMax("offernumber", "grey_offer"), 0) + 1
End Sub

[Minty]

Firstly, you are opening a recordset for no reason, you never use it.

What message are you seeing, you haven't attached anything?

I've indented your code, and removed the recordset, so you can better see what's happening, and used code tags.

    
    Dim answer As Integer


    If IsNull(Me.[txt2]) Or Me.txt2 & "" = "" Then
        answer = MsgBox("Do you Wish to exit program without save data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
        If answer = vbNo Then
            Me.txt2.SetFocus
            Cancel = True
        Else
            DoCmd.RunCommand acCmdUndo
            Exit Sub
        End If
    End If


    Me.OfferNumber = Nz(DMax("offernumber", "grey_offer"), 0) + 1

[deepaksharma]

Firstly, you are opening a recordset for no reason, you never use it.

What message are you seeing, you haven't attached anything?

I've indented your code, and removed the recordset, so you can better see what's happening, and used code tags.

    
    Dim answer As Integer


    If IsNull(Me.[txt2]) Or Me.txt2 & "" = "" Then
        answer = MsgBox("Do you Wish to exit program without save data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
        If answer = vbNo Then
            Me.txt2.SetFocus
            Cancel = True
        Else
            DoCmd.RunCommand acCmdUndo
            Exit Sub
        End If
    End If


    Me.OfferNumber = Nz(DMax("offernumber", "grey_offer"), 0) + 1

same message is coming even after deleting the record set.

[Minty]

This code is on the Main form Before update event correct?
And the record isn't already saved by some other method?

I would probably have used

Me.Undo rather than DoCmd.RunCommand acCmdUndo

as it ensures you are referring to the forms current record.

You might have to post up the database for us to see the issue.

EDIT : have you tried just undoing the record and closing the form:

    Dim answer As Integer


    If IsNull(Me.[txt2]) Or Me.txt2 & "" = "" Then
        answer = MsgBox("Do you Wish to exit program without save data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
        If answer = vbNo Then
            Me.txt2.SetFocus
            Cancel = True
        Else
            Me.Undo
            DoCmd.Close acForm, Me.Name
            Exit Sub
        End If
    End If

[deepaksharma]

This code is on the Main form Before update event correct?
And the record isn't already saved by some other method?

I would probably have used

Me.Undo rather than DoCmd.RunCommand acCmdUndo

as it ensures you are referring to the forms current record.

You might have to post up the database for us to see the issue.

EDIT : have you tried just undoing the record and closing the form:

    Dim answer As Integer


    If IsNull(Me.[txt2]) Or Me.txt2 & "" = "" Then
        answer = MsgBox("Do you Wish to exit program without save data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
        If answer = vbNo Then
            Me.txt2.SetFocus
            Cancel = True
        Else
            Me.Undo
            DoCmd.Close acForm, Me.Name
            Exit Sub
        End If
    End If

All the points mentioned by you are correct, still while closing the form (during offer entry) an error box is coming on selecting "no". please check the attachment.....

[Minty]

The problem is that you are setting the offer date in code, which instantly dirties the record.
There are a host of other things that aren't correct but that is causing most of your issues. Just put in Date() in the default value of the control, that doesn't dirty the record when you go to a new one.

Also, please please please give your controls meaningful names. txt1, txt 2 txt133 mean absolutely nothing, and make debugging a nightmare.