Macro for resetting

K

k11ngy

Hope someone can help? I have a database built whwereby there are yes/no
options on days of week. If the option is ticked/yes then at the end of each
week it will make calculations based on that week. I want a Macro to
actually reset the yes/no fileds to "Blank" so that user can then choose own
options at begining of each week rather than reset all of them manually? Is
there a way to do this as end of week routine?

thanks for help

Steve
 
S

Stefan_889_12

Hi Steve,

option 1: Build the query and save it. e.g
UPDATE tblDays SET tblDays.fldActive = No; query name is qryUpdateDays;
and then create a macro OpenQuery "qryUpdateDays"

option2:
Build macro using option RunSQL;
RunSQL "UPDATE tblDays SET tblDays.fldActive = No"
 
K

k11ngy

Hi Stefan, thanks, I am a novice user-Hope you can still assist?

I have checked database

The table in question, which is also a subform has following fields

Id-Monday-Tuesday etc, the days have the yes/no data type - Table is called
tbldeliveries

I understand? update queries to a certain degree

Do I create query based on tbldeliveries -Update query? what do I put in
update to filed and criteria? probably barking up worng tree as I dont know
where I set the criteria you mentioned - tbldays.fldactive=NO? or in my
instance tbldeliveries.fldactive=No

The Macro I have at the moment does everything I can imagine as in sets
amounts to ZERO ect, so I assume I can add that on at the end once I have
sorted this update section out?

Thanks again

Steve
 
S

Stefan_889_12

Hi Steve,

I created a table with 7 fields. table name tbldeliveries; field names Mon,
Tue,.... Sun, data type Yes/No. Have you the same structure?
and then I created following query:

UPDATE tbldeliveries SET tbldeliveries.Mon = No, tbldeliveries.Tue = No,
tbldeliveries.Wed = No, tbldeliveries.Thu = No, tbldeliveries.Fri = No,
tbldeliveries.Sat = No, tbldeliveries.Sun = No;

Stefan.
 
K

k11ngy

Hi Stefan

Thanks so much for help, it worked perfect and solved my problem

Appreciate your kind assistance

Steve
 
K

k11ngy

Quick question

In the update query boxes I actually did following

Criteria=Yes
Update to: No

I notice you mentioned "SET tbldeliveries.mon=No etc

Where would you actually write that condition? In the query design grid or???

Thanks again

Steve
 
S

Stefan_889_12

Hi Steve,

I believe you do not need any criteria.
Just fill in query grid Update to: No.

Stefan.
 
Top