Form with save/discard type buttons

[nicksource]

I have a form to insert new data but as soon as I type anything into it, it immediately gets entered into the db table. How can I make a button so it only gets entered into the table once I click the said button?

On a related note, how do I make certain form fields required?

 

[nicksource]

This can't be that hard, can it?

 

[boblarson]

A few things:

1. Data does not get saved immediately upon entering it in the the text box, or combos, etc. It only gets saved if you either move to a new record, close the form, or specifically save it.

2. The key is, if you don't want to save it unless the button has been clicked is to cancel the update with code similar to this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Nz(Me.txtSampleField,””) & “”) = 0 Then
   If MsgBox("You must enter a value before continuing." & _
“Would you like to continue with this record?”, vbYesNo + vbQuestion, "Sample Company, Inc.") = vbYes Then
        Cancel = True
        Me.txtSampleField.SetFocus
   Else
         Cancel = True
         Me.Undo
   End If
End If   
End Sub

But the trick to that code is to add a boolean flag which can let you know when the button has been clicked. So you would add this to the General Declarations section of your form:

Dim blnSave As Boolean

You would then put this in the click event of the button:

If Me.Dirty Then
   blnSave = True
   Me.Dirty = False
End If

then you change the code I gave above to this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If blnSave Then
   If Len(Nz(Me.txtSampleField,””) & “”) = 0 Then
      If MsgBox("You must enter a value before continuing." & _
“Would you like to continue with this record?”, vbYesNo + vbQuestion, "Sample Company, Inc.") = vbYes Then
         Cancel = True
         Me.txtSampleField.SetFocus
      Else
         Cancel = True
         Me.Undo
       End If
    End If
Else
    Cancel = True
    Me.Undo
End If   
End Sub

You can change things to check for valid entries in each required field before saving.

 

[nicksource]

Phew, that looks confusing, could you spare a bit of time to explain what each part does or refer to me some material?

I much appreciate your input Bob, thanks.

 

[boblarson]

Private Sub Form_BeforeUpdate(Cancel As Integer)

That part is the event header for the form's Before Update event. It is managed by Access so you don't need to type that in, just select it. The Cancel As Integer part allows you to set it to anything but 0 (which is false) and therefore cancel the update from happening.

If blnSave Then

blnSave is the boolean value that we set from the button's click event so that we know that we want to save the record. If it is false, then we don't. When using booleans, you don't have to specify = True when checking it as that is considered the default, so-to-speak. So, you would need to use If blnSave = False Then, but if you use If blnSave Then then that is considered the same as If blnSave = True Then.

   If Len(Nz(Me.txtSampleField,””) & “”) = 0 Then

To check for null and zero length strings, you can combine the test by checking the length. By using the NZ function we are able to keep an error from occuring if it is null and by adding the zero length string on the end it allows us to check for a zero length string too.

      If MsgBox("You must enter a value before continuing." & _
“Would you like to continue with this record?”, vbYesNo + vbQuestion, "Sample Company, Inc.") = vbYes Then

Checks the message box value returned and if it is YES (vbYes constant) then it goes on.

         Cancel = True
         Me.txtSampleField.SetFocus

this part would cancel the update if they want to continue and then you set the focus to the text box missing the data.

      Else
         Cancel = True
         Me.Undo
       End If

If they pick NO then it will cancel the update and go undo the record and reset back to prior to any changes.

    End If
Else
    Cancel = True
    Me.Undo
End If   
End Sub

And this last part has to do with the very first check to see if we wanted to save the record. If it gets here by any other means (for example the form is closed) then the record update is canceled and all changes undone. Does that help?

 

[missinglinq]

Private Sub Form_BeforeUpdate(Cancel As Integer)

Not only do you not need to type this in by hand, but you really shouldn't type it in by hand, as mistakes are liable to occur! You also need to understand that you cannot go modifying this code, such as adding arguments that Access didn't place here. I'm pointing this out because I've seen six posts today, on other forums, where this had been done and caused beaucoup errors!