only save record when clicking on a button

[mar7632]

Hi everyone,

I have three buttons in my form:

One for Go to new record, one for exit the form and one for save the record I am.

It is supposed that the record will be saved only when I click on the "save record" record otherwise it wont make any changes.

When I click on go to new record and exit the form without save it, instead of "undo" the record it remains and it is kept in my table and that is not my purpose.



Here is the code I am using for the three buttons:

Private Sub CerrarForm_Click() 'close form
Dim resp As Integer
If Me.chkGuardado.Value = False Then GoTo Salida 'I have a checkbox when any change is made it will be activated
resp = MsgBox("Se perderán los datos no guardados. ¿Quieres continuar?", vbInformation + vbYesNo, "AVISO")
If resp = vbNo Then Exit Sub
If resp = vbNo Then Exit Sub
Salida:
Me.Undo
DoCmd.Close acForm, Me.Name
End Sub




Private Sub cmdGuardar_Click() 'save form

DoCmd.RunCommand acCmdSaveRecord
Me.chkGuardado.Value = True
Const cstrPrompt As String = "¿Quieres guardar el registro actual?"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo, "Pregunta") = vbYes Then
If Me.Dirty Then
    Me.Dirty = False       
 End If
MsgBox 
Me.cmdMandarCorreo.Enabled = True 
End If

End Sub

Private Sub agregarRegistro_Click() 'Go to new record

Me.Undo
If MsgBox("¿Quieres agregar un nuevo registro?", vbQuestion + vbYesNo, "Pregunta") = vbYes Then
End If
Me.Undo
DoCmd.GoToRecord , , acNewRec
End Sub

Are there something wrong with the code?

Thank you in advance.

[Minty]

The only sure fire way to catch a save point for a record is in the Forms BeforeUpdate event.
Any other method can and will be by-passed by an and user.

Try your validation code in that event, and issue an Cancel = True and Me.Undo in the event of not wanting to save the record.

The only other ways, both are complicated and involve heaps more code, are to use an unbound form or create a transactional form.

[mar7632]

Thank you for answering,

Could you provide an example, please?

I tried the following thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If (MsgBox("Desea guardar los datos", vbOKCancel, "Datos introducidos y no guardados") = vbCancel) Then
Cancel = True
Me.Undo
End If
End Sub

It seems that it works properly, but I´ll try some tests...

[Minty]

There isn't a lot to program.

The form Before_Update event fires if data has been changed or added to the forms current record, before the record is saved.
So it is the ideal place to do data verification, or cancel the saving of the record. For instance;

Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    If IsNull(Me.YourControlThatCantBeNull) Then
        MsgBox ("This record cannot be saved without a valid part number"), vbCritical, "Cannot Save!"
        Me.Undo
        Cancel = True
    End If
    
    
End Sub

[mar7632]

What do you mean in this case?

"If IsNull(Me.YourControlThatCantBeNull) Then"

[Minty]

I was using a generic control name.
Lets say your record included a customer account number. Saving the record without that set, would make the record meaningless.
If your control for the customer record was called txtCustAccNo you would check it wasn't null before trying to save the record, or leaving the form.

If IsNull(Me.txtCustAccNo) Then ...

[mar7632]

Ok, I think that I understand what you are saying. This can be used by using a check box?

I have been testing the code and at first it works fine, thank you so much for your help and comments Minty

[Minty]

This can be used by using a check box?

You could use a check box as a flag, but by it's nature the before update event has already kept track of any underlying changes, so I'm not sure if it's of benefit.

Unless you want to override the validation, doing something like this at the start of the before update event would bypass your normal checks.

If Me.CheckBox is True Then Exit Sub

[mar7632]

Hi again,

I have followed your advise and I tried to stablish the code you provided

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.fecha_entDT) Then
MsgBox ("Este registro no se va a guardar si no se rellena el campo FECHA ENTRADA EN DT"), vbCritical, "¡NO SE GUARDARÁ!"
Me.Undo
Cancel = True
End If
End Sub

It works fine. However, if I dont write anything in the field called [fecha_entDT] and save it using the button an error comes up, pointing me a problem regarding this part of the code:

Private Sub cmdGuardar_Click()

DoCmd.RunCommand acCmdSaveRecord
Me.chkGuardado.Value = True
Const cstrPrompt As String = "¿Quieres guardar el registro actual?"
If MsgBox(cstrPrompt, vbQuestion + vbYesNo, "Pregunta") = vbYes Then
If Me.Dirty Then
    Me.Dirty = False        ' Save the changes
 End If
MsgBox "Se han guardado los cambios"
Me.cmdMandarCorreo.Enabled = True 'NUEVO 06/03/2019
End If

End Sub

How can I solve this problem?

[Minty]

That is because your before update code is stopping you save the record, without anything in that field.

Remove that line, as it is redundant. If you want to override the before update event check then you'll need to amend you code as follows.

Private Sub cmdGuardar_Click()


    Const cstrPrompt As String = "¿Quieres guardar el registro actual?"
    If MsgBox(cstrPrompt, vbQuestion + vbYesNo, "Pregunta") = vbYes Then
        Me.chkGuardado = True     'set this to override the validation code in the before update
        If Me.Dirty Then
            Me.Dirty = False        ' Now Save the changes, which will work
        End If
        MsgBox "Se han guardado los cambios"
        Me.cmdMandarCorreo.Enabled = True 'NUEVO 06/03/2019
    End If


End Sub
---------------------------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)


    If IsNull(Me.fecha_entDT) Then
        If Me.chkGuardado Then Exit Sub     ' If this is set to true ignore the validation
        MsgBox ("Este registro no se va a guardar si no se rellena el campo FECHA ENTRADA EN DT"), vbCritical, "¡NO SE GUARDARÁ!"
        Me.Undo
        Cancel = True
    End If
End Sub

[mar7632]

Hello, I have been doing some tests. I think that the best way to optimize the code is mixing both codes:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.fecha_entDT) Then
MsgBox ("Este registro no se va a guardar si no se rellena el campo FECHA ENTRADA EN DT"), vbCritical, "¡NO SE GUARDARÁ!"
Cancel = True
 Me.Undo

If (MsgBox("Desea guardar los datos", vbOKCancel, "Datos introducidos y no guardados") = vbCancel) Then
Cancel = True
Me.Undo


End If

Cancel = True
End If
End Sub

I want to set this two commands, but I do not know yet if this is going to work.

[Minty]

Indenting your code makes it easier to see the steps / logic.
At the moment if fecha_entDT is null you cancel the save regardless of your message boxes. And you are trying to cancel it twice or three times...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.fecha_entDT) Then
        MsgBox ("Este registro no se va a guardar si no se rellena el campo FECHA ENTRADA EN DT"), vbCritical, "¡NO SE GUARDARÁ!"
        Cancel = True    'This will always be called if fecha_entDT is null
        Me.Undo
        If (MsgBox("Desea guardar los datos", vbOKCancel, "Datos introducidos y no guardados") = vbCancel) Then   'This is pointless as you have already canceled and undone the changes before you ask this question
            Cancel = True
            Me.Undo
        End If
        Cancel = True     'This will always be called if fecha_entDT is null
    End If
End Sub

Take as step back and tell us what validation you are trying to achieve, and what exceptions you would like to make.
You are currently tripping yourself up with your logic.

[mar7632]

Thank you Minty,

What I want to achieve is to only save a record when the save button (cmdGuardar) is clicked with validation messages. Also, If a new record is introduced and not saved then undo all the changes, the same when I click on exit form (cerrarForm).
However, I had a button which refreshes the form in order to update one field called codigoRFQ (which has the format that we talked in this post (https://www.accessforums.net/showthr...404#post425404)) because when I introduce data this field does not update properly. The code for this:

Private Sub btnActualizar_Click()
Forms("Faa_DatosRecibidos")!codigoRFQ = Me.txtCodigoRFQtxt
Me.Refresh
End Sub

I have also some comb boxes which update the form and the code for Form_BeforeUpdate is triggered.

[ArviLaanemets]

Here is an example of BeforeUpdate event for a form in one of my apps.

Private Sub Form_BeforeUpdate(Cancel As Integer)
'   fChemReg.BeforeUpdate
    ' Seting error processing
    On Error GoTo ExitNow
    
    Dim strCurrCatalogue As String
    Dim strCurrChemCode As String
    Dim strCurrSiteCode As String
    Dim intFound As Integer '3 - Catalogue and ChemCode and SiteCode; 2 - Catalogue and ChemCode, 1 - Catalogue or ChemCode or neither
    
    strCurrCatalogue = Me.cbbCR_Catalogue.Value
    strCurrChemCode = Me.cbbCR_ChemCode.Value
    strCurrSiteCode = GetGlobal("gbl_SiteCode")
    
    intFound = DCount("SiteCode", "D_ChemSite", "Catalogue = '" & strCurrCatalogue & "' AND ChemCode = '" & strCurrChemCode & "' AND SiteCode = '" & strCurrSiteCode & "'")
    If intFound = 0 Then
        intFound = DCount("SiteCode", "D_ChemSite", "Catalogue = '" & strCurrCatalogue & "' AND ChemCode = '" & strCurrChemCode & "'")
        If intFound = 0 Then
            intFound = 1 ' this combimnation of catalogue and chemical code was not found in any of sites
        Else
            intFound = 2 ' this combimnation of catalogue and chemical code was found at least in one of another sites
        End If
    Else
        intFound = 3 ' this combimnation of catalogue and chemical does exist for this site
    End If
    
    If (Nz(Me.cbbCR_Catalogue.Value, "") = "" Or Nz(Me.cbbCR_ChemCode.Value, "") = "") Then
        MsgBox "Discard the new record (Esc), or fill both Catalogue and Chemical Code to proceed!"
        Cancel = True
    ElseIf intFound = 3 And Me.NewRecord = False Then
    ElseIf intFound = 3 And Me.NewRecord = True Then
        MsgBox "Such chemical is registered for this site! Discard the new record (Esc), or register another chemical!"
        Cancel = True
    ElseIf intFound = 2 Then
        MsgBox "Such chemical was registered elsewhere! It will be linked with your site now!"
        CurrentDb.Execute "INSERT INTO D_ChemSite (SiteCode, Catalogue, ChemCode) VALUES ('" & strCurrSiteCode & "', '" & strCurrCatalogue & "', '" & strCurrChemCode & "')"
    Else
        MsgBox "A new chemical will be registered!"
        CurrentDb.Execute "INSERT INTO D_ChemSite (SiteCode, Catalogue, ChemCode) VALUES ('" & strCurrSiteCode & "', '" & strCurrCatalogue & "', '" & strCurrChemCode & "')"
    End If
ExitNow:
End Sub

The code row "Cancel = True" stops saving and returns the form to the state it had before saving was attempted. Then user can:
a) make corrections and try to save again;
b) press "Esc". This discards the new record. Record pointer is set to last record in recordset.

All messaging is made in BeforeUpdate event. This is most reasonable, because mostly the checking code is same, and there is no reason to make this twice.

About saving only when button is pressed:
1. Define a global variable, e.g. booSaving, with initial value False;
2. When button is pressed, booSaving is set True, and the command to update the record is given. After that BeforeUpdate event takes over;
3. The 1st step for BeforeUpdate event is to check booSaving. When it is False, then Cancel, otherwise proceed;
4. Whatever is final outcome of BeforeUpdate event, booSaving must be set False finally.