Automation

[Guest]

I have a database that I would like to have the following happen.

1. Automatically Run an Append Query on the first Monday of every month.
Once it is done then I don't want it to run again.

I already have it set up to where when the DB opens the Switchboard
opens first. When the Switchboard opens I get a prompt "Today is the 5th.
Would you like to archive transactions?" This is set to run on the 5th of
every month; but everytime the Switchboard opens (which I close the database
and open several times a day) it asks the question. And also if it's the
weekend or I don't open the database on the 5th after that date I will not
get a prompt to run the query and perform the archive.

2. I would like for Access to also send via e-mail reports to my co-workers
on the first Tuesday of every month. I don't want to edit the message. I
just want it to done in the background without any interaction from me. Is
that possible? If not can it be done with minimal interaction from me. I
have the reports name and e-mail addresses. I use Lotus Notes as my e-mail
software.

Any help is greatly appreciated. Will be glad to provide more info if needed.

Tricia

 

[Joseph Meehan]

I have a database that I would like to have the following happen.

1. Automatically Run an Append Query on the first Monday of every
month.
Once it is done then I don't want it to run again.

I already have it set up to where when the DB opens the Switchboard
opens first. When the Switchboard opens I get a prompt "Today is the
5th. Would you like to archive transactions?" This is set to run on
the 5th of every month; but everytime the Switchboard opens (which I
close the database and open several times a day) it asks the
question. And also if it's the weekend or I don't open the database
on the 5th after that date I will not get a prompt to run the query
and perform the archive.

2. I would like for Access to also send via e-mail reports to my
co-workers on the first Tuesday of every month. I don't want to edit
the message. I just want it to done in the background without any
interaction from me. Is that possible? If not can it be done with
minimal interaction from me. I have the reports name and e-mail
addresses. I use Lotus Notes as my e-mail software.

Any help is greatly appreciated. Will be glad to provide more info
if needed.

Tricia

Without going into details, you will want to trigger the event by
comparing a stored value (equal to the last month) to the current month.
You will want something like

If stored moth = current month exit
If stored moth = current month-1 Execute the procedure
else Error Message

Make sure the procedure also updates the stored month to the current
month.

 

[Guest]

Joseph, my brain has been working over-time. I don't understand your
advice. Below is the code I am currently using. Please tell me what I am
doing wrong and how to fix it. I just guessed at what code to use.

Private Sub Form_Load()
'Verify this is the 5th day of the current month
If Day(Date) = 5 Then
If MsgBox("Today is the 5th of the month. Would you like to
archive all
transactions?", [vbYesNo]) = vbYes Then
DoCmd.OpenQuery "Test MTM Totals", , acReadOnly
End If
End If

End Sub


Thanks,

Tricia

 

[Joseph Meehan]

Joseph, my brain has been working over-time. I don't understand your
advice. Below is the code I am currently using. Please tell me what
I am doing wrong and how to fix it. I just guessed at what code to
use.

Private Sub Form_Load()
'Verify this is the 5th day of the current month
If Day(Date) = 5 Then
If MsgBox("Today is the 5th of the month. Would you like to
archive all
transactions?", [vbYesNo]) = vbYes Then
DoCmd.OpenQuery "Test MTM Totals", , acReadOnly
End If
End If

End Sub

I am not great at code, OK I am really slow. However without
looking anything up what does the acReadOnly do?

In any case you are triggering off the day of the month. I suggest that
you set a condition prior to that that checks to see if it has been archived
already this month. Any number of ways of doing this, maybe keep a last
archive data and see if it is the current month. This will make sure it
will run once a month. Next you want to check to see if it is the fifth or
later in the month If Day(date) >4 should do it.

 

[Guest]

Joseph, thanks for your help. But, I need help from someone who knows VBA
code for Access.

Thanks,
Tricia

Joseph, my brain has been working over-time. I don't understand your
advice. Below is the code I am currently using. Please tell me what
I am doing wrong and how to fix it. I just guessed at what code to
use.

Private Sub Form_Load()
'Verify this is the 5th day of the current month
If Day(Date) = 5 Then
If MsgBox("Today is the 5th of the month. Would you like to
archive all
transactions?", [vbYesNo]) = vbYes Then
DoCmd.OpenQuery "Test MTM Totals", , acReadOnly
End If
End If

End Sub

I am not great at code, OK I am really slow. However without
looking anything up what does the acReadOnly do?

In any case you are triggering off the day of the month. I suggest that
you set a condition prior to that that checks to see if it has been archived
already this month. Any number of ways of doing this, maybe keep a last
archive data and see if it is the current month. This will make sure it
will run once a month. Next you want to check to see if it is the fifth or
later in the month If Day(date) >4 should do it.

Thanks,

Tricia