By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Find Existing Records, Error: 2115

P: 9
Hi,

I am New to Access and have very little coding experience.

I am trying to modify the code (see below) posted by TheSmileyCoder on this site.

Basically, the code checks for existing records. 3 options are given:

1. Save new record
2. Duplicate found go to that the record
3. Cancel and Undo

Option 2. returns a runtime error:2115 and the debugger highlights the line "Me.Bookmark = Me.RecordsetClone.Bookmark"

From what I have read the problem has to do with saving the record. But I have no idea how to fix this. I would like the code to open the existing record in the form.

Can someone help?

Thank you,

Dave Rapson
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   'Check that no user exists with the same name
  3.  
  4.   'Only perform check for new records
  5.   If Me.NewRecord Then
  6.     Dim lngUserCount As Long
  7.     lngUserCount = Nz(DCount("*", "[tblGuests]", _
  8.       "[FirstName]=""" & Me![FirstName] & _
  9.            """ AND [LastName]=""" & Me![LastName] & """"))
  10.  
  11.     If lngUserCount > 0 Then
  12.       Dim intReply As VbMsgBoxResult
  13.       intReply = MsgBox("Guest Name Already Exists!" & vbNewLine & _
  14.               "Click 'Yes' to Add Duplicate," & vbNewLine & _
  15.               "Click 'No' open Existing Guest Record" & vbNewLine & _
  16.               "Click 'Cancel' to Discard All Changes", vbYesNoCancel + vbExclamation)
  17.  
  18.    Select Case intReply
  19.          Case vbYes
  20.             'Allow save
  21.             Cancel = False
  22.  
  23.          Case vbNo
  24.             'Stop the save
  25.             Cancel = True
  26.  
  27.             'find the first value that matches
  28.             Me.RecordsetClone.FindFirst "[LastName]= '" & LastName & "'"
  29.  
  30.             'see if record was found
  31.             If Not Me.RecordsetClone.NoMatch Then
  32.             'move to record
  33.             Me.Bookmark = Me.RecordsetClone.Bookmark
  34.             End If
  35.  
  36.          Case vbCancel
  37.             'Stop the save and undo the form
  38.             Cancel = True
  39.             DoCmd.RunCommand acCmdUndo
  40.             MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
  41.  
  42.       End Select
  43.     End If
  44.   End If
  45. End Sub
Dec 15 '13 #1
Share this Question
Share on Google+
11 Replies


zmbd
Expert Mod 5K+
P: 5,287
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.recordsetclone you potentially set a different pointer. If you do, remember to set the pointer back to nothing...
Expand|Select|Wrap|Line Numbers
  1. 'for example - This is air code
  2. dim zRS as dao.recordset
  3. dim zRSClone as DAO.Recordset
  4. '(...)
  5. set zRS = me.recordset
  6. set zRSClone = me.recordsetclone
  7. '(...)
  8. zRSCLone.FindFirst "[LastName]= '" & LastName & "'" 
  9. '(...)
  10. zRS.Bookmark = zRSClone.Bookmark
  11. '
  12. set zRS = nothing
  13. set zRSCLone = nothing
You can more than likely get away with the set zRS = me.recordset and just use Me.Bookmark = zRSClone.Bookmark if that's the only place in code that you are going to refer to the form's record set.
Dec 15 '13 #2

NeoPa
Expert Mod 15k+
P: 31,186
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()
Dec 15 '13 #3

P: 9
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.Bookmark = zRSClone.Bookmark"

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   'Check that no user exists with the same name
  3.  
  4.   Dim zRS As DAO.Recordset
  5.   Dim zRSClone As DAO.Recordset
  6.  
  7.   'Only perform check for new records
  8.   If Me.NewRecord Then
  9.     Dim lngUserCount As Long
  10.  
  11.     lngUserCount = Nz(DCount("*", "[tblGuests]", _
  12.       "[FirstName]=""" & Me![FirstName] & _
  13.            """ AND [LastName]=""" & Me![LastName] & """"))
  14.  
  15.     If lngUserCount > 0 Then
  16.       Dim intReply As VbMsgBoxResult
  17.       intReply = MsgBox("Guest Name Already Exists!" & vbNewLine & _
  18.               "Click 'Yes' to Add Duplicate," & vbNewLine & _
  19.               "Click 'No' open Existing Guest Record" & vbNewLine & _
  20.               "Click 'Cancel' to Discard All Changes", vbYesNoCancel + vbExclamation)
  21.  
  22.       Select Case intReply
  23.          Case vbYes
  24.             'Allow save
  25.             Cancel = False
  26.  
  27.          Case vbNo
  28.             'Stop the save
  29.             Cancel = True
  30.  
  31.             Set zRS = Me.Recordset
  32.             Set zRSClone = Me.RecordsetClone
  33.  
  34.             zRSClone.FindFirst "[LastName]= '" & LastName & "'"
  35.  
  36.             zRS.Bookmark = zRSClone.Bookmark
  37.  
  38.             Set zRS = Nothing
  39.             Set zRSClone = Nothing
  40.  
  41.         Case vbCancel
  42.             'Stop the save and undo the form
  43.             Cancel = True
  44.             DoCmd.RunCommand acCmdUndo
  45.             MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
  46.  
  47.       End Select
  48.     End If
  49.   End If
  50. End Sub
Once again, thank you.

Dave
Dec 15 '13 #4

P: 9
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
Dec 15 '13 #5

zmbd
Expert Mod 5K+
P: 5,287
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. (*_-)
Dec 15 '13 #6

P: 9
ZMDB,

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

Thank you,

Dave
Dec 15 '13 #7

zmbd
Expert Mod 5K+
P: 5,287
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.
Dec 15 '13 #8

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   'Check that no user exists with the same name
  3.  
  4.   dim zRS as dao.recordset
  5.   dim zRSClone as DAO.Recordset
  6.  
  7.   'Only perform check for new records
  8.   If Me.NewRecord Then
  9.     Dim lngUserCount As Long
  10.  
  11.     lngUserCount = Nz(DCount("*", "[tblGuests]", _
  12.       "[FirstName]=""" & Me![FirstName] & _
  13.            """ AND [LastName]=""" & Me![LastName] & """"))
  14.  
  15.     If lngUserCount > 0 Then
  16.       Dim intReply As VbMsgBoxResult
  17.       intReply = MsgBox("Guest Name Already Exists!" & vbNewLine & _
  18.               "Click 'Yes' to Add Duplicate," & vbNewLine & _
  19.               "Click 'No' open Existing Guest Record" & vbNewLine & _
  20.               "Click 'Cancel' to Discard All Changes", vbYesNoCancel + vbExclamation)
  21.  
  22.       Select Case intReply
  23.          Case vbYes
  24.             'Allow save
  25.             Cancel = False
  26.  
  27.          Case vbNo
  28.             'Stop the save
  29.             Cancel = True =/= Call {frmGuests}.Undo()
  30.  
  31.         set zRS = me.recordset
  32.         set zRSClone = me.recordsetclone
  33.  
  34.             zRSCLone.FindFirst "[LastName]= '" & LastName & "'" 
  35.  
  36.             zRS.Bookmark = zRSClone.Bookmark
  37.  
  38.         set zRS = nothing
  39.         set zRSCLone = nothing
  40.  
  41.          Case vbCancel
  42.             'Stop the save and undo the form
  43.             Cancel = True
  44.             DoCmd.RunCommand acCmdUndo
  45.             MsgBox "Add a New Guest or Select the Existing Guest From the 'Lookup Guest' Function."
  46.  
  47.       End Select
  48.     End If
  49.   End If
  50. End Sub
Dec 15 '13 #9

zmbd
Expert Mod 5K+
P: 5,287
"=/=" 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub CBO1_BeforeUpdate(Cancel As Integer)
  2.    Cancel = True
  3.    Call Me.Undo
  4.    Me.Recordset.MoveFirst
  5. End Sub
Dec 15 '13 #10

P: 9
zmbd and Neopa,

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

Regards,

Dave
Dec 15 '13 #11

NeoPa
Expert Mod 15k+
P: 31,186
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 :-)
Dec 16 '13 #12

Post your reply

Sign in to post your reply or Sign up for a free account.