SQL Statement within VB Code

[Guest]

Please check out this line and let me know where I'm screwing up the syntax.

rst2.Open "Select * FROM tblSuspenseList", cnn, adOpenForwardOnly,
adLockOptimistic
rst2.Update [NotificationDate] = Date() WHERE
rst2.NotificationDate<Date()-7 Or rst2.NotificationDate Is Null and
Left(rst2.AcctStation,2) & rst2.Unit = RRUU AND rst2.CAP = "4-SA"

I'm trying to avoid running an update query following a procedure that
prepares an email report. Updating the NotificationDate indicates the
report has been sent so the function doesn't occur twice in a weeks period.
This SQL is supposed to update the NotificationDate field with the current
date if it is null, or if it contains a date that is greater than 7 days old
- IF the RRUU = the current RRUU (left two characters of accountstation +
Unit number) and CAP = "4-SA"

Thanks for any help, I've not done ANYTHING in SQL type script before.

 

[Brendan Reynolds]

That's just not how the Update method of a recordset works, Bill. There are
two forms of the Update method of an ADO recordset. One takes no arguments,
and simply commits changes already made to the recordset. The other takes
either a single field name and a single value, or an array of field names
and an array of values, and assigns that value, or those values, to the
specified field name or names in the current record. Neither form of the
method accepts a SQL statement.

You say that you're attempting to do this to 'avoid running an update
query', but in fact executing an update query is exactly what this code is
attempting to do. The problem is that you can't do that using the Update
method of a recordset, you need to use the Execute method of a connection
object, e.g. CurrentProject.Connection.Execute "YourSQLStatementHere"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Guest]

I don't mind using the update query, but when I added the call to the query
at the end of my procedure, it acted like the form it was referencing in one
of the fields wasn't open. In other words I would get a dialogue box
requesting the data it should've gotten from the record on the open form.

That's just not how the Update method of a recordset works, Bill. There are
two forms of the Update method of an ADO recordset. One takes no arguments,
and simply commits changes already made to the recordset. The other takes
either a single field name and a single value, or an array of field names
and an array of values, and assigns that value, or those values, to the
specified field name or names in the current record. Neither form of the
method accepts a SQL statement.

You say that you're attempting to do this to 'avoid running an update
query', but in fact executing an update query is exactly what this code is
attempting to do. The problem is that you can't do that using the Update
method of a recordset, you need to use the Execute method of a connection
object, e.g. CurrentProject.Connection.Execute "YourSQLStatementHere"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Please check out this line and let me know where I'm screwing up the
syntax.

rst2.Open "Select * FROM tblSuspenseList", cnn, adOpenForwardOnly,
adLockOptimistic
rst2.Update [NotificationDate] = Date() WHERE
rst2.NotificationDate<Date()-7 Or rst2.NotificationDate Is Null and
Left(rst2.AcctStation,2) & rst2.Unit = RRUU AND rst2.CAP = "4-SA"

I'm trying to avoid running an update query following a procedure that
prepares an email report. Updating the NotificationDate indicates the
report has been sent so the function doesn't occur twice in a weeks
period.
This SQL is supposed to update the NotificationDate field with the current
date if it is null, or if it contains a date that is greater than 7 days
old
- IF the RRUU = the current RRUU (left two characters of accountstation +
Unit number) and CAP = "4-SA"

Thanks for any help, I've not done ANYTHING in SQL type script before.

 

[Brendan Reynolds]

If you're certain that the form was open, then the most likely cause of this
problem is a typo in the query, for example a slight spelling error in the
name of the form or of the control(s) on the form.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

I don't mind using the update query, but when I added the call to the query
at the end of my procedure, it acted like the form it was referencing in
one
of the fields wasn't open. In other words I would get a dialogue box
requesting the data it should've gotten from the record on the open form.

That's just not how the Update method of a recordset works, Bill. There
are
two forms of the Update method of an ADO recordset. One takes no
arguments,
and simply commits changes already made to the recordset. The other takes
either a single field name and a single value, or an array of field names
and an array of values, and assigns that value, or those values, to the
specified field name or names in the current record. Neither form of the
method accepts a SQL statement.

You say that you're attempting to do this to 'avoid running an update
query', but in fact executing an update query is exactly what this code
is
attempting to do. The problem is that you can't do that using the Update
method of a recordset, you need to use the Execute method of a connection
object, e.g. CurrentProject.Connection.Execute "YourSQLStatementHere"

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

Please check out this line and let me know where I'm screwing up the
syntax.

rst2.Open "Select * FROM tblSuspenseList", cnn,
adOpenForwardOnly,
adLockOptimistic
rst2.Update [NotificationDate] = Date() WHERE
rst2.NotificationDate<Date()-7 Or rst2.NotificationDate Is Null and
Left(rst2.AcctStation,2) & rst2.Unit = RRUU AND rst2.CAP = "4-SA"

I'm trying to avoid running an update query following a procedure that
prepares an email report. Updating the NotificationDate indicates the
report has been sent so the function doesn't occur twice in a weeks
period.
This SQL is supposed to update the NotificationDate field with the
current
date if it is null, or if it contains a date that is greater than 7
days
old
- IF the RRUU = the current RRUU (left two characters of accountstation
+
Unit number) and CAP = "4-SA"

Thanks for any help, I've not done ANYTHING in SQL type script before.