Undo The Last Record My Form Just Submitted

[athyeh]

Hello,

I have scoured several forums and tried all sorts of different things but for some reason I cannot get it to work.

I have a form that I am using for data-entry. On that form I have a subform based on a query, that is linked to the table my main, "parent"? form is inputting into. I am using the subform kind of as a 'running record' to show the form-user all the records he/she has entered so far.

http://answers.microsoft.com/en-us/o...d-1f981de7e55a

This most closely resembles my problem, but after entering a record, and clicking the button, either, nothing happens, or the first error message displays with ok and cancel options, and clicking ok does nothing...


Private Sub Cancel_Click()
On Error GoTo Err_Handler
Const MESSAGETEXT = "Confirm deletion of current " & _
"record and any related records?"

Dim strSQL As String
Dim strCriteria As String

strCriteria = "LID = " & Nz(Me.LID, 0)
strSQL = "DELETE * FROM tblMainDB WHERE " & strCriteria

If MsgBox(MESSAGETEXT, vbQuestion + vbOKCancel, "Confirm") = vbOK Then
' has record been saved to table? If so delete it,
' otherwise undo current record
If Not IsNull(DLookup("LID", "tblMainDB", strCriteria)) Then


CurrentDb.Execute strSQL, dbFailOnError
Else
Me.Undo
End If
DoCmd.Close acForm, Me.Name
End If
Exit_Here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub


Thank you for your replies.

[ssanfu]

To be clear:
You have a main form where the DataEntry property is set to YES.

On the main form, you have a sub-form. The sub-form is not linked to the main form.

You saved the data that was entered in the main form?

but after entering a record, and clicking the button

Which form is the "button" located on: main form or sub-form?

How do you select which record you want to delete?

[athyeh]

On the main form DataEntry property is set to yes.

On the main form, I have a sub-form that I created using the Design tools and wizard. How can I check if it is linked?

Because you brought it up, I do believe that the macro I am using to record my records is saving the data as it is entered. I will post when I get to my omp tomorrow morning.

The button is located on the main form but I also tried to place it on the sub-form and I couldn't even see it when I looked at the sub-form through the main-form (the button that is)

Up to this point, I was assuming that the "undo" function would just "undo" (like the undo button on all Microsoft apps) the last record entered. Do I have to point it?

[athyeh]

RunCommand acCmdSaveRecord
Me![qrySubForm subform].Requery
Me.Requery
End Sub

Is the code attached to an After Update Event on the field that processors are using a scanning wand to scan a barcode.

[ssanfu]

On the main form, I have a sub-form that I created using the Design tools and wizard. How can I check if it is linked?

Open the main form in design view. Click on the sub-form control - not the sub-form itself. Open the properties window. You should see "Master field" and "Child field" text boxes. If they are blank, the main form and sub-form are not linked.

-----------------
I don't use "RunCommand acCmdSaveRecord". (I think it should actually be DoCmd.RunCommand acCmdSaveRecord)
To force a save, I use

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

---------------------
I'm not sure if the main form is in continuous forms or single form view; or where "Me.LID" is located.
If the main form is in "continuous form" and LID is visible, then selecting a record and clicking the Delete (Cancel??) button will delete a record.

I just don't understand your setup. But... the way I set up the main form, the code will delete the selected record (if LID is on the main form).
If LID is on the sub-form, the reference to LID must be changed to look at the sub-form.

I modified your code a little, so try this on a test dB. (Remember, LID must be on the main form.....)

Private Sub Cancel_Click()
   On Error GoTo Err_Handler
   Const MESSAGETEXT = "Confirm deletion of current " & _
         "record and any related records?"

   Dim strSQL As String
   Dim strCriteria As String
   Dim Response

   strCriteria = "LID = " & Nz(Me.LID, 0)
   strSQL = "DELETE * FROM tblMainDB WHERE " & strCriteria

   Response = MsgBox(MESSAGETEXT, vbQuestion + vbOKCancel, "Confirm")
   If Response = vbOK Then
      ' has record been saved to table? If so delete it,
      ' otherwise undo current record
      If Not IsNull(DLookup("LID", "tblMainDB", strCriteria)) Then
         CurrentDb.Execute strSQL, dbFailOnError
         MsgBox "Deleted"
      Else
         Me.Undo
         MsgBox "No records to delete"
      End If
      DoCmd.Close acForm, Me.Name

   Else
      MsgBox "You chose Cancel"
   End If


Exit_Here:
   Exit Sub

Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here

End Sub