The main problem is actually not with the O'Briens, but with the basic format of the string you have created. Once
that is fixed, then you'll have a problem with the O'Briens.
Also, there are various other problems I suspect, that I'd like to help with but the information available is not there for me to know exactly how to format my response. What is the name of the form that the code is running from? Wherever this form is referred to in the code the object
Me
works as a preferred reference (No need for all the Forms(""). blah blah blah).
It's important to note too, that code should always be posted exactly as it is in your project (after compiling and testing - See
Before Posting (VBA or SQL) Code) to avoid various problems which you don't need help from us with. Please read that link and memorise. It can prove very helpful and important to you.
The format for your line #6 should be something like :
- Call rs.FindFirst("[FullName] = '" & Replace(Me.lstPupilSearch, "'", "''")')
This resolves both your current problem, as well as the O'Brien issue which surely would have raised its ugly head (The issue's not O'Brien's) once the first was cleared.