How to update graph every last day in a month!

[San]

Hi all

I have this sheet where I put data on how many units of this and that I
make a day. But every last day of the month I need to graph to auto
update...how to?

This is part of the code:

---------------------------------------------------------------------------------------------------------------------

Jan:

With Target

If .Column <> 1 Then GoTo Jan2
If DateNow <> DateJan Then GoTo Feb

Application.EnableEvents = False
Range("I6").Value = Application.Sum(Range(Target, Range("A5")))

Application.EnableEvents = True

End With



Jan2:

With Target

If .Column <> 2 Then GoTo InsertDate
If DateNow <> DateJan Then GoTo Feb2

Application.EnableEvents = False
Range("J6").Value = Application.Sum(Range(Target, Range("B5")))
Application.EnableEvents = True

End With

---------------------------------------------------------------------------------------------------------------------

- Column 1 and 2 are where I type in the amount of units I make.
- "I6" and "J6" are were they are stored.
- "InsertDate" is a third column were the date is.
- "DateJan" is the last date of the month where the data is sumed and
stored in the fields "I6" and "J6" and voila the graph apprears.
- "B5" is the first data field

But the big problem is that this only happens IF I type in anything
that particular date! If I don't make anything or it is a weekend then
im screwed! The graph just won't get updated...then the next month I
get the total number of units made for 2 month and not just 1 because
it didn't update the last month.


help thanks

San

 

[Arvin Meyer [MVP]]

So what you need is either, the database open all the time and polling the
clock to check if it's the last day (very inefficient) OR, when you open the
database check 2 fields in a 1 record table, and if it's the current month
and a boolean is true (or false depending on how you set it up) then check
to see if today is the last day of that month. If it is, or the code hasn't
run yet and the record is the previous month, run your code. Else quietly
allow the rest of the work to continue.

Does that psuedo code make sense?
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[San]

Think I follow a bit.


But basically I want a small code that checks if I have passed a
certain date, IF yes Then run the code(which updates the graph).

But If the date passes e.g. 31.01.06 then it updates, and when it
passes 28.02.06 then it updates both month...don't want that.

Arvin Meyer [MVP] skrev:

 

[Arvin Meyer [MVP]]

Actually you only need a single field. I've built a simple demo at:

http://www.accessmvp.com/Arvin/OncePerMonth.zip

This code will run once per month, every month, unless you skip a month
without opening the database even once.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[San]

Thanks man