0

I have an error message on a form that checks for an existing SSN on the BeforeUpdate event of a text box. If it already exists in the database, the user is given a message box to this effect, and has the option to go to the existing record. I'm using this example code here. My code is below.

Private Sub txtSocialSecurityNumber_BeforeUpdate(Cancel As Integer)

    'check for existing SSN
    Dim SSN As String
    Dim strLinkCriteria As String
    Dim rsc As Recordset
    
    Set rsc = Me.RecordsetClone
    
    SSN = Me.txtSocialSecurityNumber.Value
    strLinkCriteria = "[SocialSecurityNumber] = " & "'" & SSN & "'"
    
    'look for duplicates
    If DCount("SocialSecurityNumber", "Person", LinkCriteria) > 0 Then
        'Undo duplicate entry
        Me.Undo
        'error message
        intResponse = MsgBox("Social Security Number " & SSN & " already exists in database." & _
            vbCrLf & vbCrLf & "Would you like to view the record?", vbYesNo, "Duplicate SSN")
            If intResponse = vbYes Then
                'go to record
                rsc.FindFirst strLinkCriteria
                Me.Bookmark = rsc.Bookmark
            ElseIf intResponse = vbNo Then
                Exit Sub
            End If
    End If
    
    Set rsc = Nothing

End Sub

According to this code, and several other examples I have looked up, it seems like I'm doing everything right, but I must not be because when I try to run the code and go to the existing record, I get the error "Run-time error '424': Object Required". When I debug, the row rst.FindFirst strLinkCriteria is highlighted, and hovering over it gives me the text strLinkCriteria = "[SocialSecurityNumber] = '123456789'" (123456789 is a known sample SSN in my database). Thank you to Sergey S. for pointing out the spelling error that fixed this section.

When I tell the message box to go to the record with the existing SSN, it gives me Run-time error '3021': No current record. Debug highlights the line Me.Bookmark = rsc.Bookmark with the message rsc.Bookmark = <No current record.>. So it sounds like I'm not assigning the bookmark correctly.

I've never used bookmarks before, so I'm not really sure what I'm doing wrong here. Any help would be appreciated.

Community
  • 1
  • 1
kgrafton
  • 137
  • 1
  • 8

1 Answers1

1

You declared rsc, but used rst. Change your code to 

rsc.FindFirst strLinkCriteria

And I'd recommend always use Option Explicit in every module (default can be changed on options), otherwise you'll face with such kind strange errors. With this option the typo would be found by compiller, not at runtime.

Sergey S.
  • 6,296
  • 1
  • 14
  • 29
  • 1
    @crlast86: It really can't be stressed enough how important `Option Explicit` is. Please set this option now, and add it to all existing modules. http://stackoverflow.com/a/37792963/3820271 – Andre Oct 07 '16 at 08:44
  • That fixed that issue, but now I'm having a new one. Updated my question to reflect it. – kgrafton Oct 12 '16 at 16:27
  • I doubt that. `intResponse` isn't declared. – Gustav Oct 12 '16 at 20:46
  • In DCount function you used LinkCriteria instead of strLinkCriteria, so DCount always returns total quantity of records in Person table. Please enable finally `Option Explicit` and check all your code. As I told you, you will receive such kind strange errors just because you missuse variables, it declares new empty variables instead of using existing variables with values. – Sergey S. Oct 13 '16 at 05:11