Finding a Record based on a Combo Box

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

D Collins - 26 Jan 2005 21:57 GMT

Hello,

I think this is probably an easy question, but I need a
little help. I have a combo box on a form that locates a
record. It works fine except for the rare occasion when
there is a name like: "Sam's Sub Shop". The apostrophe
makes it bomb out.

Here is the snipet of my code:

rs.findfirst "[CompanyName] = ' & Me![cboSelectCompany]
& "'"

Thanks,
D.

Reply to this Message

Ken Snell [MVP] - 26 Jan 2005 22:04 GMT

Just double up the ' characters within the text string. Easiest way to do
this is to use the Replace function that is in ACCESS 2000 and later
versions:

rs.findfirst "[CompanyName] = ' & Replace(Me![cboSelectCompany],
"'","''",1,-1,vbTextCompare) & "'"

Signature

Ken Snell
<MS ACCESS MVP>

> Hello,
>
[quoted text clipped - 11 lines]
> Thanks,
> D.

Reply to this Message

D Collins - 27 Jan 2005 13:10 GMT

Thanks for your help!

>-----Original Message-----
>Just double up the ' characters within the text string. Easiest way to do
[quoted text clipped - 21 lines]
>
>.

Reply to this Message

D Collins - 27 Jan 2005 15:36 GMT

Thanks for this. It did work for some of my situations,
but I can't seem to figure why it won't work for 2 other
situations that I have.

Could it be that the other 2 situations have tables that
have no numeric ID field? When I go to use the combobox
tool so that it generates the code to do a find of a
record, that 3rd option doesn't show up (to find a record
based on my selection). So, what I did instead is to
write it myself. It works to find other records, but
when the record contains an apostrophe, it doesn't work.
Again, it works perfectly in situations where the
underlying forms' record source is a table that has a
numeric ID field.

What am I doing wrong?

D.

>-----Original Message-----
>Just double up the ' characters within the text string. Easiest way to do
[quoted text clipped - 21 lines]
>
>.

Reply to this Message

Ken Snell [MVP] - 27 Jan 2005 16:20 GMT

I'm not understanding clearly what the data are in this case. Are you saying
that this "Find" code isn't working for a few forms that have a different
RecordSource than the ones that are working?

Can you describe the forms' setups that aren't working? Post the forms'
RecordSource strings, and the comboboxes' RowSource strings. Also provide
some info about the tables' structures.

Signature

Ken Snell
<MS ACCESS MVP>

> Thanks for this. It did work for some of my situations,
> but I can't seem to figure why it won't work for 2 other
[quoted text clipped - 47 lines]
>>
>>.

Reply to this Message