Help with Invalid Arguement on SQL

[Alabaster2100]

I have a problem tracking program that has been working good for a couple of weeks now, until one of the techs pointed out that he could not save and close a record. I have an unbound form that the tech fills in a resolution type, resolution detail, their loginID and the date auto-set. the following is my save record command.

Private Sub Command119_Click()
' prompt for confirmation
Dim Msg, Style, Title, Response
Msg = "Save and Close Ticket Now?" ' Define message.
Style = vbYesNo + vbQuestion + vbDefaultButton1 ' Define buttons.
Title = "SAVE TICKET?" ' Define title.

' Display message.
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
' Save all values on form

Dim db As Database
Dim rs As Recordset
Dim mySQL As String

mySQL = "UPDATE tbl_Ticket SET tbl_Ticket.TechID = cmb_SelectTech,"
mySQL = mySQL & " tbl_Ticket.TicketClose = txt_CloseDate,"
mySQL = mySQL & " tbl_Ticket.ResolveID = cmb_resolveType,"
mySQL = mySQL & " tbl_Ticket.ResolveDetail = txt_ResolveDetail,"
mySQL = mySQL & " tbl_Ticket.[Close] =chk_CloseTicket"
mySQL = mySQL & " WHERE "
mySQL = mySQL & " (((tbl_Ticket.TicketID)="
mySQL = mySQL & cmb_SelectTicket.Value & &quot);"

DoCmd.SetWarnings (False)
DoCmd.RunSQL (mySQL)
DoCmd.SetWarnings (True)

' reset all values on form
....

When I tried to reproduce the error the contents of TXT_RESOLVEDETAIL is where things change. If a put approx 100 chars things work, but if I put in more I get an Invalid Arguement Error and the debug points to DoCMD.RunSQL.

Can Some one please help. I want he guys to supply detailed explinations of how the solved the problem but right now their on a 20 word limit :-(

TIA
HelpDesk@RWDI

 

[kathryn]

What is the format of the field in the SQL table? What is the field length? Kathryn

 

[Alabaster2100]

The field ProblemDetail is an access memo field, which the last time I checked was unlimited in length? Right?

 

[tlbroadbent]

It apears that the problem is in the length of the SQL command stored in MySQL and executed in the RunSQL Action. The maximum length of the SQL statement is 256 characters in RunSQL. Terry

http://members.home.net/tlbroadbent/index.htm

 

[Alabaster2100]

?????
What are my other options?????

 

[kathryn]

Don't panic. You can create a queryDef and assign the MySQL string to the QueryDef and run it.

I am at a training class with a lousy install of Access, so I can't get at the exact syntax, but if you search for QueryDef in your help files, you should see what I mean. If not, post again. Tomorrow I will be back at my own PC. Kathryn

 

[kathryn]

Don't panic. You can create a queryDef and assign the MySQL string to the QueryDef and run it.

I am at a training class with a lousy install of Access, so I can't get at the exact syntax, but if you search for QueryDef in your help files, you should see what I mean. If not, post again. Tomorrow I will be back at my own PC. Kathryn