Using an expression in a SQL Statement using Visual Basic DoCmd.RunSQL

[bitech]

I need to connect the field... Title and Subject in an update sql statement. This is what I have.:

DoCmd.RunSQL "UPDATE [Phone Calls] SET [Phone Calls].[Date Responded] = [Forms]![Fax Form]![Date], [Phone Calls].Comments =[Forms]![Fax Form]![title]&" "&[Forms]![Fax Form]![Subject],[Phone Calls].Notes = -1,[Phone Calls].Solved = -1 WHERE ((([Phone Calls].[CO ID])=[Forms]![Fax Form]![CompanyID]));"

What this sql statement does is updates the table call Phone Calls, to reflect that this customer was responded to and about what. In this particular instance, this customer was sent a faxed so I want to automatically join the title field on the Fax Form, which the value of the title field is fax, with the subject field on the Fax Form which the value of the Subject line is in the control of the user. So if a user was to send a customer a fax about a golf event. The subject would read:

Subject:
Golf Outing

I would then want the notation in the Phone Calls table to read

Comments:
Fax: Golf Outing

It sounds pretty complicated, I'm sure if I design a query I can get it done, but I would like to know how to mark it up in Visual Basic. For some reason when I copy the code from the SQL view, into the form module, it gives me an error

 

[kathryn]

OK, post the code from the form module. I am pretty sure that you are getting an error because of the syntax of the SQL.

This may be a pretty easy fix. Kathryn

 

[bitech]

Private Sub Fax_Click()
DoCmd.RunSQL "DELETE * FROM [Fax Table]"
DoCmd.OpenQuery "Fax"
DoCmd.SetWarnings False
DoCmd.OpenQuery "(FAX) Append Notes"
DoCmd.RunSQL "UPDATE [Phone Calls] SET [Phone Calls].[Date Responded] = [Forms]![Fax Form]![Date], [Phone Calls].Comments =[Forms]![Fax Form]![title]&" "&[Forms]![Fax Form]![Subject],[Phone Calls].Notes = -1,[Phone Calls].Solved = -1 WHERE ((([Phone Calls].[CO ID])=[Forms]![Fax Form]![CompanyID]));"
DoCmd.SetWarnings True
DoCmd.close acForm, "Fax Form"
DoCmd.gotorecord acDataForm, "Response", acNext
DoCmd.gotorecord acDataForm, "Response", acPrevious
DoCmd.OpenReport "Fax Sheet", acViewNormal
End Sub

 

[kathryn]

Take a look at your SQL statement:

"UPDATE [Phone Calls] SET [Phone Calls].[Date Responded] = [Forms]![Fax Form]![Date], [Phone Calls].Comments =[Forms]![Fax Form]![title]&" "&[Forms]![Fax Form]![Subject],[Phone Calls].Notes = -1,[Phone Calls].Solved = -1 WHERE ((([Phone Calls].[CO ID])=[Forms]![Fax Form]![CompanyID]));"

You want the Form values substituted for the various Forms!...variables, so you have to use the following syntax:

"UPDATE [Phone Calls] SET [Phone Calls].[Date Responded] = " & [Forms]![Fax Form]![Date] & ", [Phone Calls].Comments = " & [Forms]![Fax Form]![title] & " " & [Forms]![Fax Form]![Subject]" & ",[Phone Calls].Notes = -1,[Phone Calls].Solved = -1 WHERE ((([Phone Calls].[CO ID])= " & [Forms]![Fax Form]![CompanyID] & &quot);"


This allows access to substitute the values for the variables. Does that make sense?
Kathryn

 

[bitech]

Thanx Kathryn but it didn't work, I keep getting the same message:

Compile error:

Expected: End of Statement

 

[kathryn]

Can you post the current code and show where you are getting the error message? Kathryn

 

[tlbroadbent]

Make sure string (text) values are delimited by quotes and date values by #.

"UPDATE [Phone Calls] SET
[Phone Calls].[Date Responded] = #" & [Forms]![Fax Form]![Date] & "#,
[Phone Calls].Comments = '" & [Forms]![Fax Form]![title] & " " & [Forms]![Fax Form]![Subject]" & "',
[Phone Calls].Notes = -1,
[Phone Calls].Solved = -1
WHERE ((([Phone Calls].[CO ID])= " & [Forms]![Fax Form]![CompanyID] & &quot);"

Add quotes around [Forms]![Fax Form]![CompanyID] in the where clause if [CO ID] is data type text. Terry
------------------------------------
People who don't take risks generally make about two big mistakes a year. People who do take risks generally make about two big mistakes a year. -Peter Drucker

 

[Guest_imported]

THIS IS THE CODE THAT I USED TO RETRIEVE DATA FROM MY TABLE AND IT WOULDN'T WORK.
(UPDATE [Forms]![Browse] SET [Forms]![Browse]![CAGE_CODE] = [1659-98].[CAGE_CODE] WHERE ((([Forms]![Browse]![CONTRACTOR]) = [1659-98].[CONTRACTOR])))
IT KEEPS SAYING THAT I HAVE A SYNTAX ERROR IN THE UPDATE STATEMENT. I WAS WONDERING IF YOU COULD HELP OR IF IT IS EVEN POSSIBLE TO UPDATE A FORM IN ACCESS 2000?

 

[tlbroadbent]

LHaynes,

In the future, please start a new thread for a new question.

You cannot update a form with a query. Queries update tables. Terry
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin