Find Record With Unbound Combo Box

[LeoDee]

I have a table of Organizations. The primary key is the Organization Name. In a form I use an unbound combo box to look up the Organization Name. It works great unless there is an apostrophe in the organization name and then it gives me an error. Here is the code the Wizard generated:

rs.FindFirst "[Organization Name] = '" & Me![Combo56] & "'"

Does anyone see what the problem is? And, if it is part of the code, why was it generated as such?

Also, I know I should have used a autonumber as the primary key. Did this contribute to the problem?

Thanks

LeoDee

 

[Peter D]

This might help:

rs.FindFirst "[Organization Name] = " & chr(34) & Me![Combo56] & chr(34)

... and, yes, it's always best to use an autonumber field as your primary key field. The problem with quotes in text strings is just one reason.

Hope this helps,

 

[LeoDee]

Thanks for your reply. I added an autofield and regenerated the unbound combo box and all works well. When I get a few spare moments I will try out your code on another similar table with hopes of better understanding how this all works. Again, thanks.

 

[Pat Hartman]

Peter's code replaced the single quotes surrounding your text field with double quotes. He used the chr(34) function which turns the ASCII value 34 into its printable equivalent which is the double quote. This method is preferable since strings of double quotes are really hard to decipher. Double quotes are required in this case because the text field can contain single quotes. You cannot delimit a string with single quotes if that string may contain single quotes - Access can't work out whether you have unmatched quotes or an embedded quote.

 

[LeoDee]

Thanks. I am doing so much by rote and never really understanding completely what Access is doing. Your explanation helps.