Error in Saving a Record to a table !!

[Rajesh Candamourty]

Dear All,

I have an Access database where in I take the input of the users and save to a table. While saving, Am using an SQL INSERT statement;

quote is a string with value ""

sqlstr = " insert into [P&IC Request1]"
sqlstr = sqlstr & "(Status, ItemNo, Contact, RelatedDoc, PartNo, PartNoDesc) values ("
sqlstr = sqlstr & quote & cboStatus.Value & quote & ","
sqlstr = sqlstr & quote & ItemNo.Value & quote & ","
sqlstr = sqlstr & quote & Contact.Value & quote & ","
sqlstr = sqlstr & quote & InitiatingDoc.Value & quote & ","
sqlstr = sqlstr & quote & cboPartNo.Value & quote & ","
sqlstr = sqlstr & " ' " & PartDesc.Value & " ' " & ","
sqlstr = sqlstr & ")"

I am using single quotes for the part number description since I have inches(") in the description sometimes.

In the form there is field which displays a part number description. The part number description is like - FOAM,ASSY,END, 35" X 30" or SHELL, END, 33" or sometimes RIK 2200 FLUID OVERLAY,INT'L

The program saves to table with the first two part number descriptions.
If you take a close look, the last part number description which is in bold, has a single quote (') in the description. While saving a record the program throws an error at this point.

I would appreciate if anybody gives a work around for this.

Thanks in advance.

Raj.

 

[Guest]

Create this function in a module:
Function ConvertStringSQL(GetStr As Variant) As String
On Error GoTo Err_ConvertStringSQL

Dim i
Dim BuildStr As String
If GetStr <> "" And Not IsNull(GetStr) Then
If InStr(GetStr, "'") Then
For i = 1 To Len(GetStr)
If Mid(GetStr, i, 1) = "'" Then
BuildStr = BuildStr + "'" + Mid(GetStr, i, 1)
Else
BuildStr = BuildStr + Mid(GetStr, i, 1)
End If
Next
Else
BuildStr = GetStr
End If
End If

ConvertStringSQL = BuildStr

Exit_ConvertStringSQL:
Exit Function

Err_ConvertStringSQL:
MsgBox Error$, 48, "ConvertStringSQL"
Resume Exit_ConvertStringSQL

End Function

Now after you added this function add it to the string fields
ConvertStringSQL(PartDesc.Value)

 

[Graham Mandeno]

Hi Rajesh

The trick here is to replace all instances of your delimiting quote
character with two quotes. I use a small function to do this:

Public Function Enquote(sText As String) As String
Enquote = """" & Replace(sText, """", """""") & """"
End Function

Then you don't need to worry about which quotes may or may not be in your
text:
...
sqlstr = sqlstr & Enquote(cboStatus.Value) & ","
sqlstr = sqlstr & Enquote(ItemNo.Value) & ","
...
sqlstr = sqlstr & Enquote(PartDesc.Value) & ")"

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

---

Dear All,

I have an Access database where in I take the input of the users and save to
a table. While saving, Am using an SQL INSERT statement;

quote is a string with value ""

sqlstr = " insert into [P&IC Request1]"
sqlstr = sqlstr & "(Status, ItemNo, Contact, RelatedDoc, PartNo,
PartNoDesc) values ("
sqlstr = sqlstr & quote & cboStatus.Value & quote & ","
sqlstr = sqlstr & quote & ItemNo.Value & quote & ","
sqlstr = sqlstr & quote & Contact.Value & quote & ","
sqlstr = sqlstr & quote & InitiatingDoc.Value & quote & ","
sqlstr = sqlstr & quote & cboPartNo.Value & quote & ","
sqlstr = sqlstr & " ' " & PartDesc.Value & " ' " & ","
sqlstr = sqlstr & ")"

I am using single quotes for the part number description since I have
inches(") in the description sometimes.

In the form there is field which displays a part number description. The
part number description is like - FOAM,ASSY,END, 35" X 30" or SHELL, END,
33" or sometimes RIK 2200 FLUID OVERLAY,INT'L

The program saves to table with the first two part number descriptions.
If you take a close look, the last part number description which is in bold,
has a single quote (') in the description. While saving a record the program
throws an error at this point.

I would appreciate if anybody gives a work around for this.

Thanks in advance.

Raj.

 

[Graham Mandeno]

Sorry - I meant to say:
Public Function Enquote(sText As Variant) As String

That way it won't complain if you pass Null.