Results 1 to 9 of 9
  1. #1
    ResearchRN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    St. Louis, Mo.
    Posts
    21

    Suddenly: Run-time error 3420

    I have the following code in the Before Update event on my Demographics form:


    Code:
    Private Sub mrNum_BeforeUpdate(Cancel As Integer)
    'Prevents the user from entering a duplicate medical record number in  the Demographics form.
        Dim mrn As String
        Dim stLinkCriteria As String
        Dim rsc As DAO.Recordset
        Set rsc = Me.RecordsetClone
        mrn = Me.mrNum.Value
        stLinkCriteria = "[mrNum]=" & "'" & mrn & "'"
        'Check the Demographics table for duplicate Medical Record Number
        If DCount("mrNum", "tblDemographics", _
                  stLinkCriteria) > 0 Then
            'Undo duplicate entry
            Me.Undo
            'Message box warning of duplication
            MsgBox "That medical record number has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
            'Go to original record
            rsc.FindFirst stLinkCriteria
            Me.Bookmark = rsc.Bookmark
        End If
        Set rsc = Nothing
    End Sub

    When the user enters a new medical record number, the code checks to see if the medical record number already exists in the database. If it does, a message box alerts the user of the duplication and then displays the orignal record.

    The code has worked perfectly until today. Today, the highlighted line in the code causes run-time error 3420 "Object invalid or no longer set"

    I thought maybe the code had somehow become corrupted. But when I went back and tested the code in several of my back up copies of my database, they ALL result in the same error. So it's not the code. Something must have changed with Access or my computer. I haven't a clue what the problem is.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Compact & Repair does not fix? Try the project on another computer? Has it appeared to work because noone tried to enter a duplicate entry? Is this erroring with every data entry attempt or only when duplicate?

    This can be done without declaring and setting recordset. RecordsetClone is a method of the form. Example:
    Code:
    Public Sub tbxLabNum_AfterUpdate()
    Me.Requery
    With Me.RecordsetClone
        .FindFirst "LabNum='" & Me.tbxLABNUM & "'"
        If .NoMatch = True Then
            MsgBox "Invalid Lab Number", , "EntryError"
            Me.tbxLABNUM.SelStart = 6
        Else
            Me.Bookmark = .Bookmark
        End If
    End With
    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ResearchRN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    St. Louis, Mo.
    Posts
    21
    Quote Originally Posted by June7 View Post
    Compact & Repair does not fix? Try the project on another computer? Has it appeared to work because noone tried to enter a duplicate entry? Is this erroring with every data entry attempt or only when duplicate?

    This can be done without declaring and setting recordset. RecordsetClone is a method of the form. Example:
    Code:
    Public Sub tbxLabNum_AfterUpdate()
    Me.Requery
    With Me.RecordsetClone
        .FindFirst "LabNum='" & Me.tbxLABNUM & "'"
        If .NoMatch = True Then
            MsgBox "Invalid Lab Number", , "EntryError"
            Me.tbxLABNUM.SelStart = 6
        Else
            Me.Bookmark = .Bookmark
        End If
    End With
    End Sub
    No, compact and repair did not fix the problem. The error only occurs after a duplicate entry, not with every entry. The code runs fine down to "rsc.FindFirst stLinkCriteria."

    I copied the database to a USB drive then tested the code on a different computer running Access 2007. Same error.

    I'm not sure I understand the part about not declaring and setting the recordset. I've tried several different ways using "With Me.RecordsetClone". But the error still occurs at the same line every time. Here's my last attempt:
    Code:
    Private Sub mrNum_BeforeUpdate(Cancel As Integer)
    'Prevents the user from entering a duplicate medical record number in frmDemographics.
        Dim mrn As String
        Dim stLinkCriteria As String
       
        mrn = Me.mrNum.Value
        stLinkCriteria = "[mrNum]=" & "'" & mrn & "'"
     With Me.RecordsetClone
        'Check tblDemographics for duplicate Medical Record Number
        If DCount("mrNum", "tblDemographics", _
                  stLinkCriteria) > 0 Then
            'Undo duplicate entry
            Me.Undo
            'Message box warning of duplication
            MsgBox "That medical record number has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
            'Go to original record
            .FindFirst "mrNum='" & Me.mrNum & "'"   ' <<Error 3420 occurs here
        
            Me.Bookmark = .Bookmark
        End If
    End With
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Which line is the error on now?

    Is mrNum field a text or number type?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ResearchRN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    St. Louis, Mo.
    Posts
    21
    Quote Originally Posted by June7 View Post
    Which line is the error on now?

    Is mrNum field a text or number type?
    The error occurs here:
    Code:
    Private Sub mrNum_BeforeUpdate(Cancel As Integer)
    'Prevents the user from entering a duplicate medical record number in  the Demographics form.
        Dim mrn As String
        Dim stLinkCriteria As String
        Dim rsc As DAO.Recordset
        Set rsc = Me.RecordsetClone
        mrn = Me.mrNum.Value
        stLinkCriteria = "[mrNum]=" & "'" & mrn & "'"
        'Check the Demographics table for duplicate Medical Record Number
        If DCount("mrNum", "tblDemographics", _
                  stLinkCriteria) > 0 Then
            'Undo duplicate entry
            Me.Undo
            'Message box warning of duplication
            MsgBox "That medical record number has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
            'Go to original record
            rsc.FindFirst stLinkCriteria    '<<<<< Runtime Error 3420
            Me.Bookmark = rsc.Bookmark
        End If
        Set rsc = Nothing
    End Sub

    mrNum is a text data type.

    Thanks.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That's with your original code. I was asking about the revised code that did not use recordset. But I see your comment in the code now.

    So the apostrophe delimiters are appropriate.

    I would have to examine the project if you want to provide it. The Attachment Manager is below the Advanced post editor. Copy, remove confidential data, run Compact & Repair, zip if still large.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ResearchRN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    St. Louis, Mo.
    Posts
    21
    Here's the database minus data. I entered one ficticious record. Thanks very much for your help on this.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Run Dubug>Compile and you will discover 2 errors. Also, running Compact & Repair cut the file size in half.

    That is a huge form, too big for my monitor, have to scroll. Recommend condensing by tightening the spacing between controls.

    Had to rearrange some lines, but this worked:
    Code:
    Private Sub mrNum_BeforeUpdate(Cancel As Integer)
    'Prevents the user from entering a duplicate medical record number in frmDemographics.
        Dim mrn As String
        Dim stLinkCriteria As String
     
        mrn = Me.mrNum.Value
        stLinkCriteria = "[mrNum]=" & "'" & mrn & "'"
        If DCount("mrNum", "tblDemographics", stLinkCriteria) > 0 Then
            'Undo duplicate entry
            Me.Undo
            'Message box warning of duplication
            MsgBox "That medical record number has already been entered." _
                 & vbCr & vbCr & "You will now been taken to the record.", _
                   vbInformation, "Duplicate Information"
            With Me.RecordsetClone
                'Go to original record
                .FindFirst "mrNum='" & mrn & "'"
                Me.Bookmark = .Bookmark
            End With
        End If
    End Sub
    I think the issue was the DCount on the table which is also the RecordSource of the form taking place after the RecordsetClone is set.
    Last edited by June7; 01-13-2012 at 10:42 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ResearchRN is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Nov 2011
    Location
    St. Louis, Mo.
    Posts
    21
    Brilliant! You just made my week!

    I fixed the two errors and I agree about the form. It's way too busy and needs a thorough overhaul.


    Again, thanks for taking the time to work on this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Development Performance - Suddenly Whacked!
    By francisdm@state.gov in forum Access
    Replies: 2
    Last Post: 11-22-2011, 12:16 PM
  2. form is suddenly read-only
    By Karen H in forum Forms
    Replies: 28
    Last Post: 02-14-2011, 08:38 AM
  3. Replies: 2
    Last Post: 12-23-2010, 09:11 AM
  4. Replies: 2
    Last Post: 12-02-2010, 02:35 AM
  5. Editing forms suddenly gone wrong
    By twinfair in forum Forms
    Replies: 3
    Last Post: 02-19-2010, 07:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums