Names with Apostrophe in Lookup

[Timoty]

I have a glitch in my database that I have worked around for months. I would like to rectify it now.

My lookup for names works great except for those rare names with apostrophes like O'neil or O'brian. Although I can select the name in the box, the lookup won't work. I have to select the name before or after the name with the apostrophe and then scroll backwards or forwards.

Is there a quick way to fix this?

 

[Mile-O]

Replace any ' you are using as a delimiter with ""

i.e.

"MyField = 'O'Brien'"

[b]should become[/b]

"MyField = ""O'Brien"""

I find it best to always use the double "" delimiter.

 

[Timoty]

Take me one step further please...

OK so if my combo box is as stated below....where do I throw in the extra quotes...I tried it and got the old syntax error of death..

Private Sub Combo110_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Officer Name] = '" & Me![Combo110] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Mile-O]

Private Sub Combo110_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Officer Name] = '" & Me![Combo110] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

rs.FindFirst "[Officer Name] = """ & Me![Combo110] & """"

A few changes to that code though - it reads like someone's bad code that's been copied from somewhere - seen it many times.

Private Sub Combo110_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Recordset
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Officer Name] = """ & Me.[Combo110] & """"
    Me.Bookmark = rs.Bookmark
End Sub

I've never seen this Recordset.Clone thing - I've always used the RecordsetClone property. So, if something doesn't work there then remove the dot.

 

[Timoty]

Thanks SJ

Thanks, I got it to work just as your second reply came. I appreciate the extra tip on the code. I have written this entire database from trial and error.

Here is a story for you. I work for the government. I used to have a computer business. I can build and program PC's blind folded. Not bad with the office sweet and quite proficient with video editing. My boss knows this. Not being a computer guy he thinks that if a person is good with computers he/she must be able to do anything with them so he asks me to write a database and won't take no for an answer. I try to explain that computers are multifaceted and that very few people are good at all aspects. I have fixed many computers for programers that couldn't replace there video cars but who could replace the Windows operating system with a far better OS in a heart beat. I, on the other hand, had written the odd batch file up until a year ago. Anyhow, to make a long story short...I am now the, not so proud author, of a very complicated database that stores all the training and criminal code incidents for officers in our region. Problem is, I can almost guarantee I took the long way around for many functions and that a good chunk, if not all of my code is sloppy at best.

The people here at this forum have been exceptionally helpful....and very understanding.

If there is a wrong way to get code to work...I have surely found it.

Thanks again.

 

[Mile-O]

Not bad with the office sweet

What's her name?

 

[Timoty]

Got me

OK.....you got me. Spelling is trash too.

Thank God for the invention of spell checking.

Quick question...since I have your ear

I noticed (too late unfortunately) that when you delete a form's object, that the code is not deleted necessarily. Consequently there is a huge amount of old code sitting in the background. This code is not erased in the compact and repair process. Is there any quick way of keeping only the utilized code and deleting the rest without going through the database, painstakingly, line by line?

 

[Mile-O]

Is there any quick way of keeping only the utilized code and deleting the rest without going through the database, painstakingly, line by line?

You got it; that's what you have to do. There's no other way. If you use the Compile All Modules (one of the menus in the VBA Editor) you'll be taken to the deletable parts.

 

[Timoty]

Thanks again

Thanks again...and if you ever want a good laugh...I'll send you the databse and you can view code written by a hobiest with a lotta luck and help......probably good for a laugh or for educational purposes.

Gotta love the government