Autoexec help

[Guest]

I use the Autoexec on a marco that goes out to 5 database and collects data.
I usually do this the first thing in the morning so that when others come in
and use the database the do not have to wait the 17 mins that it take for the
data to be collected, i then change the marco's name so that when the others
open the database it will do not the data collection.. What i would like to
know if it is possible to set a marco so that if it has been run once in a
day that it will bypass the collection and open the database but run again
the next day?

 

[Guest]

I don't know of a way to get a macro to know whether it has ran already or
not (maybe you could do something with conditions in the macro line) but
here's another way of doing it...

The way I do this (and I'm assuming you are using a front end-back end
setup) is to have 1 database that is responsible for updating the back end,
then the front end database is what the other uses will log into to access
the data in the back end database.

So...on a timer is set to go off once a day, the 1st database fires off
going into the backend database and bringing all the data in for you then
shuts itself down afterwards. The users then come in and go through the
frontend database to access the data you just finished updating. Then the
timer goes off again the next day and the same thing happens.

 

[Douglas J. Steele]

Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.

 

[Steve Schapel]

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

 

[Guest]

Thanks!! Steve works great!

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

--
Steve Schapel, Microsoft Access MVP

Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.

 

[Guest]

I'm trying to accomplish the same effect, but I only want the macro to run
once each calendar week (not once every 7 days, but rather the first time
each calendar week the database is opened, whether that's on a Monday or a
Thursday). Right now, my table contains the number of the current week (for
example, this week is 37) and is updated when the AutoExec triggers an update
query to send this to the table:
Format(Now(),"ww")

Coming up with the condition to put in the AutoExec macro is causing me
fits, though. My current expression can't be parsed, and I'm not sure what to
do to fix it.
DLookup("[WeekUpdated]","tblUpdated")<Format(Now(),"ww"))

Of course, even if I could get this expression to work, it's going to freak
out at the new year when the week number reverts to 1.

Any suggestions on how to accomplish my goal here of having my AutoExec
trigger a macro only the first time the database is opened each calendar week?

Many thanks in advance,
j

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

--
Steve Schapel, Microsoft Access MVP

Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.

 

[tina]

it should work, as long as the week number is being stored as a number, not
as text - and as long as the fieldname and tablename in the DLookup are
spelled correctly. if all the preceding is correct, you might try changing
Now() to Date() - though i wouldn't think that would make a difference,
really. if it *still* doesn't work, suggest you try storing a date value (as
Date/Time data type) in the table rather than a week number, and comparing
that, as

DatePart("ww", DLookup("WeekUpdated", "tblUpdated")) <> DatePart("ww",
Date())

changing from < to <> takes care of the "new year" issue, also.

hth

I'm trying to accomplish the same effect, but I only want the macro to run
once each calendar week (not once every 7 days, but rather the first time
each calendar week the database is opened, whether that's on a Monday or a
Thursday). Right now, my table contains the number of the current week (for
example, this week is 37) and is updated when the AutoExec triggers an update
query to send this to the table:
Format(Now(),"ww")

Coming up with the condition to put in the AutoExec macro is causing me
fits, though. My current expression can't be parsed, and I'm not sure what to
do to fix it.
DLookup("[WeekUpdated]","tblUpdated")<Format(Now(),"ww"))

Of course, even if I could get this expression to work, it's going to freak
out at the new year when the week number reverts to 1.

Any suggestions on how to accomplish my goal here of having my AutoExec
trigger a macro only the first time the database is opened each calendar week?

Many thanks in advance,
j

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

--
Steve Schapel, Microsoft Access MVP

Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.

 

[bymarce]

I've been trying to get a macro to run no more than once a week also. After
reading Steve's post to the first question in this post and put -7 on the end
to give
DLookup("[TheDateField]","YourTable")<Date()-7 as the condition for my
macro. The last step in my macro is to open an update query that puts todays
date in "thedatefield" of my table every time it runs. I set the macro to
run in the "on Open" property of my switch board.

I'm trying to accomplish the same effect, but I only want the macro to run
once each calendar week (not once every 7 days, but rather the first time
each calendar week the database is opened, whether that's on a Monday or a
Thursday). Right now, my table contains the number of the current week (for
example, this week is 37) and is updated when the AutoExec triggers an update
query to send this to the table:
Format(Now(),"ww")

Coming up with the condition to put in the AutoExec macro is causing me
fits, though. My current expression can't be parsed, and I'm not sure what to
do to fix it.
DLookup("[WeekUpdated]","tblUpdated")<Format(Now(),"ww"))

Of course, even if I could get this expression to work, it's going to freak
out at the new year when the week number reverts to 1.

Any suggestions on how to accomplish my goal here of having my AutoExec
trigger a macro only the first time the database is opened each calendar week?

Many thanks in advance,
j

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

--
Steve Schapel, Microsoft Access MVP

Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.

 

[jkatj]

It took me an eternity to get back to this project, but now that I finally
have, your solution seems to have done the trick. I actually needed the
opposite of it, but switching the <> to = was something even I could sort
out. Many thanks!

jkatj

it should work, as long as the week number is being stored as a number, not
as text - and as long as the fieldname and tablename in the DLookup are
spelled correctly. if all the preceding is correct, you might try changing
Now() to Date() - though i wouldn't think that would make a difference,
really. if it *still* doesn't work, suggest you try storing a date value (as
Date/Time data type) in the table rather than a week number, and comparing
that, as

DatePart("ww", DLookup("WeekUpdated", "tblUpdated")) <> DatePart("ww",
Date())

changing from < to <> takes care of the "new year" issue, also.

hth

I'm trying to accomplish the same effect, but I only want the macro to run
once each calendar week (not once every 7 days, but rather the first time
each calendar week the database is opened, whether that's on a Monday or a
Thursday). Right now, my table contains the number of the current week (for
example, this week is 37) and is updated when the AutoExec triggers an update
query to send this to the table:
Format(Now(),"ww")

Coming up with the condition to put in the AutoExec macro is causing me
fits, though. My current expression can't be parsed, and I'm not sure what to
do to fix it.
DLookup("[WeekUpdated]","tblUpdated")<Format(Now(),"ww"))

Of course, even if I could get this expression to work, it's going to freak
out at the new year when the week number reverts to 1.

Any suggestions on how to accomplish my goal here of having my AutoExec
trigger a macro only the first time the database is opened each calendar week?

Many thanks in advance,
j

Yes, the same thing is possible with a macro, obviously. As Doug
suggested, make a table with a Date/Time field to hold the latest date
your macro was run. Make an Update Query to set the value of this date
field in the table to Date() and use an OpenQuery action in your macro
to run this update. Use a condition in your macro, the equivalent of...
DLookup("[TheDateField]","YourTable")<Date()
.... so the macro will only run if it hasn't already happened today.

--
Steve Schapel, Microsoft Access MVP


Douglas J. Steele wrote:
Not sure whether it's possible with a macro (I never use them...), but in
VBA, I'd add a table that contains a date field. Check whether or not
there's an entry in that table with today's date. If there isn't, run the
routine, and append a new row with to the table with today's date. If there
is an entry with today's date, you know you've already run the routine.