VBA Code

[Chuck216]

Hi

I need help with some VBA code. I want to connect code to a command button
that checks the fields [date] for the current date, [classification] for
“weather†and [Safety] is null in “tblDowntime†if it finds any rows that
fit this criteria then I want it to update those rows with the Time() in
[uptime] and “Auto†in [Safety]. This is all done in the background on the
table and not in a form.

I hope my question makes sense. Thanks in advance for any help with this.
Chuck

 

[Dale Fye]

First, make a copy of your table.

Update tblDowntime
SET [uptime] = time(), [Safety] = "Auto"
WHERE [DateField] = Date()
AND [Classification] = "weather"
AND [Safety] IS NULL

I think this is what you are looking for.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Bryan in Bakersfield]

Chuck -

Since you say that you want to run it on the table, I'm assuming that you
want to run it on all records and not just the record on the form.

You can add this code to the On Click event of your command button to run
the query, though you could just as easily make a query and run the query
instead.


Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto',
tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

This will run an Update query to update all records and set the field
[Safety] to Auto and [Uptime] to Time() if the [Date] is today, [Safety] is
null and [classification] = Weather.


Bryan

 

[fredg]

Hi

I need help with some VBA code. I want to connect code to a command button
that checks the fields [date] for the current date, [classification] for
´weather¡ and [Safety] is null in ´tblDowntime¡ if it finds any rows that
fit this criteria then I want it to update those rows with the Time() in
[uptime] and ´Auto¡ in [Safety]. This is all done in the background on the
table and not in a form.

I hope my question makes sense. Thanks in advance for any help with this.
Chuck

In addition to the Update SQL that others have given you, if you
really do have a field named "Date" then ....
Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

Change the field named Date to something else, i.e. ADate, EventDate,
etc.

 

[Chuck216]

Bryan

yes I do want to run it on all records.

I tried your code and I get the error (expcted end of statement) on
tblDowntime in the 2nd line of the code. also all the code comes in red
when I paste it into my app.

Thank you

Chuck -

Since you say that you want to run it on the table, I'm assuming that you
want to run it on all records and not just the record on the form.

You can add this code to the On Click event of your command button to run
the query, though you could just as easily make a query and run the query
instead.


Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto',
tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

This will run an Update query to update all records and set the field
[Safety] to Auto and [Uptime] to Time() if the [Date] is today, [Safety] is
null and [classification] = Weather.


Bryan

Hi

I need help with some VBA code. I want to connect code to a command button
that checks the fields [date] for the current date, [classification] for
“weather†and [Safety] is null in “tblDowntime†if it finds any rows that
fit this criteria then I want it to update those rows with the Time() in
[uptime] and “Auto†in [Safety]. This is all done in the background on the
table and not in a form.

I hope my question makes sense. Thanks in advance for any help with this.
Chuck

 

[Bryan in Bakersfield]

Sorry Chuck -

I didn't realize that the line got cut off and wrapped around. This window
to type answers in is too small.

To fit in here, it should have read:

Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto'," & _
" tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

The strSQL is actually one line of code that takes up 5 lines here. The "
& _ " is used to break it up to smaller lines in VB.



Bryan

Bryan

yes I do want to run it on all records.

I tried your code and I get the error (expcted end of statement) on
tblDowntime in the 2nd line of the code. also all the code comes in red
when I paste it into my app.

Thank you

Chuck -

Since you say that you want to run it on the table, I'm assuming that you
want to run it on all records and not just the record on the form.

You can add this code to the On Click event of your command button to run
the query, though you could just as easily make a query and run the query
instead.


Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto',
tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

This will run an Update query to update all records and set the field
[Safety] to Auto and [Uptime] to Time() if the [Date] is today, [Safety] is
null and [classification] = Weather.


Bryan

Hi

I need help with some VBA code. I want to connect code to a command button
that checks the fields [date] for the current date, [classification] for
“weather†and [Safety] is null in “tblDowntime†if it finds any rows that
fit this criteria then I want it to update those rows with the Time() in
[uptime] and “Auto†in [Safety]. This is all done in the background on the
table and not in a form.

I hope my question makes sense. Thanks in advance for any help with this.
Chuck

 

[Chuck216]

Bryan

Thank you it works great!

Sorry Chuck -

I didn't realize that the line got cut off and wrapped around. This window
to type answers in is too small.

To fit in here, it should have read:

Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto'," & _
" tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

The strSQL is actually one line of code that takes up 5 lines here. The "
& _ " is used to break it up to smaller lines in VB.



Bryan

Bryan

yes I do want to run it on all records.

I tried your code and I get the error (expcted end of statement) on
tblDowntime in the 2nd line of the code. also all the code comes in red
when I paste it into my app.

Thank you

Chuck -

Since you say that you want to run it on the table, I'm assuming that you
want to run it on all records and not just the record on the form.

You can add this code to the On Click event of your command button to run
the query, though you could just as easily make a query and run the query
instead.


Dim strSQL As String

strSQL = "UPDATE tblDowntime SET tblDowntime.Safety = 'Auto',
tblDowntime.uptime = #" & Time() & _
"# WHERE (((tblDowntime.Date)=#" & Date & " #) AND " & _
"((tblDowntime.classification)='Weather') AND " & _
"((tblDowntime.Safety) Is Null));"

DoCmd.RunSQL

This will run an Update query to update all records and set the field
[Safety] to Auto and [Uptime] to Time() if the [Date] is today, [Safety] is
null and [classification] = Weather.


Bryan




:

Hi

I need help with some VBA code. I want to connect code to a command button
that checks the fields [date] for the current date, [classification] for
“weather†and [Safety] is null in “tblDowntime†if it finds any rows that
fit this criteria then I want it to update those rows with the Time() in
[uptime] and “Auto†in [Safety]. This is all done in the background on the
table and not in a form.

I hope my question makes sense. Thanks in advance for any help with this.
Chuck