Find Existing Records, Error: 2115

**zmbd** · Dec 15 '13, 02:18 PM

1) Please, when reporting errors,
Which OS
Which Version of Access/Office
Report the exact title, number and text. The wonderfull MS programers re-used the text in some cases and title in other. Besides, it's nice not to have to pull out the list and hunt thru a thousand error codes to find what the report is.

2) The control is dirty, a record move forces a save, however, because the code is still running in the before update event, the record can't be saved... I'll have to look at some of my code to see why the cancel=true in your's didn't clear.

3) IMHO: you really should set a pointer to the recordsetclone. Each time you invoke the me.recordsetclo ne you potentially set a different pointer. If you do, remember to set the pointer back to nothing...

Code:

'for example - This is air code
dim zRS as dao.recordset
dim zRSClone as DAO.Recordset
'(...)
set zRS = me.recordset
set zRSClone = me.recordsetclone
'(...)
zRSCLone.FindFirst "[LastName]= '" & LastName & "'" 
'(...)
zRS.Bookmark = zRSClone.Bookmark
'
set zRS = nothing
set zRSCLone = nothing

You can more than likely get away with the set zRS = me.recordset and just use Me.Bookmark = zRSClone.Bookma rk if that's the only place in code that you are going to refer to the form's record set.

**NeoPa** · Dec 15 '13, 03:10 PM

Originally posted by Zmbd

I'll have to look at some of my code to see why the cancel=true in your's didn't clear.

Cancel = True =/= Call {Control or form}.Undo()

**DPRapson** · Dec 15 '13, 03:18 PM

Hi ZMBD,

Thank you for your response and suggestions. In reply I am using MS Access Professional Plus 2013 on Windows 8 Professional.

The exact error message is as follows:

Run-time error '2115': The Macro or function set in the BeforeUpdate or Validation Rule property for this field is preventing Trading Places Database from saving the data in the field.

"Trading Places Database" is the name of the database.

2. I will have to read up on what "dirty means"

3. I changed my code as to as below but get an error:

Run-time error '3426': This action was cancelled by an associated object.

and the debugger highlights the line "zRS.Bookma rk = zRSClone.Bookma rk"

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Check that no user exists with the same name
  
  Dim zRS As DAO.Recordset
  Dim zRSClone As DAO.Recordset
 
  '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

            Set zRS = Me.Recordset
            Set zRSClone = Me.RecordsetClone
            
            zRSClone.FindFirst "[LastName]= '" & LastName & "'"

            zRS.Bookmark = zRSClone.Bookmark
        
            Set zRS = Nothing
            Set zRSClone = Nothing
            
        Case vbCancel
            'Stop the save and undo the form
            Cancel = True
            DoCmd.RunCommand acCmdUndo
            MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
            
      End Select
    End If
  End If
End Sub

Once again, thank you.

Dave

**DPRapson** · Dec 15 '13, 03:26 PM

Hi Neopa,

Thank you for replying. I changed line 25 of the code I posted like you suggested, but text is high-lighted in red and when I compile I get a Compile Error: Syntax error window.

Regards,

Dave

**zmbd** · Dec 15 '13, 03:29 PM

Thnx Neopa, I knew I had a way to do this, just an early morning and I couldn't remember... could be the the lack of that early morning caffine

DPRapson:
Cut and paste just the offendng line, most likely a typo, I do such all the time. (*_-)

**DPRapson** · Dec 15 '13, 05:23 PM

ZMDB,

Please can you clarify your last post. Where is the typo? What must I Cut and Paste?

Thank you,

Dave

**zmbd** · Dec 15 '13, 06:47 PM

Originally posted by DPRapson

Thank you for replying. I changed line 25 of the code I posted like you suggested, but text is high-lighted in red and when I compile I get a Compile Error: Syntax error window.

Please can you clarify your last post. Where is the typo? What must I Cut and Paste?

(?_?)

The line that you said is causing you problems....
If you will cut and past the offending line of code we can take a look at it and see if there is a typo or other issue.

**DPRapson** · Dec 15 '13, 07:07 PM

zmbd,

Sorry I miss read! The line of code is exactly as per Neopa's post:

Cancel = True =/= Call {Control or form}.Undo()

I have tried "Googling" but cannot find anything remotely similar.

Dave

P.S. This is what the code looks like at the moment:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
  'Check that no user exists with the same name
  
  dim zRS as dao.recordset
  dim zRSClone as DAO.Recordset
 
  '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 =/= Call {frmGuests}.Undo()

	    set zRS = me.recordset
	    set zRSClone = me.recordsetclone
            
            zRSCLone.FindFirst "[LastName]= '" & LastName & "'" 

            zRS.Bookmark = zRSClone.Bookmark
	    
	    set zRS = nothing
	    set zRSCLone = nothing
            
         Case vbCancel
            'Stop the save and undo the form
            Cancel = True
            DoCmd.RunCommand acCmdUndo
            MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
            
      End Select
    End If
  End If
End Sub

**zmbd** · Dec 15 '13, 09:05 PM

"=/=" means does not equal. Neo was reminding me about the undo.

So: [Undo Method (ACC2010)

I pulled this out of context and changed the control name
in anycase, as written, any change should be undone, sort of like setting allow edits to false. ;-) . so it's sort of a "cute" code just to provide an example, not something that would be normally used.

Code:

Private Sub CBO1_BeforeUpdate(Cancel As Integer)
   Cancel = True
   Call Me.Undo
   Me.Recordset.MoveFirst
End Sub

**DPRapson** · Dec 15 '13, 09:21 PM

zmbd and Neopa,

With help, I have managed to solve the problem. Thank you for your assistance.

Regards,

Dave

**NeoPa** · Dec 16 '13, 12:05 AM

Dave.

Sorry for the confusion. My post was to be read in the context of the quoted text from Z's earlier post. It was a comment directed towards clarifying the situation for Z, who had temporarily forgotten a detail.

I was happy that what he had posted was already enough to help you on your way, so I wasn't looking to post a direct answer to your question at that stage.

Glad you got it resolved anyway :-)

Find Existing Records, Error: 2115

Find Existing Records, Error: 2115

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment