Hello guys I'm new to Access but i have mad a DB now i have a date fleid call "DateHandOver" which the user can input the date but i also have a flied called "EndofMission" which i what the DB the take the date from "datehandove" and add 3 years on to that date and then when the date in "endofMission" is = today i ant a popup to come up saying please email the user.
i have got this
DateHandOver (Calculation, Date result) = Date (Month (EndofMission), Day (EndofMission) + 30, Year( EndofMission))
The easiest way to do this is to use VBA to create the plus 3 years when the data is entered, but it can also be used to update any record that you look at as well.
How many records do you have at the moment?
To create the pop-ups it is best to use a simple form based on a query that only lists records where the endofMission date matches the current date.
hi OB thank you so much for helping me
so i have about 50 records right now
sorry i meant + 3 years not + 30 years
i have got an navigation form which has all the other forms inside it
i have made a query which is the endofmission from with the criteria set to date() which is ok it works when the date become today date it gets added to the endofmission query but i need a popup to come up when a record is added to the endofmisson form
Do you know how to find the Event Procedures on a Form in Design Mode?
There are Events for the Form itself and also Events for the individual fields.
So the "After Update" event procedure of the DateHandOver can be used to place the date in the EndofMission when the DateHandOver is entered.
The "On Current" event for the form can be used to test if the EndofMission is equal to the currrent date.
But you have to go to the record for the test to work, which is why the Query is the best test and VBA code can look at the query and create a message box, or actually send an email.
Let's take it a step at a time, open the After Update Event Procedure for the Field DateHandOver
copy in
me.EndofMission = me.DateHandOver +1095
Which is 1095 days, there will be a slight error due to leap years and if you want to split up the date in to days, months & years and add 3 to the years you can by using
Did you enter a date in DateOrder?
The after update only works for newly added data.
You place the same code in the Form's On Current event and each time you go in to a record it will update the EndofMission date.
However it is best to use
if isnull(EndofMission) then
Me.EndofMission = Me.DateOrder + 1095
end if
Which doesn't bother to update dates that are already there.
Did you put the code in the Form's On Current Event?
Well if you want you can create a Query to update the records in the table, or you can just step through the records to update them.
The next step would be the pop up, personally I would use a Query to collect together any records where the dates match and display them when the database is opened to tell the users that action is required.
The problem with that is if the User doesn't go in to a record he won't get a message.
Plus he will have to go through every record to check, when you have hundreds or thousands of records that is unmanageable.
That is good, but would probably be better as a Continuous mode form, so that the number of records is obvious.
So you want to place the VBA code in the On Current event of that form?
You should also allow some leeway on the date match in the query, do you allow for dates that have passed as well as those matching?
What do you want the VBA to do?
Just give them a message or actually send an email?
So, you want the code in the first form?
The code for the On Current event is
if Date()> me.EndofMission then msgbox "Hey you need to send an email, please check the End of Mission form"
ob can you tell why it saying this i'm the only person who using this db
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!