Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2003
    Posts
    66

    Unanswered: Run-time error 3077 caused by an apostrophe

    Hello all,

    I'm using the following code to display a record's details on a form when a name is selected from a combo box...

    Sub Combo58_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo58] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

    Thought it was working grand, untill I selected a name from the dropdown that happened to have an apostrophe in it, (ie o'brien). On selecting such a name, I get the message

    "Run-time error 3077
    Syntax error (missing operator) in expression"

    I know it's something to do with the apostrophe, but don't know how to fix it. I've experimented with the syntax, removing / adding sets of apostrophes etc., but it either makes no difference, or breaks it so that no records display at all.
    Anyone any ideas?

    Cheers
    Dave

  2. #2
    Join Date
    May 2003
    Posts
    144
    Here is what you can do.

    Sub Combo58_AfterUpdate()
    ' Find the record that matches the control.

    'First, check to see if the name contains an apostrophe.
    If instr(name, "'")> 0 then
    'If it does, add another apostrophe to the apostrophe in the name.
    So, O'Brien
    'will become O''Brien.
    Me.RecordsetClone.FindFirst "[Name] = '" & replace(Me![Combo58], "'", "''") & "'"
    else
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo58] & "'"
    end if

    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub



    Hope this helps.

    SHK

  3. #3
    Join Date
    Nov 2003
    Posts
    66
    Cheers SHK

    Tried that, but I'm getting a compile error "sub or function not defined"

    It doesn't seem to like 'replace' in
    Me.RecordsetClone.FindFirst "[Name] = '" & replace(Me![Combo58], "'", "''") & "'"

    I'm still not great on the whole coding end of things so not sure how to manipulate it to get it working.....

    D

  4. #4
    Join Date
    Nov 2003
    Posts
    66
    Hi again - that didn't work 'cos I'm using Access97 which doesn't support the Replace function.

    Sorry for bumping this post back up the list, but I could be in trouble if I can't get this fixed - I've similiar searches on a load of forms in this system.
    Had a look at a couple of MS Knowledge Base articles but it seems you need to know in advance if a search string is going to be entered that contains an apostrophe - I won't know in advance.

    Surely people have seen this problem before and managed to sort it?

    Any help would be greatly appreciated...

    Dave

  5. #5
    Join Date
    May 2003
    Posts
    144
    Take a look at this

    http://www.dbforums.com/showthread.php?threadid=978517&highlight=apostroph e

  6. #6
    Join Date
    Apr 2012
    Posts
    1

    Run-time error 3077 caused by an apostrophe

    Substitute the line:
    Me.RecordsetClone.FindFirst "[Name] = '" & Me![Combo58] & "'"
    with
    Me.RecordsetClone.FindFirst "[Name] = """ & Me![Combo58] & """"

    which means that you must susbsitute (in the error line) the apostrophe ('), with a double " ("").

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •