We help IT Professionals succeed at work.

Modification to a Autoexec marco in Access 2010 ...

cres1121
cres1121 asked
on
80 Views
Last Modified: 2016-03-13
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I am going to try your suggestion just have to wait till March 1st to see if it worked...

Author

Commented:
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....
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.