docmd.runsql Update

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

Guest

Here is my code:

DoCmd.RunSQL "UPDATE tblPI SET tblPI.Email2 = Date() WHERE
tblPI.Pri_responsible=[txtRptTransfer] AND IsNull(Email2) = True And
User=[txtUser] And ([Email1] < Date() - [Time1]) ;"

It works perfectly except, it updates more than it is supposed to. The
problem is User=[txtUser]. It is not specifically updating based on user.
User is the field in the table and txtUser is a hidden text box on the form
that contains the unique username. Any ideas? Any help is appreciated.
Thanks
 
JAdams said:
Here is my code:

DoCmd.RunSQL "UPDATE tblPI SET tblPI.Email2 = Date() WHERE
tblPI.Pri_responsible=[txtRptTransfer] AND IsNull(Email2) = True And
User=[txtUser] And ([Email1] < Date() - [Time1]) ;"

It works perfectly except, it updates more than it is supposed to.
The problem is User=[txtUser]. It is not specifically updating based
on user. User is the field in the table and txtUser is a hidden text
box on the form that contains the unique username. Any ideas? Any
help is appreciated. Thanks
Click to expand...

Does it update all users who meet the other criteria, or only some of
them? I don't see what exactly wrong, but it occurs to me that, since
"User" is a reserved word, you may want to explicitly qualify it with
the table name when you refer to it; e.g.,

tblPI.User=[txtUser]

Aside from that, I don't see what might be wrong. Have you isolated the
execution of the SQL statement so that you can verify that "incorrect"
users are okay before the execution and modified afterward?
 
The way it is written, SQL expects [txtUser] to be a field that is available
to the query. (In fact, unless this is the case I'm surprised it's updating
anything at all!)

What you must do is insert the *value* from the textbox into your SQL string
and, assuming User is a a text field, you must also enclose the value in
quotes.

Also, as Dirk points out, User is a reserved word in Access (it's the name
of an object class) so it's best to avoid it as a field name. The
workaround is to enclose it in brackets.

Finally, is txtRptTransfer also a textbox? If so then it requires the same
treatment as txtUser.

Oh, and one more thing: I believe it's better practice to use the native SQL
"FieldName Is Null", than to call the VBA IsNull function.

So, the final result:

DoCmd.RunSQL "UPDATE tblPI SET tblPI.Email2 = Date() " _
& "WHERE tblPI.Pri_responsible='" & [txtRptTransfer] & "' " _
& "AND Email2 Is Null And [User]='" & [txtUser] & "' " _
& "And ([Email1] < Date() - [Time1]) ;"
 
Graham Mandeno said:
The way it is written, SQL expects [txtUser] to be a field that is
available to the query. (In fact, unless this is the case I'm
surprised it's updating anything at all!)
Click to expand...

Interestingly, Access seems to be able to figure this out, when you use
DoCmd.RunSQL. It goes ahead and substitutes the values from text boxes
on the active form. I'm sure, though, that there can be problems if the
names are confusing or ambiguous.
 
Hi Dirk

Really? I'm amazed! I tend to use only the Execute methods for this kind
of thing.

I understood RunSQL could parse Forms![FormName]![ControlName], but not just
the [ControlName] by itself.

I hate days when I don't learn something new, so thanks for the tip!
--
Cheers,
Graham

Dirk Goldgar said:
Graham Mandeno said:
The way it is written, SQL expects [txtUser] to be a field that is
available to the query. (In fact, unless this is the case I'm
surprised it's updating anything at all!)
Click to expand...

Interestingly, Access seems to be able to figure this out, when you use
DoCmd.RunSQL. It goes ahead and substitutes the values from text boxes
on the active form. I'm sure, though, that there can be problems if the
names are confusing or ambiguous.

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

(please reply to the newsgroup)
Click to expand...
 
Graham Mandeno said:
Hi Dirk

Really? I'm amazed! I tend to use only the Execute methods for this
kind of thing.
Click to expand...

Me, too.
I understood RunSQL could parse Forms![FormName]![ControlName], but
not just the [ControlName] by itself.
Click to expand...

I didn't believe it the first time I saw it, so I tried it out to see.
I still don't like it, because of the imprecision involved, but it does
work.
I hate days when I don't learn something new, so thanks for the tip!
Click to expand...

Glad to oblige, Graham. :-)
 
Back
Top