Acc2003 Special Characters in Memo Field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I can't use some special characters in memo field. Error message is "Syntax
error (missing operator) in query expression". I know I've had this problem
previously, but cannot remember how I fixed it. Please help. Thanks.

Sheldon...
 
sheldon said:
Hi,

I can't use some special characters in memo field. Error message is
"Syntax error (missing operator) in query expression". I know I've
had this problem previously, but cannot remember how I fixed it.
Please help. Thanks.
Click to expand...

Under what circumstances is this message appearing? It seems to be
referring to a query, but you haven't told us anything about any query
you are running.
 
Hi,

The error message appears when saving form. There is a save command button.
On click, all data entered into the form is saved into the proper table.
This is when the error message appears.

Regards,

Sheldon...
 
sheldon said:
Hi,

The error message appears when saving form. There is a save command
button. On click, all data entered into the form is saved into the
proper table. This is when the error message appears.
Click to expand...

I guess it's an unbound form, then. Please post the code in the
button's Click event procedure.
 
The memo field name is text9.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim db As Database
Dim strSQLIns As String
Dim qdfIns As QueryDef
Dim ttList As SubForm


Dim mCode As String
Dim mStatus As String
Dim mTT As String
Dim mUnq As Long
Dim mCmt As String
Dim mCO As String
Dim mEmail As String
Dim mAdType As String
Dim mProMIS As String
Dim mFirst As String
Dim mAddr1 As String
Dim mAddr2 As String
Dim mCity As String
Dim mSt As String
Dim mZip As String
Dim mRef As String

Dim mFL As Integer
Dim mWR As Integer
Dim mLS As Integer
Dim mUp As Integer
' Dim mTT As Integer

Dim mTTc As Integer

Dim stDocName As String



Set db = CurrentDb
Set wrkCurrent = DBEngine.Workspaces(0)

' Start of New Trouble Ticket code


If mEdit = "N" Then

If Len(Trim(Forms!frmNewTT!txtLast.Value & " ")) < 2 Then
MsgBox "Need Last Name"
Forms!frmNewTT!txtLast.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!txtPhone.Value & " ")) < 7 Then
MsgBox "Need Phone Number"
Forms!frmNewTT!txtPhone.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!cmbReg.Value & " ")) < 2 Then
MsgBox "Need Region"
Forms!frmNewTT!cmbReg.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!cmbRef.Value & " ")) < 1 Then
MsgBox "Need Referral"
Forms!frmNewTT!cmbRef.SetFocus
Exit Sub
End If




If IsNull(CheckFL.Value) Then
mFL = 0
Else
mFL = CheckFL.Value
End If

If IsNull(CheckWR.Value) Then
mWR = 0
Else
mWR = CheckWR.Value
End If

If IsNull(CheckLS.Value) Then
mLS = 0
Else
mLS = CheckLS.Value
End If

If IsNull(CheckUP.Value) Then
mUp = 0
Else
mUp = CheckUP.Value
End If

If IsNull(CheckTT.Value) Then
mTTc = 0
mStatus = "Open"
Else
mTTc = CheckTT.Value
If mTTc <> 0 Then
mStatus = "Closed"
Else
mStatus = "Open"
End If
End If


mCO = Forms!frmNewTT!txtCo.Value & " "
mEmail = Forms!frmNewTT!txtEmail.Value & " "
mFirst = Forms!frmNewTT!txtFirst.Value & " "
mAdType = Forms!frmNewTT!cmbAdType.Value & " "
mAddr1 = Forms!frmNewTT!txtAddr1.Value & " "
mAddr2 = Forms!frmNewTT!txtAddr2.Value & " "
mCity = Forms!frmNewTT!txtCity.Value & " "
mSt = Forms!frmNewTT!txtSt.Value & " "
mZip = Forms!frmNewTT!txtZip.Value & " "
mReg = Trim(Forms!frmNewTT!cmbReg.Value)
mSS = Trim(Forms!frmNewTT!txtSSNum.Value) & " "
mProp = Trim(Forms!frmNewTT!txtProp.Value) & " "
mProMIS = Forms!frmNewTT!txtVendID.Value & " "
mRef = Forms!frmNewTT!cmbRef.Value


mTCode = "CA"

mCmt = "Not Used"
mUnq = NewQI_Num()

mTTUn = mUnq * 1


mTTBod = "Region: " & mReg & vbCrLf & "Property: " & mProp & vbCrLf &
"Vendor ID: " & mProMIS & vbCrLf & "Company: " & mCO & vbCrLf & "Last Name: "
& Forms!frmNewTT!txtLast.Value & _
vbCrLf & "First Name: " & mFirst & vbCrLf & "Email: " & mEmail
& vbCrLf & "Address: " & _
mAddr1 & ", " & Trim(mAddr2) & ", " & mCity & ", " & mSt & ",
" & mZip & _
vbCrLf & "Phone: " & Forms!frmNewTT!txtPhone.Value & vbCrLf & _
"Notes: " & vbCrLf & Text9.Value



'MsgBox Str(mUnq)

' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True

'Insert Sub Table Record

strSQLIns = "INSERT INTO [address_change] (UnqId, UpdateOccur, RChange,
DRDate, CheckFL, FLDate, CheckWR, WRDate, CheckLS, LSDate, CheckUP,
fd_UPDate, CheckTT, TTDate, Addr1, Addr2, Addr3, Addr4, Notes) " & _
"SELECT " & mUnq & ",'1','" & txtChange.Value & "', #" &
Now() & "#, " & mFL & ", #" & FLDate.Value & "#, " & mWR & ", #" &
WRDate.Value & "#, " & mLS & ", #" & LSDate.Value & "#, " & mUp & ", #" &
UPDate.Value & "#, " & mTTc & ", #" & TTDate.Value & "#, '" & txtAddr1.Value
& "', '" & txtAddr2.Value & "', '" & txtAddr3.Value & "', '" & txtAddr4.Value
& "', '" & Text9.Value & "'"

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

'Insert Trouble Ticket Record
strSQLIns = "INSERT INTO [trouble_ticket] (UnqID, TDate, vID, Company,
Last, fd_First, Phone, Email, AddrType, Addr1, Addr2, City, St, Zip, Region,
SSNum, Property, EntryPer, TypeCode, Ref, Assigned, fd_Status, NetworkID) " &
_
"SELECT " & mUnq & ",#" & Now() & "#, '" & mProMIS & "', '"
& mCO & "', '" & Forms!frmNewTT!txtLast.Value & "', '" & mFirst & "', '" &
Forms!frmNewTT!txtPhone.Value & "', '" & mEmail & "', '" & mAdType & "', '" &
mAddr1 & "', '" & mAddr2 & "', '" & mCity & "', '" & mSt & "', '" & mZip &
"', '" & mReg & "', '" & mSS & "', '" & mProp & "', '" & mLogin & "', '" &
mTCode & "', '" & Trim(mRef) & "', '" & mLogin & "', '" & mStatus & "', '" &
mUser & "'"


' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)


End If

' update code

If mEdit = "U" Then

If Len(Trim(Forms!frmUpdTT!txtLast.Value & " ")) < 2 Then
MsgBox "Need Last Name"
Forms!frmUpdTT!txtLast.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmUpdTT!txtPhone.Value & " ")) < 7 Then
MsgBox "Need Phone Number"
Forms!frmUpdTT!txtPhone.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmUpdTT!cmbReg.Value & " ")) < 2 Then
MsgBox "Need Region"
Forms!frmUpdTT!cmbReg.SetFocus
Exit Sub
End If




If IsNull(CheckFL.Value) Then
mFL = 0
Else
mFL = CheckFL.Value
End If

If IsNull(CheckWR.Value) Then
mWR = 0
Else
mWR = CheckWR.Value
End If

If IsNull(CheckLS.Value) Then
mLS = 0
Else
mLS = CheckLS.Value
End If

If IsNull(CheckUP.Value) Then
mUp = 0
Else
mUp = CheckUP.Value
End If

If IsNull(CheckTT.Value) Then
mTTc = 0
mStatus = "Open"
Else
mTTc = CheckTT.Value
If mTTc <> 0 Then
mStatus = "Closed"
Else
mStatus = "Open"
End If
End If


mCO = Trim(Forms!frmUpdTT!txtCo.Value) & " "
mEmail = Trim(Forms!frmUpdTT!txtEmail.Value) & " "
mFirst = Trim(Forms!frmUpdTT!txtFirst.Value) & " "
mAdType = Trim(Forms!frmUpdTT!cmbAdType.Value) & " "
mAddr1 = Trim(Forms!frmUpdTT!txtAddr1.Value) & " "
mAddr2 = Trim(Forms!frmUpdTT!txtAddr2.Value) & " "
mCity = Trim(Forms!frmUpdTT!txtCity.Value) & " "
mSt = Trim(Forms!frmUpdTT!txtSt.Value) & " "
mZip = Trim(Forms!frmUpdTT!txtZip.Value) & " "
mReg = Trim(Forms!frmUpdTT!cmbReg.Value) & " "
mSS = Trim(Forms!frmUpdTT!txtSSNum.Value) & " "
mProp = Trim(Forms!frmUpdTT!txtProp.Value) & " "
mProMIS = Trim(Forms!frmUpdTT!txtVendID.Value) & " "

mTCode = "CA"

mTTBod = "Region: " & mReg & vbCrLf & "Property: " & mProp & vbCrLf &
"Vendor ID: " & mProMIS & vbCrLf & "Company: " & mCO & vbCrLf & "Last Name: "
& Forms!frmUpdTT!txtLast.Value & _
vbCrLf & "First Name: " & mFirst & vbCrLf & "Email: " & mEmail
& vbCrLf & "Address: " & _
mAddr1 & ", " & Trim(mAddr2) & ", " & mCity & ", " & mSt & ",
" & mZip & _
vbCrLf & "Phone: " & Forms!frmUpdTT!txtPhone.Value & vbCrLf & _
"Notes: " & vbCrLf & Text9.Value



' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True


'Update Trouble Ticket Record
strSQLIns = "UPDATE [trouble_ticket] Set Company = '" & mCO & "', Email
= '" & mEmail & "', Last = '" & Forms!frmUpdTT!txtLast.Value & "', fd_First =
'" & mFirst & "', Phone = '" & Forms!frmUpdTT!txtPhone.Value & "', AddrType
= '" & mAdType & "', vID = '" & mProMIS & "', Addr1 = '" & mAddr1 & "', Addr2
= '" & mAddr2 & "', City = '" & mCity & "', Zip = '" & mZip & "', Region = '"
& mReg & "', SSNum = '" & mSS & "', Property = '" & mProp & "', fd_Status =
'" & mStatus & "'" & _
"WHERE UnqID = " & Forms!frmUpdTT!Text30.Value


' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

'Update Sub Table Record

' strSQLIns = "INSERT INTO [PCPNR] (UnqId, UpdateOccur, DRDate,
CorProMIS, CheckAP, APDate, CheckLL, LLDate, CheckTT, TTDate, Notes) " & _
"SELECT " & mUnq & ",'1', #" & Now() & "#, '" & Combo4.Value
& "', " & mAP & ", #" & APDate.Value & "#, " & mLL & ", #" & LLDate.Value &
"#, " & mTTc & ", #" & TTDate.Value & "#, '" & Text149.Value & "'"


strSQLIns = "UPDATE [address_change] Set RChange = '" & txtChange.Value
& "', CheckFL = " & mFL & ", FLDate = #" & FLDate.Value & "#, CheckWR = " &
mWR & ", WRDate = #" & WRDate.Value & "#, CheckLS = " & mLS & ", LSDate = #"
& LSDate.Value & "#, CheckUP = " & mUp & ", fd_UPDate = #" & UPDate.Value &
"#, CheckTT = " & mTTc & ", TTDate = #" & TTDate.Value & "#, Addr1 = '" &
txtAddr1.Value & " ', Addr2 = '" & txtAddr2.Value & " ', Addr3 = '" &
txtAddr3.Value & " ', Addr4 = '" & txtAddr4.Value & " ', Notes = '" &
Text9.Value & "' " & _
"WHERE UnqID = " & Forms!frmUpdTT!Text30.Value

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

' End If


End If


wrkCurrent.CommitTrans
fInTrans = False

Exit_Command11_Click:
' Set ttList = Forms!frmMain![Trouble Ticket subform]
' ttList.Requery
If mEdit = "N" Then
stDocName2 = "frmTTSave"
DoCmd.OpenForm stDocName2, , , stLinkCriteria2



'MsgBox "Trouble Ticket #" & Str(mUnq) & " entered"
Else
stDocName2 = "frmTTMod"
DoCmd.OpenForm stDocName2, , , stLinkCriteria2
End If

' DoCmd.Close
Exit Sub

Err_Command11_Click:
' If any errors rollback transaction
If fInTrans Then
wrkCurrent.Rollback
End If
MsgBox Err.Description & vbCrLf & "Please Give Admin Error Desc"
DoCmd.Quit
Resume Exit_Command11_Click


End Sub
 
I've narrowed it down to an apostrophe. I get the error message when
entering an apostrophe in the memo field.



sheldon said:
The memo field name is text9.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim db As Database
Dim strSQLIns As String
Dim qdfIns As QueryDef
Dim ttList As SubForm


Dim mCode As String
Dim mStatus As String
Dim mTT As String
Dim mUnq As Long
Dim mCmt As String
Dim mCO As String
Dim mEmail As String
Dim mAdType As String
Dim mProMIS As String
Dim mFirst As String
Dim mAddr1 As String
Dim mAddr2 As String
Dim mCity As String
Dim mSt As String
Dim mZip As String
Dim mRef As String

Dim mFL As Integer
Dim mWR As Integer
Dim mLS As Integer
Dim mUp As Integer
' Dim mTT As Integer

Dim mTTc As Integer

Dim stDocName As String



Set db = CurrentDb
Set wrkCurrent = DBEngine.Workspaces(0)

' Start of New Trouble Ticket code


If mEdit = "N" Then

If Len(Trim(Forms!frmNewTT!txtLast.Value & " ")) < 2 Then
MsgBox "Need Last Name"
Forms!frmNewTT!txtLast.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!txtPhone.Value & " ")) < 7 Then
MsgBox "Need Phone Number"
Forms!frmNewTT!txtPhone.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!cmbReg.Value & " ")) < 2 Then
MsgBox "Need Region"
Forms!frmNewTT!cmbReg.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmNewTT!cmbRef.Value & " ")) < 1 Then
MsgBox "Need Referral"
Forms!frmNewTT!cmbRef.SetFocus
Exit Sub
End If




If IsNull(CheckFL.Value) Then
mFL = 0
Else
mFL = CheckFL.Value
End If

If IsNull(CheckWR.Value) Then
mWR = 0
Else
mWR = CheckWR.Value
End If

If IsNull(CheckLS.Value) Then
mLS = 0
Else
mLS = CheckLS.Value
End If

If IsNull(CheckUP.Value) Then
mUp = 0
Else
mUp = CheckUP.Value
End If

If IsNull(CheckTT.Value) Then
mTTc = 0
mStatus = "Open"
Else
mTTc = CheckTT.Value
If mTTc <> 0 Then
mStatus = "Closed"
Else
mStatus = "Open"
End If
End If


mCO = Forms!frmNewTT!txtCo.Value & " "
mEmail = Forms!frmNewTT!txtEmail.Value & " "
mFirst = Forms!frmNewTT!txtFirst.Value & " "
mAdType = Forms!frmNewTT!cmbAdType.Value & " "
mAddr1 = Forms!frmNewTT!txtAddr1.Value & " "
mAddr2 = Forms!frmNewTT!txtAddr2.Value & " "
mCity = Forms!frmNewTT!txtCity.Value & " "
mSt = Forms!frmNewTT!txtSt.Value & " "
mZip = Forms!frmNewTT!txtZip.Value & " "
mReg = Trim(Forms!frmNewTT!cmbReg.Value)
mSS = Trim(Forms!frmNewTT!txtSSNum.Value) & " "
mProp = Trim(Forms!frmNewTT!txtProp.Value) & " "
mProMIS = Forms!frmNewTT!txtVendID.Value & " "
mRef = Forms!frmNewTT!cmbRef.Value


mTCode = "CA"

mCmt = "Not Used"
mUnq = NewQI_Num()

mTTUn = mUnq * 1


mTTBod = "Region: " & mReg & vbCrLf & "Property: " & mProp & vbCrLf &
"Vendor ID: " & mProMIS & vbCrLf & "Company: " & mCO & vbCrLf & "Last Name: "
& Forms!frmNewTT!txtLast.Value & _
vbCrLf & "First Name: " & mFirst & vbCrLf & "Email: " & mEmail
& vbCrLf & "Address: " & _
mAddr1 & ", " & Trim(mAddr2) & ", " & mCity & ", " & mSt & ",
" & mZip & _
vbCrLf & "Phone: " & Forms!frmNewTT!txtPhone.Value & vbCrLf & _
"Notes: " & vbCrLf & Text9.Value



'MsgBox Str(mUnq)

' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True

'Insert Sub Table Record

strSQLIns = "INSERT INTO [address_change] (UnqId, UpdateOccur, RChange,
DRDate, CheckFL, FLDate, CheckWR, WRDate, CheckLS, LSDate, CheckUP,
fd_UPDate, CheckTT, TTDate, Addr1, Addr2, Addr3, Addr4, Notes) " & _
"SELECT " & mUnq & ",'1','" & txtChange.Value & "', #" &
Now() & "#, " & mFL & ", #" & FLDate.Value & "#, " & mWR & ", #" &
WRDate.Value & "#, " & mLS & ", #" & LSDate.Value & "#, " & mUp & ", #" &
UPDate.Value & "#, " & mTTc & ", #" & TTDate.Value & "#, '" & txtAddr1.Value
& "', '" & txtAddr2.Value & "', '" & txtAddr3.Value & "', '" & txtAddr4.Value
& "', '" & Text9.Value & "'"

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

'Insert Trouble Ticket Record
strSQLIns = "INSERT INTO [trouble_ticket] (UnqID, TDate, vID, Company,
Last, fd_First, Phone, Email, AddrType, Addr1, Addr2, City, St, Zip, Region,
SSNum, Property, EntryPer, TypeCode, Ref, Assigned, fd_Status, NetworkID) " &
_
"SELECT " & mUnq & ",#" & Now() & "#, '" & mProMIS & "', '"
& mCO & "', '" & Forms!frmNewTT!txtLast.Value & "', '" & mFirst & "', '" &
Forms!frmNewTT!txtPhone.Value & "', '" & mEmail & "', '" & mAdType & "', '" &
mAddr1 & "', '" & mAddr2 & "', '" & mCity & "', '" & mSt & "', '" & mZip &
"', '" & mReg & "', '" & mSS & "', '" & mProp & "', '" & mLogin & "', '" &
mTCode & "', '" & Trim(mRef) & "', '" & mLogin & "', '" & mStatus & "', '" &
mUser & "'"


' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)


End If

' update code

If mEdit = "U" Then

If Len(Trim(Forms!frmUpdTT!txtLast.Value & " ")) < 2 Then
MsgBox "Need Last Name"
Forms!frmUpdTT!txtLast.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmUpdTT!txtPhone.Value & " ")) < 7 Then
MsgBox "Need Phone Number"
Forms!frmUpdTT!txtPhone.SetFocus
Exit Sub
End If

If Len(Trim(Forms!frmUpdTT!cmbReg.Value & " ")) < 2 Then
MsgBox "Need Region"
Forms!frmUpdTT!cmbReg.SetFocus
Exit Sub
End If




If IsNull(CheckFL.Value) Then
mFL = 0
Else
mFL = CheckFL.Value
End If

If IsNull(CheckWR.Value) Then
mWR = 0
Else
mWR = CheckWR.Value
End If

If IsNull(CheckLS.Value) Then
mLS = 0
Else
mLS = CheckLS.Value
End If

If IsNull(CheckUP.Value) Then
mUp = 0
Else
mUp = CheckUP.Value
End If

If IsNull(CheckTT.Value) Then
mTTc = 0
mStatus = "Open"
Else
mTTc = CheckTT.Value
If mTTc <> 0 Then
mStatus = "Closed"
Else
mStatus = "Open"
End If
End If


mCO = Trim(Forms!frmUpdTT!txtCo.Value) & " "
mEmail = Trim(Forms!frmUpdTT!txtEmail.Value) & " "
mFirst = Trim(Forms!frmUpdTT!txtFirst.Value) & " "
mAdType = Trim(Forms!frmUpdTT!cmbAdType.Value) & " "
mAddr1 = Trim(Forms!frmUpdTT!txtAddr1.Value) & " "
mAddr2 = Trim(Forms!frmUpdTT!txtAddr2.Value) & " "
mCity = Trim(Forms!frmUpdTT!txtCity.Value) & " "
mSt = Trim(Forms!frmUpdTT!txtSt.Value) & " "
mZip = Trim(Forms!frmUpdTT!txtZip.Value) & " "
mReg = Trim(Forms!frmUpdTT!cmbReg.Value) & " "
mSS = Trim(Forms!frmUpdTT!txtSSNum.Value) & " "
mProp = Trim(Forms!frmUpdTT!txtProp.Value) & " "
mProMIS = Trim(Forms!frmUpdTT!txtVendID.Value) & " "

mTCode = "CA"

mTTBod = "Region: " & mReg & vbCrLf & "Property: " & mProp & vbCrLf &
"Vendor ID: " & mProMIS & vbCrLf & "Company: " & mCO & vbCrLf & "Last Name: "
& Forms!frmUpdTT!txtLast.Value & _
vbCrLf & "First Name: " & mFirst & vbCrLf & "Email: " & mEmail
& vbCrLf & "Address: " & _
mAddr1 & ", " & Trim(mAddr2) & ", " & mCity & ", " & mSt & ",
" & mZip & _
vbCrLf & "Phone: " & Forms!frmUpdTT!txtPhone.Value & vbCrLf & _
"Notes: " & vbCrLf & Text9.Value



' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True


'Update Trouble Ticket Record
strSQLIns = "UPDATE [trouble_ticket] Set Company = '" & mCO & "', Email
= '" & mEmail & "', Last = '" & Forms!frmUpdTT!txtLast.Value & "', fd_First =
'" & mFirst & "', Phone = '" & Forms!frmUpdTT!txtPhone.Value & "', AddrType
= '" & mAdType & "', vID = '" & mProMIS & "', Addr1 = '" & mAddr1 & "', Addr2
= '" & mAddr2 & "', City = '" & mCity & "', Zip = '" & mZip & "', Region = '"
& mReg & "', SSNum = '" & mSS & "', Property = '" & mProp & "', fd_Status =
'" & mStatus & "'" & _
"WHERE UnqID = " & Forms!frmUpdTT!Text30.Value


' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

'Update Sub Table Record

' strSQLIns = "INSERT INTO [PCPNR] (UnqId, UpdateOccur, DRDate,
CorProMIS, CheckAP, APDate, CheckLL, LLDate, CheckTT, TTDate, Notes) " & _
"SELECT " & mUnq & ",'1', #" & Now() & "#, '" & Combo4.Value
& "', " & mAP & ", #" & APDate.Value & "#, " & mLL & ", #" & LLDate.Value &
"#, " & mTTc & ", #" & TTDate.Value & "#, '" & Text149.Value & "'"


strSQLIns = "UPDATE [address_change] Set RChange = '" & txtChange.Value
& "', CheckFL = " & mFL & ", FLDate = #" & FLDate.Value & "#, CheckWR = " &
mWR & ", WRDate = #" & WRDate.Value & "#, CheckLS = " & mLS & ", LSDate = #"
& LSDate.Value & "#, CheckUP = " & mUp & ", fd_UPDate = #" & UPDate.Value &
"#, CheckTT = " & mTTc & ", TTDate = #" & TTDate.Value & "#, Addr1 = '" &
txtAddr1.Value & " ', Addr2 = '" & txtAddr2.Value & " ', Addr3 = '" &
txtAddr3.Value & " ', Addr4 = '" & txtAddr4.Value & " ', Notes = '" &
Text9.Value & "' " & _
"WHERE UnqID = " & Forms!frmUpdTT!Text30.Value

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)

' End If


End If


wrkCurrent.CommitTrans
fInTrans = False

Exit_Command11_Click:
' Set ttList = Forms!frmMain![Trouble Ticket subform]
' ttList.Requery
If mEdit = "N" Then
stDocName2 = "frmTTSave"
DoCmd.OpenForm stDocName2, , , stLinkCriteria2



'MsgBox "Trouble Ticket #" & Str(mUnq) & " entered"
Else
stDocName2 = "frmTTMod"
DoCmd.OpenForm stDocName2, , , stLinkCriteria2
End If

' DoCmd.Close
Exit Sub

Err_Command11_Click:
' If any errors rollback transaction
If fInTrans Then
wrkCurrent.Rollback
End If
MsgBox Err.Description & vbCrLf & "Please Give Admin Error Desc"
DoCmd.Quit
Resume Exit_Command11_Click


End Sub


Dirk Goldgar said:
I guess it's an unbound form, then. Please post the code in the
button's Click event procedure.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
sheldon said:
The memo field name is text9.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim db As Database
Dim strSQLIns As String
Dim qdfIns As QueryDef
Dim ttList As SubForm [...]

' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True

'Insert Sub Table Record

strSQLIns = "INSERT INTO [address_change] (UnqId, UpdateOccur,
RChange, DRDate, CheckFL, FLDate, CheckWR, WRDate, CheckLS, LSDate,
CheckUP, fd_UPDate, CheckTT, TTDate, Addr1, Addr2, Addr3, Addr4,
Notes) " & _ "SELECT " & mUnq & ",'1','" &
txtChange.Value & "', #" &
Now() & "#, " & mFL & ", #" & FLDate.Value & "#, " & mWR & ", #" &
WRDate.Value & "#, " & mLS & ", #" & LSDate.Value & "#, " & mUp & ",
#" & UPDate.Value & "#, " & mTTc & ", #" & TTDate.Value & "#, '" &
txtAddr1.Value & "', '" & txtAddr2.Value & "', '" & txtAddr3.Value &
"', '" & txtAddr4.Value & "', '" & Text9.Value & "'"

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)
Click to expand...
[...]

If the problem is really in the Text9 control, the most likely cause
would be a single-quote character in there, which would cause a
premature termination of the string literal you're building into your
SQL statement. Try changing this last part of the line:
"', '" & txtAddr4.Value & "', '" & Text9.Value & "'"
Click to expand...

to this:

"', '" & txtAddr4.Value & "', '" & _
Replace(Text9.Value, "'", "''") & "'"

That should replace any single-quote in the value with a pair of quotes,
which ought to be interpreted correctly.
 
Thank you. That was indeed the problem. I owe you BIG time.

Regards,

Sheldon...

Dirk Goldgar said:
sheldon said:
The memo field name is text9.

Private Sub Command11_Click()
On Error GoTo Err_Command11_Click
Dim wrkCurrent As Workspace
Dim fInTrans As Boolean
Dim db As Database
Dim strSQLIns As String
Dim qdfIns As QueryDef
Dim ttList As SubForm [...]

' Begin SQL Transaction

wrkCurrent.BeginTrans
fInTrans = True

'Insert Sub Table Record

strSQLIns = "INSERT INTO [address_change] (UnqId, UpdateOccur,
RChange, DRDate, CheckFL, FLDate, CheckWR, WRDate, CheckLS, LSDate,
CheckUP, fd_UPDate, CheckTT, TTDate, Addr1, Addr2, Addr3, Addr4,
Notes) " & _ "SELECT " & mUnq & ",'1','" &
txtChange.Value & "', #" &
Now() & "#, " & mFL & ", #" & FLDate.Value & "#, " & mWR & ", #" &
WRDate.Value & "#, " & mLS & ", #" & LSDate.Value & "#, " & mUp & ",
#" & UPDate.Value & "#, " & mTTc & ", #" & TTDate.Value & "#, '" &
txtAddr1.Value & "', '" & txtAddr2.Value & "', '" & txtAddr3.Value &
"', '" & txtAddr4.Value & "', '" & Text9.Value & "'"

' MsgBox strSQLIns

Set qdfIns = db.CreateQueryDef("", strSQLIns)

qdfIns.Execute (dbFailOnError)
Click to expand...
[...]

If the problem is really in the Text9 control, the most likely cause
would be a single-quote character in there, which would cause a
premature termination of the string literal you're building into your
SQL statement. Try changing this last part of the line:
"', '" & txtAddr4.Value & "', '" & Text9.Value & "'"
Click to expand...

to this:

"', '" & txtAddr4.Value & "', '" & _
Replace(Text9.Value, "'", "''") & "'"

That should replace any single-quote in the value with a pair of quotes,
which ought to be interpreted correctly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top