0

I want my forms to function like most web site forms: no added/edited records saved until the Submit button is clicked. I want to be able to exit the form without getting validation errors or a new record created. Is there a toggle where it won't attempt to write to a table until it receives a command?

CodeJack
  • 19
  • 2
  • 5
  • You don't mention if using continuous or single form type. By default the bound form will update the underlying recordset when the form closes or you move to another record. You can either make the fields unbound and handle thru VBA or you could use a VBA 'Before Update' event to cancel changes unless your button was clicked. – Wayne G. Dunn Jan 18 '16 at 17:47
  • It is a single form type. Is there a way to disable the default setting to update record set on close/move? – CodeJack Jan 18 '16 at 18:02
  • Your best bet is to look at the following link... it has several different methods of preventing an update from occurring... http://www.access-programmers.co.uk/forums/showthread.php?t=84110 – Wayne G. Dunn Jan 18 '16 at 18:07

1 Answers1

0

Behind your submit button, simply place this code:

If me.Dirty = True then me.dirty = false

You don’t say if you going to have a cancel button, but the code behind the cancel button would look like this:

If me.dirty = true then
   Me.Undo
End if

If you looking to allow the user to close the form, then this code would suffice:

 Option Compare Database
 Option Explicit
 Dim bolOkSave As Boolean

Private Sub cmdSubmit_Click()

   If Me.Dirty = True Then

      Me.Command20.SetFocus
      bolOkSave = True
      Me.Dirty = False
      bolOkSave = False

   End If


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

   If Me.Dirty = True Then
      If bolOkSave = True Then
         Cancel = False
      Else
         ' optional code can be placed here to give user a message
         ' or as this code stands, changes will be dumped
          Me.Undo

    End If
End If

End Sub
Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
  • That's not going to solve the problem. If the user closes the form or navigates to the next (or prior) record, the update will take effect (since he is using a bound form). The code you mention will not fire in either case unless a click is made to invoke the event code. – Wayne G. Dunn Jan 19 '16 at 15:10
  • Well, if the stated goal is a a web like experience, then navigation to another record should not be possible and part of the design criteria. I mean, if the user does not hit submit, and navigates to another record, then what on earth is supposed to occur? In other words, it much assumed that some kind of web like submit button means that the form in question is VERY likely to be closed - my included code thus should have a close form. Adding a submit button to a form that allows navigation is going to be a messy affair - even for a pure web based application. – Albert D. Kallal Jan 20 '16 at 01:51
  • I think the OP's main concern was to 'force' the user to click Submit to avoid any accidental data entry. Equally important, the OP should include code so he can't 'lose' the changes by just closing the form... i.e. ask "You made changes, but have not saved them. Do you want to exit without saving?" or "Are you sure?" or "Hmmm, you spent hours entering data, throw it all away?" :) – Wayne G. Dunn Jan 20 '16 at 02:11
  • Well, does the web page give that message? (it really depends). However I updated my code to reflect this issue. So I don't give a warning message if the form is closed, but adding such a message, or what kind of action can be thus placed in the above code - this really comes down to what the user wants to achieve here. – Albert D. Kallal Jan 20 '16 at 02:14