UNDO won't remove record

[Ziggy1]

I'm setting this code up and I have it working, except when the UNDO is initiated, I was hoping it would delete the record, but it only clears the field and sets focus to it.

Private Sub Skid_BeforeUpdate(Cancel As Integer)


'Checks if Pallet is already used or if it is closed
' If it is Not found it will be added to the master Skid table, otherwise skipped
' this process avoids duplicates


Dim X As String
Dim stat As String
Dim mySQL As String
Dim y As String
Dim CurSkid As String 'check current skid ID
    Dim rs As Object
Dim Rec As String
    Set rs = Me.Recordset.Clone




If Me.RecordsetClone.RecordCount <> 0 Then

Rec = Nz(rs.Skid, "NA")

Do Until rs.EOF Or Rec = Me.Skid


Rec = Nz(rs.Skid, "NA")
rs.MoveNext
Loop


End If


 
' Skid from Master Skid table
 X = Nz(DLookup("[MasterSkid]", "tblMasterSkid", "MasterSkid =" & "'" & Skid & "'"), "NA")
 'MsgBox Me.SkidID
 
 stat = Nz(DLookup("[Master Skid Status]", "tblMasterSkid", "MasterSkid =" & "'" & Skid & "'"), "NA")
 
   'populates Table
  
  mySQL = "INSERT INTO tblMasterSkid (MasterSkid) "
  mySQL = mySQL + "Values(" & "'" & Me.Skid & "'" & ")"
  'MsgBox mySQL
  
  
  'checks if skid entered already exists and if entered on this return already
  If Rec = Me.Skid Then
  
  MsgBox " You already have this skid started on this return"
  Me.Skid.Undo
  
  Cancel = True
  Else
  
  
    If X <> Me.Skid Then
  
      '
        DoCmd.SetWarnings False
        DoCmd.RunSQL mySQL
        DoCmd.SetWarnings True
  
    Else: If stat = "CLOSED" Then MsgBox "this pallet is closed"
  


    End If


End If
 

End Sub

 

[MarkK]

Given that this appears to be the BeforeUpdate handler for a control, it may not have sufficient scope to undo changes to the entire record.
I'd try the Form_BeforeUpdate() handler. See if an Undo in that context will abort the insert, and if so, maybe that's the time and place to do the validation.

 

[Ziggy1]

thanks lagbolt, it sounded like a good idea, but doesn't work. The before update of the form does not "fire" at that point it seems.

 

[HiTechCoach]

I'm setting this code up and I have it working, except when the UNDO is initiated, I was hoping it would delete the record, but it only clears the field and sets focus to it.

Private Sub Skid_BeforeUpdate(Cancel As Integer)


'Checks if Pallet is already used or if it is closed
' If it is Not found it will be added to the master Skid table, otherwise skipped
' this process avoids duplicates


Dim X As String
Dim stat As String
Dim mySQL As String
Dim y As String
Dim CurSkid As String 'check current skid ID
    Dim rs As Object
Dim Rec As String
    Set rs = Me.Recordset.Clone




If Me.RecordsetClone.RecordCount <> 0 Then

Rec = Nz(rs.Skid, "NA")

Do Until rs.EOF Or Rec = Me.Skid


Rec = Nz(rs.Skid, "NA")
rs.MoveNext
Loop


End If


 
' Skid from Master Skid table
 X = Nz(DLookup("[MasterSkid]", "tblMasterSkid", "MasterSkid =" & "'" & Skid & "'"), "NA")
 'MsgBox Me.SkidID
 
 stat = Nz(DLookup("[Master Skid Status]", "tblMasterSkid", "MasterSkid =" & "'" & Skid & "'"), "NA")
 
   'populates Table
  
  mySQL = "INSERT INTO tblMasterSkid (MasterSkid) "
  mySQL = mySQL + "Values(" & "'" & Me.Skid & "'" & ")"
  'MsgBox mySQL
  
  
  'checks if skid entered already exists and if entered on this return already
  If Rec = Me.Skid Then
  
  MsgBox " You already have this skid started on this return"
  Me.Skid.Undo
  
  Cancel = True
  Else
  
  
    If X <> Me.Skid Then
  
      '
        DoCmd.SetWarnings False
        DoCmd.RunSQL mySQL
        DoCmd.SetWarnings True
  
    Else: If stat = "CLOSED" Then MsgBox "this pallet is closed"
  


    End If


End If
 

End Sub

You code is working correctly as coded.

Me.Skid.Undo

Should only undo a the control, not the record.

To Undo the record try:

Me.Undo

 

[Ziggy1]

thanks Coach! you got it! Good eye, I totally missed that.

 

[HiTechCoach]

thanks Coach! you got it! Good eye, I totally missed that.

You're welcome! Glad I could assist ...