Error 3075 (SQL Update Statement)

[mindlike]

Seen a few other posts on this error but none that address issue
specifically.

Using this line to update data in Details Table. As many others report,
when I insert quotes, either a double or a single around words, I get the
standard 3075 syntax error. I only need to be able to insert quotes in the
Me.txtDescription field of the form. ANy help on changing code to accomodate
quotes would be greatly appreciated.

Additionally, I've truncated this line of code by leaving out 24 other
fields that update on this form. Any idea on how to write a looping
structure to do updating instead of having to list out each field
individually? I just can find a site that elaborates--mabye not even
possible with an sql statement.


dbs.Execute "UPDATE Details SET CategoryName = """ & Me.cboCategory & """,
Description = """ & Me.txtDescription & """ " & "WHERE DetailsID = " &
Me.txtDetailsID & ""

 

[Stefan Hoffmann]

hi,

I only need to be able to insert quotes in the
Me.txtDescription field of the form. ANy help on changing code to accomodate
quotes would be greatly appreciated.

On Local Error GoTo LocalError

Dim SQL as String

SQL = "UPDATE Details " & _
"SET CategoryName = " & SQLQuote(cboCategory.Value) & ", " & _
" Description = " & SQLQuote(txtDescription.Value) & " " & _
"WHERE DetailsID = " & txtDetailsID.Value

dbs.Execute SQL, dbFailOnError

Exit Sub

LocalError:
MsgBox Err.Number & " " & Err.Description & vbCrLf & _
"SQL: " & vbCrLf & _
SQL

with

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = Replace(AString, ADelimiter, ADelimiter & ADelimiter)

End Function

Additionally, I've truncated this line of code by leaving out 24 other
fields that update on this form. Any idea on how to write a looping
structure to do updating instead of having to list out each field
individually?

Huh? I'm not sure that I understand you ...


mfG
--> stefan <--

 

[mindlike]

Hello Stefan,

I'm sorry, but I was probably not clear enough in my first email. The
coding module appears to assume I'm sending the data from an Access form into
an SQL environment of some sort. I'm actually just dumping data from an
Access (v2003) form in Denver to another Access table in Pheonix, where our
tables of data are stored. I use the SQL to speed up the delivery of
information so I can use Microsoft Jet Database only and not get bogged down
with the Access error checking stuff, etc.

Anyway the code Im using to update is:

Set dbs = OpenDatabase("L:\Member\EventData.mdb")

dbs.Execute "UPDATE Details SET CategoryName = """ & Me.cboCategory & """,
Description = """ & Me.txtDescription & """ " & "WHERE DetailsID = " &
Me.txtDetailsID & ""

dbs.Close
MsgBox "RECORD " & Me.txtDetailsID & " UPDATED IN PHEONIX"

the statement will accept an apostrophe in the form field Me.txtDescription,
but not double quotes. As I understand quotes are some kind of delimiter for
the SQL language, so when used in text a few modifications need to be made.
I tried a few modifications based on what you originally sent me, but still
get the same dead end.

Also, the "looping" structure I mentioned in the first email. I mean,
instead of listing out each textbox or combobox or checklist box on my form
(about25) is there not a way to loop through each one with the SQL...So just
some aircode to give you the idea

Dim ctl As Access.Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = Null
End If
Next ctl

This loop identifies each text box on the form and nulls the value in each
box. I use it during the load event of the form. Is there a similar way to
loop through the textbox's and update the data in them into Pheonix...just
trying to avoid listing each control individually with the big hairy SQL
update statement.

Thanks in advance.

 

[Stefan Hoffmann]

hi,

dbs.Execute "UPDATE Details SET CategoryName = """ & Me.cboCategory & """,
Description = """ & Me.txtDescription & """ " & "WHERE DetailsID = " &
Me.txtDetailsID & ""

Error 3075 normally means that the SQL statement is not formally correct.

As I understand quotes are some kind of delimiter for
the SQL language, so when used in text a few modifications need to be made.

Yes, when you assemble a SQL statement by code, then you need to take
care of what you get.

I tried a few modifications based on what you originally sent me, but still
get the same dead end.

Use the SQL variable and use

MsgBox SQL

or

Debug.Print SQL ' output in the immediate window

to display the statement. Please post it with your complete procedure.

This loop identifies each text box on the form and nulls the value in each
box. I use it during the load event of the form. Is there a similar way to
loop through the textbox's and update the data in them into Pheonix...just
trying to avoid listing each control individually with the big hairy SQL
update statement.

Okay, i see.


mfG
--> stefan <--