problem with undo

[jillrein]

I have the following code that checks a recordset for duplicate records - Ihave not yet written the code to handle them - they will be allowed under certain circumstances.

All I need at the moment is for the new record to be undone if it is a duplicate. The code works fine if I comment out the filteron=false line but once I turn the filter off, the undo line stops working. The message still comes up so it is recognising it as a duplicate - I am confused as to why this is and more importantly how I can fix it.

Any help would be much appreciated.

Function isduplicate() As Boolean
Dim rst As Recordset
Application.Echo False
Forms!registerheader.RegisterQuery.Form.FilterOn = False
isduplicate = False
Set rst = Me.Recordset.Clone
rst.FindFirst "StudentID = " & Me!CboStudentID

If Not rst.NoMatch Then
Response = MsgBox("Record exists", vbYesNo)
Me.Undo
End If

rst.Close
Forms!registerheader.RegisterQuery.Form.FilterOn = True
Application.Echo True
End Function

 

[CEH]

Heres a little code I use on a textbox to check for duplicate SS#.... might give you some insight......

Private Sub SocialSecurity_BeforeUpdate(Cancel As Integer)
'to check for duplicate SS numbers
 Dim Answer As Variant
 Answer = DLookup("[SocialSecurity]", "tblApplicant", "[SocialSecurity] = '" & Me.SocialSecurity & "'")
 If Not IsNull(Answer) Then
 MsgBox "Duplicate Social Security Number Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
 
 Cancel = True
 Me.SocialSecurity.Undo
 
 
 Else:
 End If
End Sub

 

[WIS]

I have the following code that checks a recordset for duplicate records - Ihave not yet written the code to handle them - they will be allowed under certain circumstances.

All I need at the moment is for the new record to be undone if it is a duplicate. The code works fine if I comment out the filteron=false line but once I turn the filter off, the undo line stops working. The message still comes up so it is recognising it as a duplicate - I am confused as to why this is and more importantly how I can fix it.

Any help would be much appreciated.

Function isduplicate() As Boolean
Dim rst As Recordset
Application.Echo False
Forms!registerheader.RegisterQuery.Form.FilterOn = False
isduplicate = False
Set rst = Me.Recordset.Clone
rst.FindFirst "StudentID = " & Me!CboStudentID

If Not rst.NoMatch Then
Response = MsgBox("Record exists", vbYesNo)
Me.Undo
End If

rst.Close
Forms!registerheader.RegisterQuery.Form.FilterOn = True
Application.Echo True
End Function

Instead of Me.Undo try rst.Delete - the record seems to already exist.

I understand Undo removes from memory before the record is committed.

This is where the BeforeUpdate event needs to be used.

 

[jillrein]

Thanks to both of you - makes much more sense to put it on the before update event. Wasn't thinking it through properly and it fooled me by working before I removed the filter. Assistance much appreciated.