Finding a Record based on a Combo Box

  • Thread starter Thread starter D Collins
  • Start date Start date
D

D Collins

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.
 
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) & "'"
 
Thanks for your help!

-----Original Message-----
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) & "'"

--

Ken Snell
<MS ACCESS MVP>

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.
Click to expand...


.
Click to expand...
 
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
this is to use the Replace function that is in ACCESS 2000 and later
versions:

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

--

Ken Snell
<MS ACCESS MVP>

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.
Click to expand...


.
Click to expand...
 
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.
--

Ken Snell
<MS ACCESS MVP>



D Collins said:
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
this is to use the Replace function that is in ACCESS 2000 and later
versions:

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

--

Ken Snell
<MS ACCESS MVP>

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.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top