Results 1 to 4 of 4
  1. #1
    Dee300 is offline Advanced Intermediate
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    12

    Duplicate Entry Error

    I have the below code in field called Vendor Name. This prevents duplicate entries and takes the user to the existing record. It works great except if I add a new Vendor Name that has an apostrophe (example: Staple's Office Supplies) I get an error (3075). Is there anyway to prevent this?

    Private Sub VendorName_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
    Dim VendorID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone
    VendorID = Me.VendorName.Value
    stLinkCriteria = "[VendorName]=" & "'" & VendorID & "'"
    'Check Vendor table for duplicate Vendors
    If DCount("VendorName", "UtilVendors", _
    stLinkCriteria) > 0 Then
    'Undo duplicate entry
    Me.Undo
    'Message box warning of duplication
    MsgBox "Warning Vendor Name " _
    & VendorID & " has already been entered." _
    & vbCr & vbCr & "You will now be taken to the record.", _
    vbInformation, "Duplicate Information"
    'Go to record of original Vendor Name
    rsc.FindFirst stLinkCriteria
    Me.Bookmark = rsc.Bookmark


    End If
    Set rsc = Nothing
    End If
    End Sub

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Use the following code instead:
    stLinkCriteria = "[VendorName]=" & Chr(34) & VendorID & Chr(34)
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Dee300 is offline Advanced Intermediate
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    12
    That Works. Thank You!

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,912
    Glad we could help.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Replies: 4
    Last Post: 01-14-2016, 02:18 PM
  2. Prevent Duplicate Entry
    By kilosierra71 in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:08 PM
  3. Scanning table for duplicate entry from VBA
    By Buakaw in forum Programming
    Replies: 6
    Last Post: 03-08-2011, 07:29 PM
  4. MsgBox for duplicate entry
    By usmcgrunt in forum Programming
    Replies: 3
    Last Post: 11-09-2010, 08:48 AM
  5. Customer Entry/Find Duplicate/Similar names
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-27-2010, 09:20 PM

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 - Senior Forums