sahi0002
asked on
Adding a pop up note informing the records have not been renewed
Hi, I have a database in MS Access.
There is a field "Site" which is the name of the record, field "Commencing" which is the start date of a record/contract and field "Expiry" which is the expiry of that record.
There is also a field "Created Date" which displays the date of creation for every record automatically with Now()
I wish that whenever the "Expiry" date of a record has lapsed i.e. the current date is more than the expiry date, and a new record with the same name as the "Site" name has not been created having the field "Created Date" greater than the expiry of the previous record, then a pop up note should tell me that "This 'Site' needs to be renewed".
It should keep telling me everytime I open until a new record with the "Site" name same as the previous one is not created having its "Created Date" > expiry date of previous record.
Please help
thanks
There is a field "Site" which is the name of the record, field "Commencing" which is the start date of a record/contract and field "Expiry" which is the expiry of that record.
There is also a field "Created Date" which displays the date of creation for every record automatically with Now()
I wish that whenever the "Expiry" date of a record has lapsed i.e. the current date is more than the expiry date, and a new record with the same name as the "Site" name has not been created having the field "Created Date" greater than the expiry of the previous record, then a pop up note should tell me that "This 'Site' needs to be renewed".
It should keep telling me everytime I open until a new record with the "Site" name same as the previous one is not created having its "Created Date" > expiry date of previous record.
Please help
thanks
ASKER
Hi Jeff,
Is there any other way. Basically I have the standard duplicate record button.
Can there be a way if after the records expiry if the duplicate record button is not pressed for that record. Then an note should show.
Is there any other way. Basically I have the standard duplicate record button.
Can there be a way if after the records expiry if the duplicate record button is not pressed for that record. Then an note should show.
The issue here is thet it is posible for a person to click the "Duplicate" buton multiple times.
The other reason is that you do not want to write code that relies on direst User actions. (users can make mistakes)
...or write code based on something *not* happening.
Your original question said that "if the record had expired". You made no mention of having a "Duplicate Record Button" at that time.
If you want a system that is more or less full-proof, then I stand by my suggestions.
This will can be done, but it will require you to make an investment in seeing it through.
Your requirments are what make this tricky.
Most systems like this simply will have an "Expired" field (Based on the Expiry date and the current date), that they can key off of to initiate another action, either automatically or through code.
In other words, this is not typically done with a "Pop-up" messabge box.
Finally it is not clear, to me at least, what you mean by your last statement:
<It should keep telling me everytime I open until a new record with the "Site" name same as the previous one is not created having its "Created Date" > expiry date of previous record.>
So this may require more time.
Taking all of this into account, how would you like to procede?
JeffCoachman
The other reason is that you do not want to write code that relies on direst User actions. (users can make mistakes)
...or write code based on something *not* happening.
Your original question said that "if the record had expired". You made no mention of having a "Duplicate Record Button" at that time.
If you want a system that is more or less full-proof, then I stand by my suggestions.
This will can be done, but it will require you to make an investment in seeing it through.
Your requirments are what make this tricky.
Most systems like this simply will have an "Expired" field (Based on the Expiry date and the current date), that they can key off of to initiate another action, either automatically or through code.
In other words, this is not typically done with a "Pop-up" messabge box.
Finally it is not clear, to me at least, what you mean by your last statement:
<It should keep telling me everytime I open until a new record with the "Site" name same as the previous one is not created having its "Created Date" > expiry date of previous record.>
So this may require more time.
Taking all of this into account, how would you like to procede?
JeffCoachman
ASKER
Hi Jeff,
I have now placed the duplicate record button which is derived from a query that appends that record.
Hence it will now ask me before I click it that if i want to append it. Hence it won't accidently be pressed by users.
So now if this can be done that for each record, if its expiry date is over. And the Duplicate record button is not pressed for that record, it should inform me that the record needs to be duplicated. I am not sure if it should be in form of a pop-up note or some other way. :S
I hope I'm clear :)
I have now placed the duplicate record button which is derived from a query that appends that record.
Hence it will now ask me before I click it that if i want to append it. Hence it won't accidently be pressed by users.
So now if this can be done that for each record, if its expiry date is over. And the Duplicate record button is not pressed for that record, it should inform me that the record needs to be duplicated. I am not sure if it should be in form of a pop-up note or some other way. :S
I hope I'm clear :)
ASKER
Which ever you think will be the best way to inform.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi,
Ok will try doing that.
But i didn't exactly understand DCount()
" DCount that will count the number of occurences of a value in a Field.
For example:
DCount("YourField","YourTa
This will tell you the number of records meeting your criteria."
What am I suppose to put in YourValue?
Ok will try doing that.
But i didn't exactly understand DCount()
" DCount that will count the number of occurences of a value in a Field.
For example:
DCount("YourField","YourTa
This will tell you the number of records meeting your criteria."
What am I suppose to put in YourValue?
The Unique Site Value you are looking for.
ASKER
Hi,
I'm a little confused here.
So it will be something like:-
Iff([Table].[Expiry]>=Now(
DCount("Site","MyTable",I do not know what is suppose to come here. :S
I'm a little confused here.
So it will be something like:-
Iff([Table].[Expiry]>=Now(
DCount("Site","MyTable",I do not know what is suppose to come here. :S
The IIF and the Dcount are two separate calculations
The IIF will basically see if the record is expired
This will be in a field in your query.
The Dcount, on the other hand will be used to get a count of how many records match the expired records "Site" value (I guess it's Site value?... If that is what makes the record Unique).
If it is only 1, then you will need to create a new record.
Since I don't know what values are in your "Site" field, I don't know what to substitute.
So it will be something like this:
DCount("Site", "YourTable", "Site=" & SiteID)
(If SiteiID is numeric)
Or
DCount("Site", "YourTable", "Site=" & "'" & SiteName" & "'")
If the Site value is a string.
The IIF will basically see if the record is expired
This will be in a field in your query.
The Dcount, on the other hand will be used to get a count of how many records match the expired records "Site" value (I guess it's Site value?... If that is what makes the record Unique).
If it is only 1, then you will need to create a new record.
Since I don't know what values are in your "Site" field, I don't know what to substitute.
So it will be something like this:
DCount("Site", "YourTable", "Site=" & SiteID)
(If SiteiID is numeric)
Or
DCount("Site", "YourTable", "Site=" & "'" & SiteName" & "'")
If the Site value is a string.
On your own, can you create a query field that results in True (-1) if the Expiry date is passed, and False(0) if the Expiry date has not passed?
For example:
Iff(X>=Y,True,False)
Then again, on your own, can you create a DCount that will count the number of occurences of a value in a Field?
For example:
DCount("YourField","YourTa
This will tell you the number of records meeting your criteria.
JeffCoachman