Solved

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

Posted on 2009-05-14
11
275 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

749 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