Marco to be run monthly the first Monday of the month. I have a autoexec macro but I have to edit the date to get it to run.

I created a Autoexec macro.  I need it to run the first Monday of the Month.  I also need it to only run once versus every time I long into access that day.  I have it doing it based off the date and I have to edit it each month.  

THe macro creates email that I need to click on send but okay I can live with that.. I just don't want to edit the the macro each month... (I can't ever take a vacation at the beginning of the month).
cres1121Asked:
Who is Participating?
 
FlysterCommented:
Better yet, try this:
Dim dbs As Database
Dim rst As Recordset
Dim d, rs As String
Dim dn As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenTable)

dt = Date 'Todays date

With rst

rs = !Run_Status
d = Format(dt, "ddd")
dn = Val(Format(dt, "d"))

  If rs = "Yes" And d = "Mon" And dn < 8 Then
    Call Emailing_Contract_Expiration_90_Day_Reports
  .Edit
    ![Run_Status] = "No"
    .Update
  End If
  
  If rs = "No" And dn > 8 Then
    .Edit
    ![Run_Status] = "Yes"
    .Update
  End If
  
End With

Open in new window

This will call the function you already have and you can keep the error handler.
0
 
FlysterCommented:
This seems to work. Create a table and name it tblFirstRun. It will have 2 fields, ID set as Autonumber and Run_Status set as text. Enter the first record in Run_Status as "Yes" (without quotes). Add this code to your macro:
Dim dbs As Database
Dim rst As Recordset
Dim d, rs As String
Dim dn As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenTable)

dt = Date 'Todays date

With rst

rs = !Run_Status
d = Format(dt, "ddd")
dn = Val(Format(dt, "d"))

  If rs = "Yes" And d = "Mon" And dn < 8 Then
    'Run
    'your
    'code
    'here
    
    .Edit
    ![Run_Status] = "No"
    .Update
  End If
  
  If rs = "No" And dn > 8 Then
    .Edit
    ![Run_Status] = "Yes"
    .Update
  End If
  
End With

Open in new window

When your macro runs, it looks at the first record in the table. If it is Yes(rs="Yes") and the day of the week is Monday(d="Mon") and the day is between 1 and 7 (dn<8) it will run your code (Run your code here). It will then change the first record in the table to "No" (.Edit,![Run_Status] = "No",.Update). If you run your macro a second time the code will not run as rs now = "No". Now the first time you run your macro after the 7th, it will change rs back to "Yes". It will remain Yes until the next first Monday. Let me know if the works for you.

Flyster
0
 
cres1121Author Commented:
I still am working on this
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FlysterCommented:
That's OK. This is new territory for me.
0
 
cres1121Author Commented:
Flyster

I converted my access marco to vb code using a tool that access had, I then just copied and pasted your text to my macro.  I don't know where my docmd.sendobjects should go...

Here is what it looks like

Option Compare Database
Dim dbs As Database
Dim rst As Recordset
Dim d, rs As String
Dim dn As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenTable)

dt = Date 'Todays date

With rst

rs = !Run_Status
d = Format(dt, "ddd")
dn = Val(Format(dt, "d"))

  If rs = "Yes" And d = "Mon" And dn < 8 Then
    'Run
    'your
    'code
    'here
   
    .Edit
    ![Run_Status] = "No"
    .Update
  End If
 
  If rs = "No" And dn > 8 Then
    .Edit
    ![Run_Status] = "Yes"
    .Update
  End If
 
End With
'------------------------------------------------------------
' Emailing_Contract_Expiration_90_Day_Reports
'
'------------------------------------------------------------
Function Emailing_Contract_Expiration_90_Day_Reports()
On Error GoTo Emailing_Contract_Expiration_90_Day_Reports_Err

    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Andy", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;Andy.Lin@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Edgar", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;Edgar.Romero@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Noelle", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com; Noelle.Kuehn@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days Out", "Linus says Thank You!", True, ""
    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Lisa", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;lisa.stone@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days out", "Linus says Thank You!", True, ""
    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Rusty", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;rusty.player@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days out", "", True, ""
    DoCmd.SendObject acQuery, "Customer Contract Expiration for web portal", "ExcelWorkbook(*.xlsx)", "michael@markiiisys.com", "rick.hedges@markiiisys.com", "", "Web Portal update", "Michael this needs to be opened in Excel and then saved as a csv file so it can then be uploaded to the web portal.  Thanks", True, ""
    DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Leslie", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com, leslie.hattig@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "", "Maintenance Contract Expiration 90 days out", True, ""


Emailing_Contract_Expiration_90_Day_Reports_Exit:
    Exit Function

Emailing_Contract_Expiration_90_Day_Reports_Err:
    MsgBox Error$
    Resume Emailing_Contract_Expiration_90_Day_Reports_Exit

End Function


Can you help with the order and syntex?
0
 
cres1121Author Commented:
Help?
0
 
FlysterCommented:
Try this in your Auto Macro
Dim dbs As Database
Dim rst As Recordset
Dim d, rs As String
Dim dn As Integer

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenTable)

dt = Date 'Todays date

With rst

rs = !Run_Status
d = Format(dt, "ddd")
dn = Val(Format(dt, "d"))

  If rs = "Yes" And d = "Mon" And dn < 8 Then
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Andy", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;Andy.Lin@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Edgar", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;Edgar.Romero@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Noelle", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com; Noelle.Kuehn@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days Out", "Linus says Thank You!", True, ""
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Lisa", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;lisa.stone@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days out", "Linus says Thank You!", True, ""
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Rusty", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com;rusty.player@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "Maintenance Contract Expiration 90 Days out", "", True, ""
        DoCmd.SendObject acQuery, "Customer Contract Expiration for web portal", "ExcelWorkbook(*.xlsx)", "michael@markiiisys.com", "rick.hedges@markiiisys.com", "", "Web Portal update", "Michael this needs to be opened in Excel and then saved as a csv file so it can then be uploaded to the web portal.  Thanks", True, ""
        DoCmd.SendObject acQuery, "Salesmen Contract Expiration for Leslie", "Excel97-Excel2003Workbook(*.xls)", "rick.hedges@markiiisys.com, leslie.hattig@markiiisys.com", "jean.mcgowan@markiiisys.com", "", "", "Maintenance Contract Expiration 90 days out", True, ""
    .Edit
    ![Run_Status] = "No"
    .Update
  End If
  
  If rs = "No" And dn > 8 Then
    .Edit
    ![Run_Status] = "Yes"
    .Update
  End If
  
End With

Open in new window

0
 
cres1121Author Commented:
Okay I will show how lost I am .. Make this the autoexec macro?
0
 
FlysterCommented:
Yes. Place the code above in your auto exec macro. Just that. Every time you open the database, it will run. The first time you open the database on the first Monday of the month, function "Emailing_Contract_Expiration_90_Day_Reports" will run. We'll get this thing working for you yet!
0
 
cres1121Author Commented:
Thank you for your patience.   I will play with this now.
0
 
cres1121Author Commented:
Okay I think I figured out where it should all go but I had to do the one with the reports listed out.  Because I converted the autoexec macro to code and it could not find that code.  So will a module called autoexec run like a macro called autoexec?
0
 
FlysterCommented:
I do not believe there is such a thing as an autoexec module. You should be able to keep your autoexec macro. What the code I provide above does is to look in table tblFirstRun. If field RunStatus is Yes and it's the 1st Monday, it executes this line of code:
Call Emailing_Contract_Expiration_90_Day_Reports
What that is doing is going to your Emailing_Contract_Expiration_90_Day_Reports Function and running that. The code then changes the RunStatus to No, so if you open the program again it will not run your function. The first time you open your program on the 9th or thereafter, It changes RunStatus back to Yes. You can open the program as many times as you want and it will not run your code again until it's the 1st Monday again.
0
 
cres1121Author Commented:
Okay well April 1 is coming and I will test it.  I had to create a macro called autoexec to run the module that you made for me..    Lets hope it does not fool me.  I so appreciate your patience and help.  I learned allot.  I wish you were here to help with my other problem.  Thanks
0
 
FlysterCommented:
I'll keep my fingers crossed for you. Good luck!
0
 
cres1121Author Commented:
This person was patient with me and helped me find a solution.   I don't know vb and instead of forcing me to write out each line of code they helped me with a design with my stuff so I could understand what it was suppose to do.  It went off today just as planned once exactly as I needed.  I so do appreciate the patience and the knowledge!  I hope they will help me in the future.  I am so thankful. Yes, I want to learn it but sometimes I just need to have an answer.  This will free me up to worry about other things.
0
 
FlysterCommented:
Thanks for the kind words. I'm happy that this code worked for you.
0
 
cres1121Author Commented:
Now can you help me with another problem....  Thanks again
0
 
cres1121Author Commented:
One more question I want to move my table to a split database.  When I moved tblFirstRun I got a compile error.  What would the rst be for a table that is in a folder on z:\service database and the database is name Servicedatabase_be.
0
 
FlysterCommented:
Shot in the dark here, try changing 2nd line of code to:

Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenDynaset)
0
 
cres1121Author Commented:
Well that was a good shot because it was right on the money.
0
 
FlysterCommented:
Glad it worked out!
0
 
cres1121Author Commented:
Can you help me one more time with this?  Now they would like it to run the first work day of the month?  So before we were running it the first Monday now we would like it to run the first workday that they log into the access program.  One time a month only...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.