Error in Saving a Record to a table !!

  • Thread starter Thread starter Rajesh Candamourty
  • Start date Start date
R

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.
 
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)
 
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.
 
Sorry - I meant to say:
Public Function Enquote(sText As Variant) As String

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