• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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
0
sahi0002
Asked:
sahi0002
  • 5
  • 5
1 Solution
 
Jeffrey CoachmanMIS LiasonCommented:
sahi0002,

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","YourTableOrQuery", "YourField=" & "'" & YourValue & "'")
This will tell you the number of records meeting your criteria.

JeffCoachman
0
 
sahi0002Author Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
sahi0002Author Commented:
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 :)
0
 
sahi0002Author Commented:
Which ever you think will be the best way to inform.
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, but you still need to make the query to detect if the expiry date has passed:
 Iff(X>=Y,True,False)
...and the formula:
  DCount("YourField","YourTableOrQuery", "YourField=" & "'" & YourValue & "'")

If you can get this far, I can help you with the rest
0
 
sahi0002Author Commented:
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","YourTableOrQuery", "YourField=" & "'" & YourValue & "'")
This will tell you the number of records meeting your criteria."

What am I suppose to put in YourValue?
0
 
Jeffrey CoachmanMIS LiasonCommented:
The Unique Site Value you are looking for.
0
 
sahi0002Author Commented:
Hi,

I'm a little confused here.

So it will be something like:-

 Iff([Table].[Expiry]>=Now(),True,False) Then
DCount("Site","MyTable",I do not know what is suppose to come here. :S
0
 
Jeffrey CoachmanMIS LiasonCommented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now