Avatar of cres1121
cres1121

asked on 

Modification to a Autoexec marco in Access 2010 ...

I asked and got answered a question about macros.  I initially wanted a macro to run every month only once the first monday of the month.  Well they have asked for it to be changed so that it runs the 1st working day of the month.  This would also run only once a month and by one user...  

This is the macro/Module we have been running that a expect "helped" me write (they get all the credit) .  Can anyone help me with the modifications?


'------------------------------------------------------------
' salemenreports
'
'------------------------------------------------------------
Function Salemenreport()

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)
 Set rst = dbs.OpenRecordset("tblFirstRun", dbOpenDynaset)

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", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
       
   
    .Edit
    ![Run_Status] = "No"
    .Update
  End If
 
  If rs = "No" And dn > 8 Then
    .Edit
    ![Run_Status] = "Yes"
    .Update
  End If
 
End With
End Function
Microsoft Access

Avatar of undefined
Last Comment
cres1121
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of cres1121
cres1121

ASKER

I am going to try your suggestion just have to wait till March 1st to see if it worked...
Avatar of cres1121
cres1121

ASKER

I loved that they gave me the code.  I would like to learn it but lets face it I am not going to and will get frustrated when trying to do it.  It ran just as designed.  I will see if it will happen again April....
Microsoft Access
Microsoft Access

Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.

226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo