Command Button

[dbDamo]

Hi

I have a Command Button in my Database that when pressed runs a macro that runs a set of queries and then a number of automated emails to different recipients based on the results of the queries.

With a team of only a few people that perform this action, you would think that we would be able to communicate with eachother to let eachother know when we have sent the emails out. Unfortunately not.

I have suggested that as part of the action, the macro could also send an email to the members of my team informing them that the emails had been sent out, but they said that as the emails need to be sent out first thing, they usually open the Database as soon as they get in, before checking their emails.

Can anyone think of another way of letting the user know the action has been performed when they open the Database?

I thought about disabling the button for 24 hours after it had been pressed, but there could be a valid reason why the emails would need to be sent again and I'm not sure if that is even possible.

 

[boblarson]

Okay, create a table with one field and make it a date datatype (formatted Short Date).

Then, in the form's Load event put

If DLookup("[YourDateFieldNameHere]", "YourOneFieldTableNameHere") <> Date Then
   Me.YourButtonName.Enabled = True
Else
   Me.YourButtonName.Enabled = False
End If

And then at the end of the click event of the button which runs the stuff put:

CurrentDb.Execute "UPDATE [YourTableNameHere] SET [YourTableNameHere].[YourDateFieldHere] = Date();", dbFailOnError

Also, put this code in the button's click event at the top BEFORE running anything else (to check if the date is today's date so that someone who may have had the database open but not run when someone else ran the code then could not run the code):

If DLookup("[YourDateFieldNameHere]", "YourOneFieldTableNameHere") <> Date Then
   ...put your other code to run the macros, etc. here
Else
   MsgBox "This has already been run for today", vbInformation
End If

Then when you run the code, it will then update the date to today's date and when the others try to do it, they won't be able to.

 

[dbDamo]

Nice one bob, thanks very much!!

 

[dbDamo]

Ok, have added the code to my events.

Problem I am having is that the UPDATE query is not updating the date. Here is my code -

Private Sub Command13_Click()
On Error GoTo ErrorHandler
If DLookup("[LastRunDate]", "Lock_Daily_Email") <> Date Then
 
 
The rest of my code.....
 
 
Else
    MsgBox "Todays emails have already been sent", vbInformation
End If
 
CurrentDb.Execute "UPDATE [Lock_Daily_Email] SET [Lock_Daily_Email].[LastRunDate] = Date();", dbFailOnError
End Sub

Any idea why it is not updating?

 

[boblarson]

I would put a breakpoint on and then step through the code using F8 and see what values exist at any one point. It might not be doing what we think it should.

 

[dbDamo]

Tried everything I could think of and couldn't get it to work.

Ended up creating a macro which ran the SQL statement and used DoCmd.RunMacro in VB to call it.

Works nicely, thanks for the help Bob