Solved

Adding a pop up note informing the records have not been renewed

Posted on 2009-05-14
11
276 Views
Last Modified: 2012-05-07
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
Comment
Question by:sahi0002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24417362
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
 

Author Comment

by:sahi0002
ID: 24418263
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24418752
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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:sahi0002
ID: 24418887
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
 

Author Comment

by:sahi0002
ID: 24418890
Which ever you think will be the best way to inform.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 24428254
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
 

Author Comment

by:sahi0002
ID: 24428420
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24429066
The Unique Site Value you are looking for.
0
 

Author Comment

by:sahi0002
ID: 24447408
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24447665
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

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question