Clear entry from Access Form UI but not DB, after saving record

enter image description here

I will enter roll, grade and data entered by and click Save Record to save record in DB. I want the Clear button to only clear the entry in UI of Form and not in DB, so I can enter a new record instead of closing the form and opening it again to make new entry.

The UndoRecord macro works on the Form UI, only if the record hasn't been saved in DB. Also I dont want to delete the record, I just want to clear the entry from the Form UI after saving it.

1 answer

  • answered 2019-03-14 05:33 C Perkins

    Here's a basic recipe for a bound form:

    1. Set the form's DataEntry property to true (for instance in the form's Property Sheet).
    2. Change the form's RecordSource query to contain an always-false statement in the WHERE clause, for example something like

    SELECT Data.* FROM Data WHERE (True = False);
    
    1. In the [Save Record] button click event handler, include code similar to the following:

    Private Sub SaveRecord_Click()
      On Error Resume Next
      Me.Dirty = False 'Attempt to save the record
      If Err.Number = 0 Then
        Me.Requery 'Force the form to reload the query and reset the data entry form
      Else
        MsgBox Err.Description, vbCritical, "Error"
      End If
    End Sub
    

    Note: The false criteria and setting DataEntry = True are actually redundant, since one or the other should be sufficient to keeping existing records from being shown in the form. However, certain key combinations could allow navigation of the form whilst showing recently added records. Having both of the settings ensures that only new records can be added without showing any saved records.


    If you only want to clear the form with the clear button, leaving newly-added records available to review, then instead setup the following code:

    Private Sub SaveRecord_Click()
      On Error Resume Next
      Me.Dirty = False 'Attempt to save the record
      If Err.Number = 0 Then
        'Only enter a new record, leaving other newly-added record accessible
        DoCmd.GoToRecord , , acNewRec
      Else
        MsgBox Err.Description, vbCritical, "Error"
      End If
    End Sub
    
    Private Sub ClearForm_Click()
      On Error Resume Next
      Me.Dirty = False 'Attempt to save the record
      If Err.Number = 0 Then
        Me.Requery
      Else
        MsgBox Err.Description, vbCritical, "Error"
      End If
    End Sub