# Solved: Access 2007. Can it alert me when a date is due?



## tmetford (Apr 8, 2008)

Hi,
My database contains date fields that indicate when a particular action needs to be executed (e.g. send a follow up letter to the sales call).
Can Access 2007 be configured to alert me when that date arrives?
Thank you.
Tim


----------



## OBP (Mar 8, 2005)

Yes providing the database is open.


----------



## tmetford (Apr 8, 2008)

Okay, that's good. Would you tell me how that can be done please?
I am gaining knowledge about using MS Access 2007 and am reasonably adept re general computing skills.
Does Access have such a "module" for generating an alert already built in, or will I need to create a macro or use VBA script to do this? (both of these are new things to me)
Thank you for your help.
Tim


----------



## OBP (Mar 8, 2005)

Tim, do you want the Alert on the Day in the Field or the Day before?
First of all you create a query for therecords and in the "alert" field you put in the Criteria row
Date()
for the same day, or
Date() -1 
for the day before.
You then create an Autoexec macro (that runs when the database is opened) to open a "hidden" form based on the query
On that form you have some simple VBA in the "On Load" or "On Current" event procedure like 
If Not Isnull(me.recordcount) or me.recordcount > 0 then
me.Visible = True
msgbox "the Records shown require action today"
end if


----------



## tmetford (Apr 8, 2008)

Thank you, I've done what you suggested and it's beginning to work which is great.
I can get a form (or report) to show the requisite fields that tell me what action is now needed when that form is opened (loaded).
How do I get this form to open by default when the database itself is opened at the beginning of the working day?
Also, it'd be useful to have the script search for all records that possess a date field that's within a range; say, between 3 days ago and 1 day in the future (a total range of 4days, just so that any recently missed actions are flagged up).
What would be the expression/s to insert into the script you suggested, to enable this?
Thanks.
Tim
Tim


----------



## OBP (Mar 8, 2005)

First of all the Form can be opened using the Autoexec macro that I mentioned.
It can also be opened using the Main Menu>Tools>Startup

To have a range of dates in the queries Criteria you use 
Between Date() - 3 And Date() + 1


----------



## mnm123 (May 6, 2009)

Hi, I read the threads above and I would appreciate some more information. I am working with my coworker on access 2007 and we need to set up the database for automatic alerts on specific date fields. We are not as familiare with access as we would like so I was wondering if anyone could in detail explain how we would set the automatic alerts up. I know its a lot to ask but I would appreciate it. Thanks so much.....


----------



## OBP (Mar 8, 2005)

I can do that for you tomorrow.


----------



## mnm123 (May 6, 2009)

Thank you I appreciate it. Will it actually alert the person as soon as the y open the database?


----------



## OBP (Mar 8, 2005)

Yes it will do that, it could even email them if you wanted it to.


----------



## mnm123 (May 6, 2009)

Wow that would be great. If you could give me instructions how I could get the database set up and have an email sent that would be great.


----------



## OBP (Mar 8, 2005)

First of all follow my instructions in Post #4, to see if you can get part working.
If you are having real problems you can email or post a zipped copy of the database and I will set it up. But it must be in Access 2003 format and not contain any personal data.


----------



## mnm123 (May 6, 2009)

We actually did step 4 and tested it out. A message comes up but only if you go into that particular form on the database. We set up code in the vbs box. Had to play around with the code a bit so that it would recognize that the date had expired and it was due for action.


----------



## OBP (Mar 8, 2005)

Did you open the form using an Autoexec macro when the database opens?


----------



## mnm123 (May 6, 2009)

No we did not. We couldn't figure out how to get that piece set up...


----------



## OBP (Mar 8, 2005)

Ok, go in to macros, "New macro", which opens a new macro in design mode.
Select for the first row's "Action" Drop down list "Open Form", in the lower part where it says Form Name click and a drop down appears, select your form. If you want to hide the form, in the Window Mode select "Hidden". Close or click "Save" and save the macro with the name "Autoexec".
A macro with the Autoexec is run whenever the Database is opened.
You can test it by Running it with the database open or close and re-open it.


----------



## mnm123 (May 6, 2009)

O.K. we just got the autoexec macro to work. Only step left is how to send the email with the message. Can you help?? Thanks..


----------



## OBP (Mar 8, 2005)

OK, either before this line or replace this line if you do not want the message to come up
msgbox "the Records shown require action today"
paste the following

Dim subject As String, Body As String, EmailAddress As String
EmailAddress = Me.EmailAddress
subject = "Action Required"
Body = "Please note that Action is required on " & [Text2]

DoCmd.SendObject , , , EmailAddress, , , subject, Body, False

Note that if you do not have an email address field on the form you need to replace this "*Me.EmailAddress*" with an actual email address in quotes like "myemail.com", if you have one modify it so that it has the name of your field

Also this *[Text2]* is for you to enter a meaningful field from the form that will identify what needs the action. You need to replace it with a field name using the format like the email one, ie. using the me.fieldname where fieldname is the actual name of your field. Or Delete it.
the email is sent after you click "yes" to the "Outlook" security question. If you want to be able to edit the email in Outlook first change "False" at the end of the "DoCmd" line to True.


----------



## mnm123 (May 6, 2009)

It worked and we do get an email. One problem left it isn't sending to all individuals who have a due date or expired date. The first individual on the form is getting the email but when we scroll to the next form that individual is not getting the email. Any ideas how to fix? Thanks so much for all the help.


----------



## OBP (Mar 8, 2005)

To send individual emails for each Record you need additional VBA . Can you post the vBA that have working?


----------



## mnm123 (May 6, 2009)

Here it is. Currently, only sending out one email. We also need to figure out how we can run it so that it only sends the email once to the person. Not sure if that can be done. thanks...

Private Sub Form_Load()
Dim subject As String, Body As String, EmailAddress As String
EmailAddress = Me.email_address
subject = "Action Required"
Body = [VendorName] & " " & "is due for oversight review process"

DoCmd.SendObject , , , EmailAddress, , , subject, Body, False

End Sub


----------



## OBP (Mar 8, 2005)

OK, change it to this

Private Sub Form_Load()
Dim subject As String, Body As String, EmailAddress As String
Dim rs as object, recount as Integer, count as Integer
set rs = me.recordsetclone
if rs.recordcount > 0 then
rs.movelast
rs.movefirst
recount = rs.recordcount
for count = 1 to recount
EmailAddress = Me.email_address
subject = "Action Required"
Body = [VendorName] & " " & "is due for oversight review process"

DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.movenext
next count
end if
End Sub

This is from memory so it is untested, if you get an error message makea note of it and come back on here. If you have a lot of records you might want to use a free program called ClickYes which will answer the outlook security question for you.


----------



## mnm123 (May 6, 2009)

o.k. I will try this out. Were can I get more information on the click yes program? Thanks again and I will let you know if I get any errors...


----------



## OBP (Mar 8, 2005)

http://www.contextmagic.com/express-clickyes/


----------



## mnm123 (May 6, 2009)

Tried the new code and it is now sending the email twice to the first person on the first record. It is still not looking at the next record to send out email to another person....


----------



## OBP (Mar 8, 2005)

Sorry, I forgot. Change this

EmailAddress = Me.email_address

to

EmailAddress = rs.email_address

and this

Body = [VendorName] & " " & "is due for oversight review process"

to

Body =rs.VendorName & " " & "is due for oversight review process"


----------



## mnm123 (May 6, 2009)

I changed the code but we get an error when we load the database. It doesn't seem to like the 

Emailaddress = rs.email._address


----------



## OBP (Mar 8, 2005)

The "." after rs.email should not be there.
try 
Is there a space between email and address?
If yes try 
rs.[email address]
or
rs![email address]


----------



## mnm123 (May 6, 2009)

It worked!! Thank you so much....


----------



## mnm123 (May 6, 2009)

Any ideas on how to stop the emailing from going out again to the same person everytime we open the access database. We only need the email sent out once and it keeps sending it out everytime we load the database.


----------



## OBP (Mar 8, 2005)

You will need an email sent field (check box) which you set when the email is sent.
Then in the query that supplies the form with the data enter in the Criteria row of the emailsent check box column
0

That will only list records where an email hasn't been sent.


----------



## mnm123 (May 6, 2009)

Where do I find the email sent field??


----------



## OBP (Mar 8, 2005)

You have to create one in the Table.


----------



## mnm123 (May 6, 2009)

o.k. we will do that now.


----------



## mnm123 (May 6, 2009)

I set that up as explained. We actually set up a new table with Email sent field and joined it to the Employee table. We brought the field down to the query section as a new Field called Email Sent and criteria set to 0. It stopped all emails from going out. I tried putting in new records but still did not send any emails out. Is there something else I need to set up?


----------



## OBP (Mar 8, 2005)

When you set up the new table how have you added that to the original query that provided a list of the Records that needed emails sent?


----------



## mnm123 (May 6, 2009)

This is what we have "On Load" in event procedure.

Private Sub Form_Load()​​Dim subject As String, Body As String, EmailAddress As String​Dim rs As Object, recount As Integer, count As Integer​Set rs = Me.RecordsetClone​If rs.RecordCount > 0 Then​rs.MoveLast​rs.MoveFirst​recount = rs.RecordCount​For count = 1 To recount​EmailAddress = rs![email address]​subject = "Action Required"​Body = rs.VendorName & " " & "is due for oversight review process"​​DoCmd.SendObject , , , EmailAddress, , , subject, Body, False​rs.MoveNext​Next count​End If​


----------



## OBP (Mar 8, 2005)

But do you get any records to send in the form?


----------



## mnm123 (May 6, 2009)

Right now if I pull up the form it is blank and no records are in the form...


----------



## OBP (Mar 8, 2005)

You have somehow messed up the Form's Record Source with the New Table.
Does the Query that supplies the Form show any Records?
Can you take a Screen Print of the Query in Design View?


----------



## mnm123 (May 6, 2009)

The query does not show any records when we run it. I attached a word doc with screen shot.


----------



## mnm123 (May 6, 2009)

Here is additional screen shot..


----------



## OBP (Mar 8, 2005)

I am not sure, but it looks as if the Joins between the Tables is not correct, should it be between EmployeeIDs?
Can you post a copy of the database with a couple of dummy records for me look at?
Also try deleting the new table from the query to tset if you get records or not. You do not have to "save" the cahnge to run the Query.


----------



## mnm123 (May 6, 2009)

If I delete the new table it runs o.k. I am going to try to figure out of it is joined properly and test out a few changes. I will keep you posted. Thanks.


----------



## mnm123 (May 6, 2009)

I deleted the table and created a field called emailsent in the CUemployees table. It ran fine when I did that and sent the emails but it is still sending out an email to each person when I go back into the database. I had to manually check off the checkboxes in the Email form, save it and when I went back in it did not send the email again. Is there maybe additional code I need to stop the emails from going out more than once. I have the criteria set to 0 as you can see in the screen shots.


----------



## OBP (Mar 8, 2005)

Yes there is, after the email has been sent but before the "Next" statement, add the followng
rs.emailsent = -1


----------



## mnm123 (May 6, 2009)

I put the new rs statement in and I am not getting any emails sent. I have criteria set to 0 on Emailsent field. If I remove the 0 criteria and do not have the new rs statement in than I get all the emails however it repeats every time I go into the DB. If I leave the new rs statement in and remove the 0 criteria than I get just one record sent and a run time error 3020. If I leave the 0 criteria in and remove the rs statement I still get no emails sent. It seems that the 0 criteria in email sent field is stopping all emails. Here is code below that I added:

Private Sub Form_Load()

Dim subject As String, Body As String, EmailAddress As String
Dim rs As Object, recount As Integer, count As Integer
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.EmailSent = -1
rs.MoveNext
Next count
End If

End Sub


----------



## OBP (Mar 8, 2005)

after the Dim statement add

on error goto errorcatch

after the end if add

exit sub
errorcatch:
MsgBox Err.Description

This will tell you what type of error you are getting. I need to know that to tell you what is wrong.


----------



## mnm123 (May 6, 2009)

If i leave in the new rs statement you gave me and leave code as is but just remove the 0 from criteria in Emailsent field I get one email sent (should have 8 emails sent) and the message box pops up and states "Update or cancel Update without AddNew or edit. I attached a screen shot.


----------



## OBP (Mar 8, 2005)

Sorry, that is my fault,I wasn't thinking clearly about changing the check box, it was too simple, it has to be

rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = .LastModified

In place of the simple rs.EmailSent = -1

You should also add before the Exit sub 
rs.close
set rs = nothing


----------



## mnm123 (May 6, 2009)

I updated the code below. I am getting a compile error. It is a Microsoft Visual Basic error - "Invalid or unqualified reference" and highlights rs.Bookmark = .LastModified line in code. I tried putting the 0 back in to the criteria field for email sent and got the same error. I wasn't sure if I should have the 0 for the criteria any longer? Do I leave the criteria blank now for Email Sent and not put 0 in? I attached screen shot. Thanks....

Private Sub Form_Load()

Dim subject As String, Body As String, EmailAddress As String
On Error GoTo errorcatch
Dim rs As Object, recount As Integer, count As Integer
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = .LastModified
rs.MoveNext
Next count
End If
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description

End Sub


----------



## OBP (Mar 8, 2005)

this 

rs.Bookmark = .LastModified

should be

rs.Bookmark = rs.LastModified


----------



## mnm123 (May 6, 2009)

That fixed the compile error. The problem is still the 0 in the criteria field for emailsent in the query. If I have it set to 0 all emails stop and nothing goes out at all. If I take the 0 out then I get all the emails and they get sent again when I go back into the database. Any ideas why this is happening?


----------



## OBP (Mar 8, 2005)

Why are you putting a 0 in the table?
Is the field a Check Box?


----------



## mnm123 (May 6, 2009)

Yes. the field is a check box. Should I not have the 0? I can take it out but the emails are still duplicating even if I do remove it. Let me know what you think. Thanks.


----------



## OBP (Mar 8, 2005)

Sorry, I can't open Office 2007 documents.


----------



## mnm123 (May 6, 2009)

o.k. here it is in older format...


----------



## OBP (Mar 8, 2005)

Ok, in the query instead of just 0 try
0 or Is Null

Or you could try 
<> -1

It is possible that some have no value at all.


----------



## mnm123 (May 6, 2009)

I tried both options and neither worked. I still get no emails sent. Is there a way that I could leave that criteria field blank and put something else into the code to have it send once only since. If I don't have anything in criteria field for email sent, it does send all the emails the only problem is it keeps sending the same emails everytime I go back into the database.

Existing code is:

Private Sub Form_Load()

Dim subject As String, Body As String, EmailAddress As String
On Error GoTo errorcatch
Dim rs As Object, recount As Integer, count As Integer
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = rs.LastModified
rs.MoveNext
Next count
End If
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description

End Sub


----------



## OBP (Mar 8, 2005)

Well you could try changing the Field to text and put a "Yes" in the ones that have been sent.
In your query put <> "Yes" as the criteria.
I have used this techniques quite a few times before, so I can't understand why it is not working.

Is there any chance that you can email me an Access 2003 vesrion with some dummy data in a couple of records?
The other possibilty is to put the emailsent check in the VBA code.

if emailsent <> -1 then
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = rs.LastModified
end if 
Unfortunately I have to go as my Grandchildren are coming.


----------



## mnm123 (May 6, 2009)

I made the changes in the VBA code. It is now displaying the emails when I am in design view and click on open form. It was not displaying the emails before. It is also checking off the yes box but is not sending out the email. I must have something off in the code where it stopped sending the email. Can you take a look at the code for me. I need to get permission to send a copy of the database. Not sure if I would be able to do that. You have been extremely helpful and appreciate any help. thanks...

here is the code with recent changes.

Dim subject As String, Body As String, EmailAddress As String
On Error GoTo errorcatch
Dim rs As Object, recount As Integer, count As Integer
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
If EmailSent <> -1 Then
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = rs.LastModified
End If
rs.MoveNext
Next count
End If
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description

End Sub


----------



## simplepa (Mar 17, 2009)

Hi:
I like this feature but can I set it up where it will email the person that is responsble for that record?


----------



## OBP (Mar 8, 2005)

simplepa, please start a new Thread on this topic, making reference to this one, but the answer is yes.
mnm, I have some things for you to try, as the email is not being sent the VBA code is not recognising If EmailSent <> -1 Then as valid, is the EmailSent field on your Form?

Before that line of code type in the following

msgbox rs.EmailSent

This will tell you in a message box what the value of emailsent is, if it comes up with an "Invalid Use of Null" then the emailsent field has no value (i.e. not 0 but nothing)
If that is the case try

If Isnull(EmailSent) Then

Instead of the current If statement.

If that still does not work, go in to the table and just for those 18 records tick and then untick the emailsent field so that it has been registered by the table and try running it with the original If EmailSent <> -1 Then
If EmailSent <> -1 Then


----------



## mnm123 (May 6, 2009)

o.k. When I put the message line in code I got a message box with the statement "True". I also checked and unchecked the records in the table and when I uncheck the records I get one email only which is the first record in the table and message is False in the message box. The next 3 messages in the message box are "True" even though I had the records unchecked. I tried this several times and the program seems to look at the first record and recognize that the first record is unchecked but the remaing records it thinks have been sent when in fact they were unchecked. I even tried to add new records, unchecked all the previous records and still recieved only one email which again was the first record in the table.


----------



## OBP (Mar 8, 2005)

OK, try changing this line 

Set rs = Me.RecordsetClone

to 

Set rs = CurrentDb.OpenRecordset("QUERYNAME")

Where QUERYNAME is the actual name of your Query that is presumably bringing up the correct records.
If that doesn't work we can try using the actual Table, but that requires completely different Code.


----------



## mnm123 (May 6, 2009)

o.k. I tried changing the command. Something is still wrong. When I uncheck all records in the form I get a response with "False" then an email gets sent out for the first record listed and then "True" in the message box for the remaing records but no additional emails get sent out. If I leave the first record checked and uncheck all the records below it then I get "True" message for all records below the first one listed and no emails sent out. If I uncheck just the first record and leave all records below it checked, I again get first message as "false" and an email will go out for the first record and get message "True" for all remaining records. The program really isn't recognizing at all whether or not the records are checked or unchecked under the first record listed because the response is always "True" for those records regardless.

Here is current code:
Dim subject As String, Body As String, EmailAddress As String
On Error GoTo errorcatch
Dim rs As Object, recount As Integer, count As Integer
Set rs = currentdb.OpenRecordset("EmailInfo Query")
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
MsgBox rs.EmailSent
If EmailSent <> -1 Then
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs!EmailSent = -1
rs.Update
rs.Bookmark = rs.LastModified
End If
rs.MoveNext
Next count
End If
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description

End Sub


----------



## OBP (Mar 8, 2005)

Any chance that you can create a database with that table, query and form in it with just the dates and check box, in other words no proprietory data at all so that I can see what is happening?

In the EmailInfo Query what do you see for the emailsent field, is it a tick box?


----------



## mnm123 (May 6, 2009)

Unfortunately I can't send the database. The email sent field is a check box - not sure if that is what you refer to as a tick box.


----------



## OBP (Mar 8, 2005)

Under those circumstances the only way that I can help you is by rebuilding your table, query and form.
So can you provide the table, query and form names and all of the field names, or at lwast all those that could affect the operation of the form.


----------



## mnm123 (May 6, 2009)

o.k. we built a small database with just the forms you outlined. You will have to put in a valid email addrees under FI Email in the employee table to test it out. The review date field is what should trigger the email to be sent. There should only be one email sent to the email address from that vendor record. Please let me know if you have any questions when testing it out. Right now it has invalid use of null when you open the database however when I had valid names and email adresses in there it came up "True" for all records unless I unchecked the first record then it came up "False" for the first record and true for all records as my previous email stated. Thanks for all your help....


----------



## OBP (Mar 8, 2005)

Sorry, can you convert it to Access 2000-2003 please?


----------



## mnm123 (May 6, 2009)

o.k. here it is in 2003.


----------



## OBP (Mar 8, 2005)

In the example you gave, the same person is used to send the emails, as soon as the email is sent for the first vendor and the emailsent field is updated no more emails can be sent by that person.
You need the emailsent field in the table that contains detail of person/vendor that requires the email to be sent.
A new table if necessary.
Do you want me to do it in this database. I won't be able to do it today as the Grandchildren are coming.


----------



## mnm123 (May 6, 2009)

Please use the database and create the table as you see fit. When you say the same person sending, do you mean the person that is logged into outlook at that time on the pc? I didn't realize that was a problem. When we tested previously we were actually were getting all the emails from only one individual who was logged into outlook. We ran into a problem because it kept sending the emails over and over whenever we logged on to the database and we only want the email sent once for each vendor. I guess it is more complicated than we thought initially. No problem if you can't do it today, I understand you have your grandchildren. Enjoy time with them...


----------



## mnm123 (May 6, 2009)

OBP, just want to clarify something. The email address for each employee listed is used so that they receive the email message when the next review date field is due or overdue. We only had one person listed because we created the records with the same person which is o.k. because technically you could have the same person get 10 emails if they are assigned to the vendor record. The email to the individual listed is triggered by the next review date....


----------



## OBP (Mar 8, 2005)

mnm, it is the person in the Table not the person Logged in that is the problem. Because the the Table person also has the emailsent field when their first email is sent is ticks their box and doesn't send any more.
Which is why when you didn't have it, it worked and sent them all.
I will take a look and let you know what you need to do on your database.


----------



## OBP (Mar 8, 2005)

Well I am dismayed by the logic of this, the VBA doesn't work with checking the emailsent = -1 where ever the emailsent field is. I have never come across this problem before.
However I have added the emailsent field to the UtilVendor table and the emails are sent just once. If you run the query or look at the form there are no records to be sent again.
If this works for you then I would suggest that whent the ReviewDate is reset if required then the emailsent is unticked.
I will leave it at that. But I am baffled by changing one record prevents the rest from working.
If it doesn't do what you want I will look at it further.


----------



## mnm123 (May 6, 2009)

OBP, emails is working properly now the only problem will be the email will not get sent again under the vendor record. We need to have the email sent again to the indivudual when a change is made to the next review date field. For example I may have 5/1/2009 in there and once the email goes out and the review is done the record might be changed to 5/1/2010 and a new email should be sent. Is there a way to change the code so that if the next review date field is changed it will uncheck the email sent box in the vendor table? Also, I need to define the date in the query a little better. Right now we have anything less than todays date and the email goes out <Date(). I would like to have the query set to "next review date 30 days less than today's date" and have the email generated. What would be the proper code to put in the query for the date? Thanks again.

Here is current code for vba

Private Sub Form_Load()
Dim subject As String, Body As String, EmailAddress As String
On Error GoTo errorcatch
Dim rs As Object, recount As Integer, count As Integer
Set rs = Me.RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
recount = rs.RecordCount
For count = 1 To recount
EmailAddress = rs![email address]
subject = "Action Required"
Body = rs.VendorName & " " & "is due for oversight review process"
MsgBox rs.EmailSent
DoCmd.SendObject , , , EmailAddress, , , subject, Body, False
rs.Edit
rs.EmailSent = -1
rs.Update
rs.Bookmark = rs.LastModified
rs.MoveNext
Next count
End If
rs.Close
Set rs = Nothing
Exit Sub
errorcatch:
MsgBox Err.Description

End Sub


----------



## OBP (Mar 8, 2005)

mnm, in the query do you mean that you want to trigger the email 30 days before the review date.
Or any time within 30 days after the review date?

You can add to the Review Date's after update field
me.emailsent = 0
that will reset it and allow the emailing query to work again.


----------



## mnm123 (May 6, 2009)

OBS, I want the email to get sent out 30 days before the review date. Not sure how to put that in query...


----------



## OBP (Mar 8, 2005)

Put in the query
between date() -30 and Date()

so that it will pick it up even if the 30 days is a Sunday, it will pick it up on the Monday.


----------



## mnm123 (May 6, 2009)

OBS, I put in me.emailsent=0 in the after update field for next review date but I get a message when I change the date and save the record.

Microsoft Access Message box: It says "Microsoft Access can't find the object me" " If me is a new macro group make sure you have saved it and typed it correctly.


----------



## OBP (Mar 8, 2005)

Did you put the emailsent field on the form with the nextReviewDate?
Has it got the same name?
Me means the currently open form, so it couldn't find the field emailsent.


----------



## mnm123 (May 6, 2009)

OBS. Fixed it and it is working great! Thank you so much for all the help. We could not have done it without you. I hope you will be around in the future in case we have another project on access. Thanks again.....


----------



## mnm123 (May 6, 2009)

Hey OBP. Hope I can pick your brain a little more. We are trying to figure out how we can have access automatically change some forms without having to go into them individually. Here is the problem: when we add a new employee to replace an old one we have to go into every form where the old employee name was and change it to the new employee name. Can you think of an easy way where we could have access do a look up on that name and replace it with the new name of the new employee. Not sure if a macro would do it and I am a little lost as to where I would start. We have a form set up in access with the employee information but we want to change it to inactive when they leave and keep the information out there so when we add a new employee we add a new record. Problem is when you add a new record you have to link all the old forms from the employee who left to the new employee taking over their job. Hope you can help. thanks...


----------



## OBP (Mar 8, 2005)

It sounds like you do not want to change the Forms, but you do want to change the data associated with the "Replaced" employee and make it associated with the emlpoyee "Replacing them" and Set the Repaced employee as "Inactive"?


----------



## mnm123 (May 6, 2009)

That is correct OBP


----------



## OBP (Mar 8, 2005)

Yes it can be done but you need to be certain that is what you want to do. If you use the simpler (still not easy) method it would get messed up if someone added or deleted any of the actual Tables in the database.
Of course it would probably be better to Assign the New employee the Old employee's ID then you wouldn't have to do anything else, just create a new record for the Old employee and mark them as Inactive. Assuming that the emloyee's ID is used in the other tables!


----------



## mnm123 (May 6, 2009)

Employee ID is not used in the other tables. The information for the other forms comes from the original record of the employee but not tied to employee ID. If I change the name on the record for the old employee to the new employees name than all the other tables get changed. However, if I create a new record using the old employee's ID number it doesn't work.


----------



## OBP (Mar 8, 2005)

Well that is a database design fault, text should not be used to relate Tables, that is what Autonumber ID fields are for.
So you could correct that and then use the ID.
If you want ot persist in using the employee name then all you need to do is copy the Old Employee details to a new record and overwrite the old one with the your new data.


----------

