save button on form with subform (1 Viewer)

[Ramnik]

Hello everyone,
I have created a form with subform on it having one to many relation .
I have created a save button on main form and edited the before update property of main form to require the click on save button to update the record.
But when i enter the fields on form and click inside subform to enter child values , all the main form data is removed automatically .
what am i doing wrong i just wanted to save only when user clicks the save button. (see the image attached)

Thanks.

 

[bob fitz]

When the focus moves from the main form to the sub form Access will try to save the record on the main form. So, can you show us the code you have in the Before Update Event of the main form.

 

[Ramnik]

Hello Bob ,
This is the code i currently use. There is save button click event and before update event .


Option Compare Database

Private blnSave As Boolean

Private Sub btn_Save_Click()
If MsgBox("Are you sure you want to save?", vbQuestion + vbYesNo, "Save Confirmation") = vbYes Then
blnSave = True
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Else
blnSave = False
End If
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not blnSave Then
Cancel = True
Me.Undo
End If
End Sub

 

[pr2-eugin]

Your code discards all changes without any confirmation.. You need to prompt the user if they wish to discard the changes..

Option Compare Database

Private blnSave As Boolean

Private Sub btn_Save_Click()
    If MsgBox("Are you sure you want to save?", vbQuestion + vbYesNo, "Save Confirmation") = vbYes Then
        blnSave = True
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.GoToRecord , , acNewRec
    Else
        blnSave = False
    End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not blnSave Then
        [COLOR=Blue]If MsgBox("You have not saved the information, do you wish to abondon all changes?, vbQuestion + vbYesNo, "Discard changes") = vbYes Then[/COLOR]
            Cancel = True
            Me.Undo
        [COLOR=Blue]Else
            MsgBox "Please click the save button to proceed to enter information in the Child table", vbInformation[/COLOR]
        [COLOR=Blue]End If[/COLOR]
    End If
End Sub

 

[Ramnik]

Thanks for the suggestion mate .
But what would be the solution if i want to save form and subform at the click of a button . and why my main form refreshes its value to blanks as soon as i click on subform ?
I have turned "data entry" property to "yes".

Thanks.

 

[pr2-eugin]

If you read the reply Bob posted earlier..

When the focus moves from the main form to the sub form Access will try to save the record on the main form.

So unfortunately there is nothing much we will be able to avoid..

Unless you would also close the Form when the Save button is clicked. In that case you can use the FormUnload event. But to discard information would be quiet too late at this point. You might have to run a Delete command.

 

[Pat Hartman]

To control whether or not a record is saved, you must use the form's BeforeUpdate event. Access is designed to save records and it does so under many circumstances. you can drive yourself crazy trying to tie down all the lose ends or you can learn why the BeforeUpdate event is the answer to all your problems. The BeforeUpdate event is the LAST event to run before data is saved and it is NEVER bypassed. It ALWAYS runs prior to a data save REGARDLESS of what triggered the save and it ONLY runs if the current record is dirty. That makes it the place to trap a save and cancel it if you don't want to allow it.

To incorporate a "save" button you need code in a couple of other events. First, define a flag in the header of the form's class module.

Public bSaveRequested as Boolean

Then in the form's Current event (another event you should make your friend) set the variable to false.

bSaveRequested = False

In the click event of the save button. Set the variable to true.

bSaveRequested = True

And finally, in the form's BeforeUpdate event, check the value of the variable.

Dim iAnswer as integer
If bSaveRequested = True Then
Else
    iAnswer = msgbox("Press Yes to save, press No to discard, press cancel to return to form for additional editing.", vbYesNoCancel)
    select Case iAnswer
        Case vbNo
            Cancel = True
            Me.Undo
            Exit Sub
        Case vbCancel
            Cancel = True
            Exit Sub
    End Select
End If
... continue with edits
bSaveRequested = False

The last statement should set the variable back to false or the user will be able to save repeatedly without actually pressing the save button since its state is still true.