CALCULATING A DATE 1YEAR FROM DATE ENTERED IN FIELD!!!!!!

[Liam.M]

Hey guys, I need some help.

In my Database I use V.B to display a Calandar(2) to the user, who then
selects the date they want, which is then displayed back to them in a
date/time field I have created, named "DateofIssue". I want to create
another date/time field (lets call it Due Date) whereby it calculates a
"One year" period from whatever the date the user has selected from the
Calandar2 function (which then transfers this date to the "DateofIssue"
date/time field. I am having trouble implementing the "=DateAdd"
function, as many of the threads on here discuss. The V.B code I am
using to display the calandar2 function, which the user selects such
and such date, and then it exhibits the date in the "DateofIssue"
field, is as follows:

Private Sub DateofIssue_MouseDown(Button As Integer, Shift As Integer,
X As Single, Y As Single)
Calendar2.Visible = True
Calendar2.SetFocus
If Not IsNull(DateofIssue) Then
Calendar2.Value = DateofIssue.Value
Else
Calendar2.Value = Date
End If

End Sub

Am I surposed to add code to this function that automatically
calculates "one year" from the selected date, that would then show this
result in the "Due Date" field, or am I surposed to create code within
the "Due Date" field?

If someone could please help me with this, it would be greatly
appreciated, and HELP with the code would be even more appreciated!

Thanxs, and kind regards

Liam Murphy

 

[Graham R Seach]

Liam,

All you need to do is add the following to txtDueDate's ControlSource
property:
=DateAdd("yyyy", 1, DateOfIssue)

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Liam.M]

Thanks Mr. Seach, that is awesome, your help is very much appreciated,
I have another question (if you could spare the time, that would be
awesome), based upon your help with the last problem. I am trying to
find out how to command the Database to automatically send a reminder
email to the user based on a date field (that you just helped me
calculate) The data base contain's both "the date of issue", and a "Due
Date" field, I need to have a automated email sent to "one specific
user" (myself), 2 months before the "Due Date" to remind me that this
record "Is Due" within Two months, any assistance you could provide
would be appreciated....code would be a life saver!

Kind Regards,

Liam Murphy.

 

[Graham R Seach]

Liam,

You can send email directly from Access using VBA, but you get several
annoying messages. I thiink you're better to use something like this:
http://www.contextmagic.com/express-clickyes/

As for sending it once a certain date is reached, you'll need to add a table
field to each record to indicate whether the email has been sent *for that
record*. You should also add code to the form's Current event, to check the
DueDate. If the DueDate is the one you want, then check the new table field
to see if the email has already been send. If not, send the email; if so, do
nothing.

If you want the system to automatically check all the relevent records in
the database and automatically send emails about the ones that will be due,
you'll need to have startup code check the database when you first launch
Access, and send emails as necessary.

Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia

 

[Liam.M]

Hey Graham,

Thankyou so much for your response, it is very much appreciated. You
will have to forgive me for my silly questions, but could you please
help me clarify a few things. Firstly, I have added a new field to my
table, which is yes/no check box, named "EmailSent", with the default
value set to No.

I am curious as to the code I should add to my Records On Current
Event, to check the "DueDate" field...could you possibly provide me
with an example or the exact code, it would be greatly appreciated.
This code I assume would be a DateDiff function...checking the systems
current date with the Records "Due Date" field (which you helped me
with earlier), minus two months (to remind the system adminstrater that
the "due date" is almost reached. Is this possible? Because the
equation would have to check if the Systems date falls within the last
two months of the "Due Date"? please advise.

A query must then be done to see if the "EmailSent" Yes/no field is
still at default value of "No"? what sort of code will I have to obtain
to check this, and also change it once the email has been sent? Any
suggestions? And more importantly what sort of code do I require to
actually send the email itself? So far I have only looked at MAPI very
briefly, and it doesnt appear to be able to perform the function I
require it for.

As for the system automatically checking all of the relevent records in
the database and sending automated emails, i will obviously have to
obtain code for the databases "on load" event? Apologies for all of
these questions, unfortunately I am fine at reading code/ adopting and
understanding it for my own needs, however, when it comes to writing
it....I am useless, if you are able to provide me with further
assistance it would be much appreciated!

Kind Regards,

Liam Murphy

 

[Liam.M]

I have breifly attempted to write a module to send an Email :

Function emailer()
Dim rs As Recordset
Dim strsql As String


strsql = "SELECT [YourEmailAddressField] FROM [YourTable]
WHERE [YourDueDateField] = Date GROUP BY
[YourEmailAddressField]"


Set rs = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)


If Not rs.EOF Then
rs.MoveFirst
Do Until rs.EOF


DoCmd.SendObject acSendNoObject, , , rs.Fields
("YourEmailAddressField"), , , "Type Your Subject
Here", "Any Message Text Here", False


rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
End Function

But this is as far as I got.....I want to add fields such as the
"Vessels Name" and "IMO Number", could you help with adding this to the
code for the email, and also would this code be appropriate?

 

[Graham R Seach]

Liam,

I'm sorry for not getting back to you sooner, but I've been extremely busy
with word, study and family commitments.

I haven't checked your code, but I assume [Vessel Name] and [IMO Number] are
in [YourTable]. If that's the case, then you need to get that data from the
table in the recordset. Then simply pass that data into the body of the
email using the variable, strMessage.

Function emailer()
Dim rs As Recordset
Dim strSQL As String
Dim strMessage As String

strsql = "SELECT [YourEmailAddressField], [Vessel Name], [IMO Number] "
& _
"FROM [YourTable] " & _
"WHERE [YourDueDateField] = Date()" & _
"GROUP BY [YourEmailAddressField]"

Set rs = CurrentDb.OpenRecordset(strsql, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
Do Until rs.EOF
strMessage = rs![Vessel Name] & _
vbCrLf & rs![IMO Number] & _
vbCrLf & "some extra text"

DoCmd.SendObject acSendNoObject, , , rs.Fields _
("YourEmailAddressField"), , , _
"Type Your Subject Here", _
strMessage, False

rs.MoveNext
Loop
End If

rs.Close
Set rs = Nothing
End Function


Regards,
Graham R Seach
Microsoft Access MVP
Canberra, Australia