Solved

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

Posted on 2009-05-14
11
273 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:sahi0002
Comment Utility
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
Comment Utility
Which ever you think will be the best way to inform.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
The Unique Site Value you are looking for.
0
 

Author Comment

by:sahi0002
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now