Updating a Table using RUNSQL statement...

  • Thread starter Thread starter Ron
  • Start date Start date
R

Ron

Hello,

Can anyone help me understand why access does not like
this statement?

If [Forms]![Phone Observation Maintenance].[ErrPoint]
= 6 Then
SQLStr1 = "UPDATE Call Error Xref Table (ErrPoint)"
SQLStr2 = "SET ErrPoint = 1"
SQLStr3 = "WHERE [Call Error Xref Table].[CallID] ="
SQLStr4 = [Forms]![Phone Observation Maintenance].
[CallID] & ");"
SQLStr5 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4
DoCmd.RunSQL SQLStr5
End If

All I am trying to do is update the field "ErrPoint" to a
new value. I'm trying to update the specific record where
the form's CallID field is equal to the table's CallID
field so that it does not put in an extra record. What is
wrong with this statement?

Your assistance is greatly appreciated.

Thank you!

Ron
 
A reply is posted in another group.

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<

Ron said:
Hello,

Can anyone help me understand why access does not like
this statement?

If [Forms]![Phone Observation Maintenance].[ErrPoint]
= 6 Then
SQLStr1 = "UPDATE Call Error Xref Table (ErrPoint)"
SQLStr2 = "SET ErrPoint = 1"
SQLStr3 = "WHERE [Call Error Xref Table].[CallID] ="
SQLStr4 = [Forms]![Phone Observation Maintenance].
[CallID] & ");"
SQLStr5 = SQLStr1 & SQLStr2 & SQLStr3 & SQLStr4
DoCmd.RunSQL SQLStr5
End If

All I am trying to do is update the field "ErrPoint" to a
new value. I'm trying to update the specific record where
the form's CallID field is equal to the table's CallID
field so that it does not put in an extra record. What is
wrong with this statement?

Your assistance is greatly appreciated.

Thank you!

Ron
Click to expand...
 
Yup,
Your query looks like this

UPDATE Call Error Xref Table (ErrPoint)SET ErrPoint =
1WHERE [Call Error Xref Table].[CallID] =21);

You need to embed some spaces to make it readable
You also have a trailing parenthesis you don't need
 
You also have a trailing parenthesis you don't need

Good catch. I missed that one. <s>
 
My thanks to both Bruce and Chris for this exteremely
important piece of information I was missing.

Would either of you know a good resource (book, website)
that deals with these "RUNSQL" commands? This has been
a pain in the "..." trying to get the syntax to work with
no examples to go on.

Thank you both!

Ron
 
My thanks to both Bruce and Chris for this exteremely
important piece of information I was missing.

Would either of you know a good resource (book, website)
that deals with these "RUNSQL" commands? This has been
a pain in the "..." trying to get the syntax to work with
no examples to go on.
Click to expand...

Reply posted in m.p.a.setupconfig. Please don't post independent copies of the
same questions to multiple newsgroups. If you feel that you must post to more
than one group (and such a need is **rare**) please "cross-post" to multiple
groups _at once_ (I'm not sure how the web interface deals with this, but most
news readers include the capability to select multiple groups to include in the
"To:" or "Newsgroups:" box) so that any response to your question in one group
will also appear in the other groups.

Thanks.
 
Back
Top