Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Automation

Discussion in 'Information Technology' started by Tricia Young, Oct 28, 2005.

Tags:
  1. Tricia Young

    Tricia Young
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Kicking Marketing Automation into High Gear (NewsFactor) Interfaith Dialogues Mar 29, 2005

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Tricia Young wrote:
    >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.
    --
    Joseph Meehan

    Dia duit
     
  4. Tricia Young

    Tricia Young
    Expand Collapse
    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" wrote:

    > Tricia Young wrote:
    > >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.
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  5. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Tricia Young wrote:
    > 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
    >
    > "Joseph Meehan" wrote:
    >
    >> Tricia Young wrote:
    >> >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.
    >> --
    >> Joseph Meehan
    >>
    >> Dia duit


    --
    Joseph Meehan

    Dia duit
     
  6. Tricia Young

    Tricia Young
    Expand Collapse
    Guest

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

    Thanks,
    Tricia

    "Joseph Meehan" wrote:

    > Tricia Young wrote:
    > > 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
    > >
    > > "Joseph Meehan" wrote:
    > >
    > >> Tricia Young wrote:
    > >> >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.
    > >> --
    > >> Joseph Meehan
    > >>
    > >> Dia duit

    >
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     

Share This Page