Check for Existing Records - Code Returns "No Current Record" message

[DPRapson]

Hi All,

The code listed below does what it should. It checks for existing records in 2 fields an then allows for 3 options:

1. Yes - Existing found, add duplicate (2 John Smiths)
2. No - Open existing record
3. Cancel - Cancel and Undo

However after the code runs I get a message "no current record" message.

debugging shows that the problem happens after the me.undo before the If Not Me.RecordsetClo ne.NoMatch Then (Highlighted in bold in the code below). If I Remove the Me.Undo, I get a run-time error:

run-time error '2115': "The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Trading Places Guest Database from saving the data in the field"

I do not have enough coding experience to know how to fix this, and so am asking for help.

I am using Office Pro 2013 on Windows 8.

Thank you,

Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Check that no user exists with the same name
 
  'Only perform check for new records
  If Me.NewRecord Then
    Dim lngUserCount As Long

    lngUserCount = Nz(DCount("*", "[tblGuests]", _
      "[FirstName]=""" & Me![FirstName] & _
           """ AND [LastName]=""" & Me![LastName] & """"))
 
    If lngUserCount > 0 Then
      Dim intReply As VbMsgBoxResult
      intReply = MsgBox("Guest Name Already Exists!" & vbNewLine & _
              "Click 'Yes' to Add Duplicate," & vbNewLine & _
              "Click 'No' open Existing Guest Record" & vbNewLine & _
              "Click 'Cancel' to Discard All Changes", vbYesNoCancel + vbExclamation)
 
   Select Case intReply
         Case vbYes
            'Allow save
            Cancel = False
            
         Case vbNo
            'Stop the save
            Cancel = True
            
            'find the first value that matches
            Me.RecordsetClone.FindFirst "[FirstName] = """ & Me![FirstName] & _
            """ AND [LastName] = """ & Me![LastName] & """"
            [B]Me.Undo[/B]
            Forms!frmGuests.FirstName.SetFocus

            'see if record was found
            If Not Me.RecordsetClone.NoMatch Then
            'move to record
            Me.Bookmark = Me.RecordsetClone.Bookmark
            End If
            
         Case vbCancel
            'Stop the save and undo the form
            Cancel = True
            Me.Undo
            MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
            Forms!frmGuests.FirstName.SetFocus
            
      End Select
    End If
  End If
End Sub

I would like to credit: TheSmileyCoder who wrote the original code.

And thank zmbd and NeoPa from this forum and TheDBGuy and Tina T from utteracces who have all helped so far.

[zmbd]

(...)
If Me.NewRecord Then
Case vbNo 
            'Stop the save 
            Cancel = True 
(...)
            Me.Undo

IFF
I understand this correctly,
Because you are working on a new record and you've canceled the update, then there is nolonger a record pointer, because there's no record, then the me.undo fails.

The new record is schonders cat in that it both exsists and doesn't exsit until commitment by opening the box, or saving the record. The before_update event, stops you from opening the lid. When you set the cancel to true, you killed the cat, so to speak, by opening the lid; thus, the undo is no longer valid because there's no cat... in this case, the cat was actually sent into the either never to meow again... or maybe... that meow in your head are all those poor lost records seeking a table in your brain to live.

Merry Christmas and Happy Holidays!
-z

[NeoPa]

The code is failing within the Form_BeforeUpda te() event procedure when it tries to select a new record.

The Cancel = True has not yet had any effect (If you set it to False again after that point in the code it would continue to apply the new record without ever knowing it had ever been set to True).

Thus, half way through an update procedure, you cannot sensibly expect the system to allow you to switch records.

[DPRapson]

Thank you for explaining why, but I an asking for help on how to fix this.

[zmbd]

Thus, the cat is still in limbo... then moving 31 to 27 should fix?

[DPRapson]

Schrodinger's cat is out of the box!

Having thought about the responses from NeoPa and zmdb, I went and rehashed my code based on on and example that looked for a record and then showed the record. And to my surprise it works.

I have listed the code below for those who are interested.

Thank you to all who helped.

Dave

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim FNAME As String
Dim LNAME As String
Dim NameCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

FNAME = Me.FirstName.Value
LNAME = Me.LastName.Value

NameCriteria = "[FirstName]=""" & FNAME & _
    """ AND [LastName]=""" & LNAME & """"

'Check Employee Table table for duplicate
If DCount("*", "tblGuests", NameCriteria) > 0 Then

'Message box warning of duplication
Select Case MsgBox("Guest name already exists!" & vbNewLine & _
"Click 'Yes' to view the existing guest record," & vbNewLine & _
"Click 'No' to add a duplicate guest record," & vbNewLine & _
"Click 'Cancel' to discard all changes.", vbYesNoCancel + vbExclamation)

Case vbYes
'Go to record of original record
Me.Undo
rsc.FindFirst NameCriteria
Me.Bookmark = rsc.Bookmark

Case vbNo
'Allow save
Cancel = False
Forms!frmGuests.FirstName.SetFocus

Case vbCancel
'Stop the save and undo the form
Me.Undo
MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
Forms!frmGuests.FirstName.SetFocus

End Select
End If

Set rsc = Nothing

End Sub