troubleshooting Question

Modification to a Autoexec marco in Access 2010 ...

Avatar of cres1121
cres1121 asked on
Microsoft Access
3 Comments1 Solution84 ViewsLast Modified:
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)", "", "", "Maintenance Contract Expiration 90 days out", "Linus says Thank You!", True, ""
    ![Run_Status] = "No"
  End If
  If rs = "No" And dn > 8 Then
    ![Run_Status] = "Yes"
  End If
End With
End Function
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros