Syntax problem with replace function

[Guest]

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[Douglas J. Steele]

Nothing obvious jumps out. Try writing sqlAddRec to the immediate window to
see whether it looks okay.

 

[Brendan Reynolds]

It's just a guess, but my guess would be that the bound column of the combo
box is bound not to the company name, but to a numeric company ID.

 

[mwg]

The immediate window says:

INSERT INTO PO ([PO_Number],[company]) VALUES ('806037','Toole''s Repair
Shop')

admittingly, I've never worked with the Replace function, so I am assuming
the 2 single apostrophes are correct based on what I've read. I can run
this code successfully when there is no apostrophe in the Company name.

Nothing obvious jumps out. Try writing sqlAddRec to the immediate window
to see whether it looks okay.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[mwg]

It's actually an unbound combo being used to lookup and populate address
fields. Sorry I missed giving that detail earlier.

It's just a guess, but my guess would be that the bound column of the
combo box is bound not to the company name, but to a numeric company ID.

--
Brendan Reynolds
Access MVP

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[Guest]

Brendan is not talking about what the combo is bound to. What he means is
the all combo's have a bound column. That is the column in the combo that is
returned when you query it. Look in the combo's properties and see what the
Bound Column property says and be sure it is returning the value you are
expecting.
Note that to the combo, the bound column number is different the how you
address the column numbers. If the bound column property says 1, then when
using the column property of the combo, it would be 0. Me.cboMyCombo.Column(0)

It's actually an unbound combo being used to lookup and populate address
fields. Sorry I missed giving that detail earlier.

It's just a guess, but my guess would be that the bound column of the
combo box is bound not to the company name, but to a numeric company ID.

--
Brendan Reynolds
Access MVP

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[Brendan Reynolds]

Are the two fields, PO_Number and Company, both text fields? Could either of
the two controls, txtPONumber or cboCompany, contain a Null value? Is the
CurrentDb.Execute line the one that raises the error?

Looking at the code you posted, it's obviously not a direct copy-and-paste
of actual code. Sometimes the real problem can be hidden when code is
re-typed. Could you copy and paste the actual code?

--
Brendan Reynolds
Access MVP

It's actually an unbound combo being used to lookup and populate address
fields. Sorry I missed giving that detail earlier.

It's just a guess, but my guess would be that the bound column of the
combo box is bound not to the company name, but to a numeric company ID.

--
Brendan Reynolds
Access MVP

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[mwg]

sorry...I misread Brendan's comment...

The bound column is a text field of the Company's name

Brendan is not talking about what the combo is bound to. What he means is
the all combo's have a bound column. That is the column in the combo that
is
returned when you query it. Look in the combo's properties and see what
the
Bound Column property says and be sure it is returning the value you are
expecting.
Note that to the combo, the bound column number is different the how you
address the column numbers. If the bound column property says 1, then
when
using the column property of the combo, it would be 0.
Me.cboMyCombo.Column(0)

It's actually an unbound combo being used to lookup and populate address
fields. Sorry I missed giving that detail earlier.

It's just a guess, but my guess would be that the bound column of the
combo box is bound not to the company name, but to a numeric company
ID.

--
Brendan Reynolds
Access MVP

I'm getting a Data Type mismatch error when I run the following and
the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[Douglas J. Steele]

That looks as though it should work to me.

Try using

currentdb.execute sqlAddRec, dbFailOnError

and see whether the error message gives any more details.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The immediate window says:

INSERT INTO PO ([PO_Number],[company]) VALUES ('806037','Toole''s Repair
Shop')

admittingly, I've never worked with the Replace function, so I am assuming
the 2 single apostrophes are correct based on what I've read. I can run
this code successfully when there is no apostrophe in the Company name.

Nothing obvious jumps out. Try writing sqlAddRec to the immediate window
to see whether it looks okay.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[mwg]

Brendan, thanks so much for the replies. I appreciate the help.

Your questions got me thinking and I found the problem somewhere other than
the replace function.

Thanks again!

Are the two fields, PO_Number and Company, both text fields? Could either
of the two controls, txtPONumber or cboCompany, contain a Null value? Is
the CurrentDb.Execute line the one that raises the error?

Looking at the code you posted, it's obviously not a direct copy-and-paste
of actual code. Sometimes the real problem can be hidden when code is
re-typed. Could you copy and paste the actual code?

--
Brendan Reynolds
Access MVP

It's actually an unbound combo being used to lookup and populate address
fields. Sorry I missed giving that detail earlier.

It's just a guess, but my guess would be that the bound column of the
combo box is bound not to the company name, but to a numeric company ID.

--
Brendan Reynolds
Access MVP

I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?

 

[mwg]

Doug- thanks so much for the help!!! It wasn't the replace function
causing the problem.

Thanks again!

That looks as though it should work to me.

Try using

currentdb.execute sqlAddRec, dbFailOnError

and see whether the error message gives any more details.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The immediate window says:

INSERT INTO PO ([PO_Number],[company]) VALUES ('806037','Toole''s Repair
Shop')

admittingly, I've never worked with the Replace function, so I am
assuming the 2 single apostrophes are correct based on what I've read. I
can run this code successfully when there is no apostrophe in the Company
name.

Nothing obvious jumps out. Try writing sqlAddRec to the immediate window
to see whether it looks okay.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm getting a Data Type mismatch error when I run the following and the
company name has an apostrophe in it (i.e. Toole's Repair Shop)...

strCompany=replace(me!cboCompany,"'","''")

sqlAddRec="INSERT INTO PO ([PO_Number],[Company]) VALUES ('" &
me!txtPONumber & "','" & strCompany & "')"

currentdb.execute sqlAddRec

Can anyone see the problem that I'm obviously not seeing?